/// <summary>
/// Reduce my data to map Data structure
/// </summary>
/// <param name="mapDataColumns"></param>
/// <param name="dtSessionData"></param>
/// <returns></returns>
private DataTable TransformTableMyDataCase(DI_MapDataColumns mapDataColumns, DataTable dtSessionData, ref bool isMrdColumnAdded)
{
DataTable RetVal = new DataTable();
string numericColumnIndexes = string.Empty;
List<string> numericColumnList = new List<string>();
string areaColumnIndexes = string.Empty;
List<string> areaColumnList = new List<string>();
string isMRDColumnIndexes = string.Empty;
List<string> isMRDColumnList = new List<string>();
string timePeriodColumnIndexes = string.Empty;
List<string> timePeriodColumnList = new List<string>();
int counter = 1;
DataRow DRRow = null;
Dictionary<string, string> columnHistory = new Dictionary<string, string>();
List<string> seriesCombinations = new List<string>();
string SeriesText = string.Empty;
isMrdColumnAdded = false;
try
{
//1) getColumnCaption of Area and Numeric column and set columns index
foreach (DataColumn column in dtSessionData.Columns)
{
if (!string.IsNullOrEmpty(column.Caption))
{
//area columns
if (column.Caption == mapDataColumns.AreaID || column.Caption == mapDataColumns.AreaName)
{
if (column.Caption == mapDataColumns.AreaID && areaColumnIndexes != "")
{
areaColumnIndexes = dtSessionData.Columns.IndexOf(column) + "," + areaColumnIndexes;
}
else
{
areaColumnIndexes += dtSessionData.Columns.IndexOf(column) + ",";
}
continue;
}
////numeric columns
//if (column.DataType == typeof(decimal))
//{
// numericColumnIndexes += dtSessionData.Columns.IndexOf(column) + ",";
//}
//numeric columns
if (column.Caption.StartsWith("[**Data_Value**]"))
{
numericColumnIndexes += dtSessionData.Columns.IndexOf(column) + ",";
}
////ToDo Manage it by gaurav in DES case Column caption get Time instead of TimePeriod
//if (column.Caption == "Time")
//{
// column.Caption = mapDataColumns.TimePeriod;
// column.ColumnName = mapDataColumns.TimePeriod;
//}
//timeperiod columns
if (column.Caption == mapDataColumns.TimePeriod)
{
timePeriodColumnIndexes += dtSessionData.Columns.IndexOf(column) + ",";
}
if (column.Caption == mapDataColumns.isMRD)
{
isMRDColumnIndexes += dtSessionData.Columns.IndexOf(column) + ",";
}
}
}
areaColumnIndexes = areaColumnIndexes.Trim(',');
numericColumnIndexes = numericColumnIndexes.Trim(',');
timePeriodColumnIndexes = timePeriodColumnIndexes.Trim(',');
isMRDColumnIndexes = isMRDColumnIndexes.Trim(',');
//2. get column name by column Index
//2.1 for numeric columns
foreach (string numericColumnIndex in numericColumnIndexes.Split(','))
{
if (!string.IsNullOrEmpty(numericColumnIndex))
{
numericColumnList.Add(dtSessionData.Columns[Convert.ToInt32(numericColumnIndex)].ColumnName);
}
}
//2.2. for area columns
foreach (string areaColumnIndex in areaColumnIndexes.Split(','))
{
if (!string.IsNullOrEmpty(areaColumnIndex))
{
areaColumnList.Add(dtSessionData.Columns[Convert.ToInt32(areaColumnIndex)].ColumnName);
}
}
//2.3. for timeperiod columns
foreach (string timeperiodColumnIndex in timePeriodColumnIndexes.Split(','))
{
if (!string.IsNullOrEmpty(timeperiodColumnIndex))
{
timePeriodColumnList.Add(dtSessionData.Columns[Convert.ToInt32(timeperiodColumnIndex)].ColumnName);
}
}
//2.4. for timeperiod columns
foreach (string MRDColumnIndex in isMRDColumnIndexes.Split(','))
{
if (!string.IsNullOrEmpty(MRDColumnIndex))
{
isMRDColumnList.Add(dtSessionData.Columns[Convert.ToInt32(MRDColumnIndex)].ColumnName);
}
}
////Remove extra quotes from session data table
//dtSessionData = this.RemoveQuotefromTableContent(Timeperiods.TimePeriod, dtSessionData);
//3. create columns
//3.1 for area column
RetVal.Columns.Add(mapDataColumns.AreaID, typeof(string));
if (areaColumnIndexes.Split(',').Length > 1)
{
RetVal.Columns.Add(mapDataColumns.AreaName, typeof(string));
}
//3.2 for timeperiod Column
RetVal.Columns.Add(mapDataColumns.TimePeriod, typeof(string));
//3.3 for series ID for distinct combination od data
RetVal.Columns.Add(mapDataColumns.SeriesNid, typeof(int));
foreach (DataColumn column in dtSessionData.Columns)
{
if (!string.IsNullOrEmpty(column.ColumnName) && !numericColumnList.Contains(column.ColumnName) && column.Caption != mapDataColumns.AreaID && column.Caption != mapDataColumns.AreaName && column.Caption != mapDataColumns.TimePeriod && column.Caption.ToLower() != "ismrd")
{
RetVal.Columns.Add("s" + counter, typeof(string));
RetVal.Columns["s" + counter].Caption = "[**SERIES**]s" + counter;
columnHistory.Add(column.ColumnName, "s" + counter);
counter++;
}
}
//3.4 For Numeric combinations like India,IMR,2010,Male for male and itz kind of subgroup_Val
RetVal.Columns.Add("s" + counter, typeof(string));
RetVal.Columns["s" + counter].Caption = "[**SERIES**]s" + counter;
//3.5 for other combination type column
RetVal.Columns.Add(mapDataColumns.isMRD, typeof(bool));
//3.6 for other combination type column
RetVal.Columns.Add(mapDataColumns.DataValue, typeof(string));
//4. add rows for discinct combination
foreach (DataRow row in dtSessionData.Rows)
{
//4.1 add all numeric combinations
foreach (string columnName in numericColumnList)
{
SeriesText = string.Empty;
if (string.IsNullOrEmpty(row[areaColumnList[0]].ToString().Trim()) || string.IsNullOrEmpty(row[columnName].ToString().Trim()))
{
//areaID is blank then wont cosider unmapped area //check for data value also if blank then not add column
continue;
}
DRRow = RetVal.NewRow();
//4.1.1 set area values by index
DRRow[mapDataColumns.AreaID] = row[areaColumnList[0]].ToString().Trim();
if (isMRDColumnList.Count > 0)
{
DRRow[mapDataColumns.isMRD] = row[isMRDColumnList[0]];
}
else
{
if (isMrdColumnAdded == false)
isMrdColumnAdded = true;
DRRow[mapDataColumns.isMRD] = false;
}
if (areaColumnList.Count > 1)
{
DRRow[mapDataColumns.AreaName] = row[areaColumnList[1]].ToString().Trim();
}
//4.1.2 set timeperiod values by index
if (timePeriodColumnList.Count > 0)
{
DRRow[mapDataColumns.TimePeriod] = row[timePeriodColumnList[0]];
}
else
{
DRRow[mapDataColumns.TimePeriod] = string.Empty;
}
//4.1.3 set value for other combination type column
foreach (KeyValuePair<string, string> columnDic in columnHistory)
{
DRRow[columnDic.Value] = row[columnDic.Key];
SeriesText += row[columnDic.Key] + ",";
}
// subgroup_Val
DRRow["s" + counter] = columnName;
DRRow[mapDataColumns.DataValue] = row[columnName];
SeriesText += columnName;
if (!seriesCombinations.Contains(SeriesText))
{
seriesCombinations.Add(SeriesText);
}
DRRow[mapDataColumns.SeriesNid] = seriesCombinations.IndexOf(SeriesText) + 1;
RetVal.Rows.Add(DRRow);
RetVal.AcceptChanges();
}
}
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
throw;
}
finally
{
if (RetVal == null)
{ RetVal = dtSessionData; }
}
return RetVal;
}
private DataTable ResetDTColumns(DI_MapDataColumns mapDataColumns, DataTable dtSessionData, bool isMyData, ref bool isMrdColumnAdded)
{
DataTable RetVal = null;
string dataValue = string.Empty;
string IUSNId = string.Empty;
bool isNumericColumnAdded = false;
DataTable distinctSessionTable = null;
isMrdColumnAdded = false;
try
{
//1. if my data case then transform myData format to MapData
if (isMyData && !dtSessionData.Columns.Contains(mapDataColumns.SeriesNid))
{
dtSessionData = this.TransformTableMyDataCase(mapDataColumns, dtSessionData, ref isMrdColumnAdded);
}
//2. set table name
dtSessionData.TableName = "dvTable";
//3. Add data_value column as NumericData with datatype decimal. This will handle texual values
isNumericColumnAdded = this.CheckNCreateColumn(dtSessionData, mapDataColumns.NumericData, typeof(decimal));
if (isNumericColumnAdded)
{
//2. add new column as numeric data
for (int row = 0; row < dtSessionData.Rows.Count; row++)
{
dataValue = Convert.ToString(dtSessionData.Rows[row][mapDataColumns.DataValue]);
if (!Global.isNumeric(dataValue, System.Globalization.NumberStyles.Any))
{
//set numeric values for distinct textual data values (Yes, No, Don't Know)
distinctSessionTable = dtSessionData.DefaultView.ToTable(true, mapDataColumns.DataValue);
distinctSessionTable.Columns.Add(mapDataColumns.NumericData, typeof(decimal));
for (int i = 0; i < distinctSessionTable.Rows.Count; i++)
{
distinctSessionTable.Rows[i][mapDataColumns.NumericData] = i + 1;
}
//remove then add again
dtSessionData.Columns.Remove(mapDataColumns.NumericData);
dtSessionData = this.AddRecordFromTableWithoutLoop(dtSessionData, distinctSessionTable, mapDataColumns.DataValue, mapDataColumns.DataValue + "," + mapDataColumns.NumericData);
break;
}
else
{
dtSessionData.Rows[row][mapDataColumns.NumericData] = dtSessionData.Rows[row][mapDataColumns.DataValue];
}
}
}
RetVal = dtSessionData;
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
throw;
}
return RetVal;
}
private SeriesData GetSeriesInfo(DI_MapDataColumns mapDataColumns, DataTable SeriesDataTable, bool isMyData, string dbNid, string LanguageCode)
{
SeriesData RetVal = null;
Hashtable Indicators = new Hashtable();
DataTable dtDistinctIUSs = null;
DataTable dtDistinctIndicatorNIds = null;
Hashtable IUSs = null;
SeriesInfo seriesInfo;
List<string> distinctIUSNIds = new List<string>();
List<string> columnsToSkipFilter = new List<string>();
List<string> distinctColumns = new List<string>();
string SeriesName = string.Empty;
try
{
if (SeriesDataTable.Rows.Count > 0)
{
seriesInfo = new SeriesInfo();
RetVal = new SeriesData();
if (!isMyData)
{
//get theme name IndicatorUnitSubgroup from Database
_DBCon = Global.GetDbConnection(int.Parse(dbNid));
dtDistinctIndicatorNIds = SeriesDataTable.DefaultView.ToTable(true, mapDataColumns.SeriesNid);
dtDistinctIUSs = SeriesDataTable.DefaultView.ToTable(true, mapDataColumns.SeriesNid);
this.SetTimeLogIntoFile("Start getting IUSs Nmaes from Procedure sp_getIUSNames_-> " + DateTime.Now.ToLongTimeString());
IUSs = getIUSNames(dtDistinctIUSs, LanguageCode);
this.SetTimeLogIntoFile("Ends getting IUSs Nmaes from Procedure sp_getIUSNames_-> " + DateTime.Now.ToLongTimeString());
foreach (object key in IUSs.Keys)
{
seriesInfo = new SeriesInfo();
seriesInfo.SeriesID = key.ToString();
seriesInfo.SeriesName = IUSs[key].ToString();
RetVal.Add(seriesInfo);
}
}
else
{
columnsToSkipFilter.Add(mapDataColumns.AreaID);
columnsToSkipFilter.Add(mapDataColumns.AreaName);
columnsToSkipFilter.Add(mapDataColumns.DataValue);
columnsToSkipFilter.Add(mapDataColumns.NumericData);
columnsToSkipFilter.Add(mapDataColumns.TimePeriod);
columnsToSkipFilter.Add(mapDataColumns.isMRD);
//set row filter
foreach (DataColumn column in SeriesDataTable.Columns)
{
if (!columnsToSkipFilter.Contains(column.ColumnName))
{
distinctColumns.Add(column.ColumnName);
}
}
dtDistinctIndicatorNIds = SeriesDataTable.DefaultView.ToTable(true, distinctColumns.ToArray());
foreach (DataRow row in dtDistinctIndicatorNIds.Rows)
{
seriesInfo = new SeriesInfo();
SeriesName = string.Empty;
seriesInfo.SeriesID = Convert.ToString(row[mapDataColumns.SeriesNid]);
foreach (string columnName in distinctColumns)
{
if (columnName != mapDataColumns.SeriesNid)
{
SeriesName += Convert.ToString(row[columnName]) + ", ";
}
}
seriesInfo.SeriesName = SeriesName.TrimEnd(", ".ToCharArray());
RetVal.Add(seriesInfo);
}
}
}
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
throw;
}
return RetVal;
}
private SourceData GetSourceInfo(DI_MapDataColumns mapDataColumns, DataTable sessionData, bool isMyData, string dbNid, string LanguageCode)
{
SourceData RetVal = null;
DataTable SourceTable = null;
SourceInfo sourceInfo;
try
{
if (sessionData.Rows.Count > 0)
{
sourceInfo = new SourceInfo();
RetVal = new SourceData();
//get theme name IndicatorUnitSubgroup from Database
_DBCon = Global.GetDbConnection(int.Parse(dbNid));
//Step 2 Get IUS and Source information
//Step 2.1. Get Source Information from Database
List<DbParameter> DbParams = null;
DbParameter sourceParam1 = _DBCon.CreateDBParameter();
sourceParam1.ParameterName = "SourceNIds";
sourceParam1.DbType = DbType.String;
sourceParam1.Value = this.AddQuotesInCommaSeperated(this.GetCSV(sessionData, mapDataColumns.SourceNid), false);
DbParams = new List<DbParameter>();
DbParams.Add(sourceParam1);
SourceTable = _DBCon.ExecuteDataTable("sp_get_sources_from_nids_" + LanguageCode, CommandType.StoredProcedure, DbParams).DefaultView.ToTable(true, mapDataColumns.SourceNid, mapDataColumns.SourceName);
foreach (DataRow row in SourceTable.Rows)
{
sourceInfo = new SourceInfo();
sourceInfo.SourceNId = row[mapDataColumns.SourceNid].ToString();
sourceInfo.SourceName = row[mapDataColumns.SourceName].ToString();
RetVal.Add(sourceInfo);
}
}
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
throw;
}
return RetVal;
}
private void CheckNCreateIsMRDColumn(DI_MapDataColumns mapDataColumns, DataTable dtSessionData)
{
bool isNewColumnCreated = false;
try
{
isNewColumnCreated = this.CheckNCreateColumn(dtSessionData, mapDataColumns.isMRD, typeof(bool));
if (isNewColumnCreated)
{
//to do get MRD
}
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
}
}
