private void ImportAreas()
{
string sSQL = string.Empty;
System.Data.Common.DbDataAdapter Adpt = null;
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.AreaInfo areaInfo = null;
try
{
//- Create New Areas log
sSQL = " Select DISTINCT T." + Area.AreaID + ", T." + Area.AreaName + ", T." + Constants.Log.SkippedSourceFileColumnName +
" FROM " + Constants.TempDataTableName + " AS T LEFT JOIN " + this._DBQueries.TablesName.Area + " AS A " +
" ON T." + Area.AreaID + " = A." + Area.AreaID + " where A." + Area.AreaNId + " IS null AND (T." + Area.AreaID + " Is Not Null)";
this.NewAreaImported = this._DBConnection.ExecuteDataTable(sSQL);
//- Insert Distinct unmatched Areas from TempDataTable into UT_Area.
foreach (DataRow drArea in this.NewAreaImported.Rows)
{
areaInfo = new DevInfo.Lib.DI_LibBAL.DA.DML.AreaInfo();
areaInfo.Name = Convert.ToString(drArea[Area.AreaName]);
areaInfo.ID = Convert.ToString(drArea[Area.AreaID]);
areaInfo.GID = Convert.ToString(drArea[Area.AreaID]);
areaInfo.ParentNid = -1;
areaInfo.Parent = new DevInfo.Lib.DI_LibBAL.DA.DML.AreaInfo();
Database.DIArea.CheckNCreateArea(areaInfo);
}
//foreach (DataRow Row in this._DBConnection.DILanguages(this._DBQueries.DataPrefix).Rows)
//{
// // Get Object of CPDQueries for Current Language
// DIQueries LangBasedDBQueries = new DIQueries(this._DBQueries.DataPrefix, Row[Language.LanguageCode].ToString());
// sSQL = "Insert into " + LangBasedDBQueries.TablesName.Area + " (" + Area.AreaParentNId + ", " + Area.AreaID + ", " + Area.AreaName + ", " + Area.AreaGId + "," + Area.AreaLevel + ")" +
// " Select DISTINCT -1, T." + Area.AreaID + ", T." + Area.AreaName + ", T." + Area.AreaID + ", 1 " +
// " FROM " + Constants.TempDataTableName + " AS T LEFT JOIN " + LangBasedDBQueries.TablesName.Area + " AS A " +
// " ON T." + Area.AreaID + " = A." + Area.AreaID + " where A." + Area.AreaNId + " IS null AND (T." + Area.AreaID + " Is Not Null)";
// this._DBConnection.ExecuteNonQuery(sSQL);
//}
}
catch
{
}
finally
{
}
}
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;
}
}