private static void ImportMapping(string MappedValueString, string MappedTypeString, Mapping elementMapping) { string Value = string.Empty; string GID = string.Empty; string[] MappedValues; string[] MappedTypes; string[] ElementValue; if (!string.IsNullOrEmpty(MappedValueString) && !string.IsNullOrEmpty(MappedTypeString)) { MappedTypes = DICommon.SplitString(MappedTypeString, "\r\n"); MappedValues = DICommon.SplitString(MappedValueString, "\r\n"); for (int i = 0; i < MappedTypes.Length; i++) { // only if value exists if (!string.IsNullOrEmpty(MappedTypes[i]) && !string.IsNullOrEmpty(MappedValues[i])) { ElementValue = DICommon.SplitString(MappedValues[i], "||"); // only if element value have value and GID if (ElementValue.Length == 2) { Value = Convert.ToString(ElementValue[0]); GID = Convert.ToString(ElementValue[1]); switch (MappedTypes[i].ToUpper()) { case "INDICATOR": elementMapping.IndicatorName = Value; elementMapping.IndicatorGID = GID; break; case "UNIT": elementMapping.UnitName = Value; elementMapping.UnitGID = GID; break; case "SUBGROUP": elementMapping.SubgroupVal = Value; elementMapping.SubgroupValGID = GID; break; case "AREA": elementMapping.Area = Value; elementMapping.AreaID = GID; break; case "SOURCE": elementMapping.Source = Value; break; case "TIMEPERIOD": elementMapping.Timeperiod = Value; break; case "AGE": case "SEX": case "LOCATION": case "OTHERS": if (elementMapping.Subgroups.ContainsKey(GID) == false) { DevInfo.Lib.DI_LibBAL.DA.DML.DI6SubgroupInfo SG = new DevInfo.Lib.DI_LibBAL.DA.DML.DI6SubgroupInfo(); SG.Name = Value; SG.GID = GID; elementMapping.Subgroups.Add(GID, SG); } break; default: break; } } } } } }
private void InsertIndicatorUnitSubgroups() { string SQL = string.Empty; //- Create DML Database object. DevInfo.Lib.DI_LibBAL.DA.DML.DIDatabase Database = new DevInfo.Lib.DI_LibBAL.DA.DML.DIDatabase(this._DBConnection, this._DBQueries); DevInfo.Lib.DI_LibBAL.DA.DML.IUSInfo newIUS = null; DevInfo.Lib.DI_LibBAL.DA.DML.DI6SubgroupInfo SubgroupDimensionValue = null; DevInfo.Lib.DI_LibBAL.DA.DML.DI6SubgroupTypeInfo oSubgroupType = null; DevInfo.Lib.DI_LibBAL.DA.DML.DI6SubgroupValBuilder SubgroupValBuilder = new DevInfo.Lib.DI_LibBAL.DA.DML.DI6SubgroupValBuilder(this._DBConnection, this._DBQueries); DevInfo.Lib.DI_LibBAL.DA.DML.DI6SubgroupTypeBuilder SubgroupTypeBuilder = new DevInfo.Lib.DI_LibBAL.DA.DML.DI6SubgroupTypeBuilder(this._DBConnection, this._DBQueries); DevInfo.Lib.DI_LibBAL.DA.DML.DI6SubgroupBuilder SubgroupBuilder = new DevInfo.Lib.DI_LibBAL.DA.DML.DI6SubgroupBuilder(this._DBConnection, this._DBQueries); try { //- Get all IndicatorGID, UnitGId, SubgroupVal_Gid form TempTable & thier Names + NIds from associated tables. SQL = "SELECT DISTINCT T." + Indicator.IndicatorName + ", T." + Unit.UnitName + ", T." + SubgroupVals.SubgroupVal + ", T." + Indicator.IndicatorGId + ", T." + Unit.UnitGId + ", T." + SubgroupVals.SubgroupValGId + ", I." + Indicator.IndicatorNId + ", U." + Unit.UnitNId + ", S." + SubgroupVals.SubgroupValNId + ", I." + Indicator.IndicatorName + ", U." + Unit.UnitName + ", S." + SubgroupVals.SubgroupVal + " FROM ((" + Constants.TempDataTableName + " AS T LEFT JOIN " + this._DBQueries.TablesName.Indicator + " AS I ON T." + Indicator.IndicatorGId + " = I." + Indicator.IndicatorGId + ") LEFT JOIN " + this._DBQueries.TablesName.Unit + " AS U ON T." + Unit.UnitGId + " = U." + Unit.UnitGId + ") LEFT JOIN " + this._DBQueries.TablesName.SubgroupVals + " AS S ON T." + SubgroupVals.SubgroupValGId + " = S." + SubgroupVals.SubgroupValGId; DataTable UnmatchedI_U_S_GIds = this._DBConnection.ExecuteDataTable(SQL); if (UnmatchedI_U_S_GIds != null && UnmatchedI_U_S_GIds.Rows.Count > 0) { //- For each unmatched I_U_S, foreach (DataRow drIUS in UnmatchedI_U_S_GIds.Rows) { // create IUS combination in database newIUS = new DevInfo.Lib.DI_LibBAL.DA.DML.IUSInfo(); //- If IndicatorNId not found for indicatorGID in template, then create GID by IndicatorName // Logic for creating GID by Name is fixed. Removing space character by Dots and converting into upper case. // FOR E.g.: GID for "Population Size" will be "POPULATION.SIZE" // GID for "Total Female" will be "TOTAL.FEMALE" if (drIUS[Indicator.IndicatorNId] == DBNull.Value || Convert.ToInt32(drIUS[Indicator.IndicatorNId]) <= 0) { newIUS.IndicatorInfo.Name = Convert.ToString(drIUS["T." + Indicator.IndicatorName]); newIUS.IndicatorInfo.GID = Convert.ToString(drIUS[Indicator.IndicatorGId]); newIUS.IndicatorInfo.GID = this.CreateGIdByName(newIUS.IndicatorInfo.GID); } else { newIUS.IndicatorInfo.Name = Convert.ToString(drIUS["I." + Indicator.IndicatorName]); newIUS.IndicatorInfo.GID = Convert.ToString(drIUS[Indicator.IndicatorGId]); } //- If UnitNId not found for UnitGID in template, then create GID by UnitName if (drIUS[Unit.UnitNId] == DBNull.Value || Convert.ToInt32(drIUS[Unit.UnitNId]) <= 0) { newIUS.UnitInfo.Name = Convert.ToString(drIUS["T." + Unit.UnitName]); newIUS.UnitInfo.GID = Convert.ToString(drIUS[Unit.UnitGId]); newIUS.UnitInfo.GID = this.CreateGIdByName(newIUS.UnitInfo.GID); } else { newIUS.UnitInfo.Name = Convert.ToString(drIUS["U." + Unit.UnitName]); newIUS.UnitInfo.GID = Convert.ToString(drIUS[Unit.UnitGId]); } //- If SubgroupValNId not found for Subgroup_Val_GID in template, then create GID by Subgroup_Val if (drIUS[SubgroupVals.SubgroupValNId] == DBNull.Value || Convert.ToInt32(drIUS[SubgroupVals.SubgroupValNId]) <= 0) { newIUS.SubgroupValInfo.Name = Convert.ToString(drIUS["T." + SubgroupVals.SubgroupVal]); newIUS.SubgroupValInfo.GID = Convert.ToString(drIUS[SubgroupVals.SubgroupValGId]); newIUS.SubgroupValInfo.GID = this.CreateGIdByName(newIUS.SubgroupValInfo.GID); //-- Check if newIUS.SubgroupValInfo.Nid = SubgroupValBuilder.GetSubgroupValNid(newIUS.SubgroupValInfo.GID, newIUS.SubgroupValInfo.Name); //- Add Subgroup Dimensions Value against SubgroupVal SubgroupDimensionValue = new DevInfo.Lib.DI_LibBAL.DA.DML.DI6SubgroupInfo(); if (newIUS.SubgroupValInfo.Nid <= 0) { //- Assign Subgroup Dimension (SubgroupType) as "OTHERS" oSubgroupType = new DevInfo.Lib.DI_LibBAL.DA.DML.DI6SubgroupTypeInfo(); oSubgroupType.Name = SubgroupType.Others.ToString(); oSubgroupType.GID = SubgroupType.Others.ToString().ToUpper(); oSubgroupType.Nid = SubgroupTypeBuilder.CheckNCreateSubgroupType(oSubgroupType); //- keep Subgroup Dimensions Value same as Subgroup_Val SubgroupDimensionValue.DISubgroupType = oSubgroupType; SubgroupDimensionValue.Name = newIUS.SubgroupValInfo.Name; SubgroupDimensionValue.Type = oSubgroupType.Nid; SubgroupDimensionValue.Nid = SubgroupBuilder.CheckNCreateSubgroup(SubgroupDimensionValue); //- Check and insert SubgroupVal newIUS.SubgroupValInfo.Nid = SubgroupValBuilder.CheckNCreateSubgroupVal(newIUS.SubgroupValInfo); //- Add SubgroupVal - Subgroup RelationShip SubgroupValBuilder.InsertSubgroupValRelations(newIUS.SubgroupValInfo.Nid, SubgroupDimensionValue.Nid); } } else { newIUS.SubgroupValInfo.Name = Convert.ToString(drIUS["S." + SubgroupVals.SubgroupVal]); newIUS.SubgroupValInfo.GID = Convert.ToString(drIUS[SubgroupVals.SubgroupValGId]); newIUS.SubgroupValInfo.Nid = Convert.ToInt32(drIUS[SubgroupVals.SubgroupValNId]); } newIUS.Nid = Database.DIIUS.CheckNCreateIUS(newIUS); } //- NOW update IndiatorGID, UnitGId, SubgroupGID in TempDataTable for matched Names //- Update IndicatorGID in TempDataTable for matching Names between UT_Indicator and TempDataTable SQL = "UPDATE " + Constants.TempDataTableName + " AS T INNER JOIN " + this._DBQueries.TablesName.Indicator + " AS I ON T." + Indicator.IndicatorName + " = I." + Indicator.IndicatorName + " SET T." + Indicator.IndicatorGId + " = I." + Indicator.IndicatorGId + " WHERE T." + Indicator.IndicatorName + " IS NOT Null AND T." + Indicator.IndicatorName + " <> ''"; this._DBConnection.ExecuteNonQuery(SQL); //- Update UnitGID in TempDataTable for matching Names between UT_Unit and TempDataTable SQL = "UPDATE " + Constants.TempDataTableName + " AS T INNER JOIN " + this._DBQueries.TablesName.Unit + " AS U ON T." + Unit.UnitName + " = U." + Unit.UnitName + " SET T." + Unit.UnitGId + " = U." + Unit.UnitGId + " WHERE T." + Unit.UnitName + " IS NOT Null AND T." + Unit.UnitName + " <> ''"; this._DBConnection.ExecuteNonQuery(SQL); //- Update SubgroupValGID in TempDataTable for matching Names between UT_Subgroup_Vals and TempDataTable SQL = "UPDATE " + Constants.TempDataTableName + " AS T INNER JOIN " + this._DBQueries.TablesName.SubgroupVals + " AS S ON T." + SubgroupVals.SubgroupVal + " = S." + SubgroupVals.SubgroupVal + " SET T." + SubgroupVals.SubgroupValGId + " = S." + SubgroupVals.SubgroupValGId + " WHERE T." + SubgroupVals.SubgroupVal + " IS NOT Null AND T." + SubgroupVals.SubgroupVal + " <> ''"; this._DBConnection.ExecuteNonQuery(SQL); } } catch (Exception ex) { throw; } }