/// <summary>
/// Rename Data_value and TextualValue column into OrgData_value and OrgTextualValue
/// and merge both column value into new column Data_Value
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="dbQueries"></param>
public static void MergeTextualandNumericDataValueColumn(string databaseFileNameWPath)
{
try
{
using (DIConnection DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, databaseFileNameWPath, string.Empty, string.Empty))
{
DIQueries DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault()));
MergeTextualandNumericDataValueColumn(DBConnection, DBQueries);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Returns the database language code
/// </summary>
/// <param name="dbConnection"></param>
/// <returns></returns>
public static string GetDBLanguageCode(DIConnection dbConnection)
{
string RetVal = string.Empty;
string DataPrefix = dbConnection.DIDataSetDefault();
if (dbConnection.IsValidDILanguage(DataPrefix, DataAdmin.DAAppliationLangCode))
{
RetVal = "_" + DataAdmin.DAAppliationLangCode;
}
else
{
RetVal = dbConnection.DILanguageCodeDefault(DataPrefix);
}
return RetVal;
}
public DatabaseIncrementBuilder(string sourceDBLocation, string TargetDBLocation)
{
string DataPrefix, LangCode = string.Empty;
SourceConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, sourceDBLocation, string.Empty, "unitednations2000");
TargetConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, TargetDBLocation, string.Empty, "unitednations2000");
DataPrefix = SourceConnection.DIDataSetDefault();
LangCode = SourceConnection.DILanguageCodeDefault(DataPrefix);
SourceQueries = new DIQueries(DataPrefix, LangCode);
DataPrefix = TargetConnection.DIDataSetDefault();
LangCode = TargetConnection.DILanguageCodeDefault(DataPrefix);
TargetQueries = new DIQueries(DataPrefix, LangCode);
this.SourceDbFile = sourceDBLocation;
this.TargetDbFile = TargetDBLocation;
}
/// <summary>
/// Returns the DIQueries object
/// </summary>
/// <param name="dbConnection"></param>
/// <returns></returns>
public static DIQueries GetDBQueries(DIConnection dbConnection)
{
DIQueries RetVal = null;
string LanguageCode = string.Empty;
string DataPrefix = dbConnection.DIDataSetDefault();
if (dbConnection.IsValidDILanguage(DataPrefix, DataAdmin.DAAppliationLangCode))
{
LanguageCode = "_" + DataAdmin.DAAppliationLangCode;
}
else
{
LanguageCode = dbConnection.DILanguageCodeDefault(DataPrefix);
}
RetVal = new DIQueries(DataPrefix, LanguageCode);
return RetVal;
}
public bool CreateMetadataFromSDMX(string templateFileName,string tempFolderPath, string languageCode,string sdmxRegistryUrl)
{
bool RetVal = true;
DIConnection DBConnection = null;
DIQueries DBQueries;
try
{
//create temp template file
DIDatabase TempTemplateFile = new DIDatabase(templateFileName, "UT_", languageCode);
// create database object
DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, templateFileName, string.Empty, string.Empty);
DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), languageCode);
// delete default categories from blank template
DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataCategory.Delete.DeleteMetadataCategory(DBQueries.TablesName.MetadataCategory, string.Empty));
// import data from SDMX
BaseSDMXHelper HelperObj = new BaseSDMXHelper(DBConnection, DBQueries);
string TempXmlFile = tempFolderPath + "\\IMPORT_MSDXML_" + this.GetDateTimeStamp() + DICommon.FileExtension.XML;
HelperObj.GenerateMetadataXMLFromSDMXWebservice(sdmxRegistryUrl, TempXmlFile);
}
catch (Exception ex)
{
RetVal = false;
}
finally
{
// dispose database object
if (DBConnection != null)
{
DBConnection.Dispose();
}
}
return RetVal;
}
public static DIQueries GetMapServerQueries(string DataPrefix_UI, string LanguageCode_UI, DIConnection connection)
{
//TO Do GetConnection from web service
DIQueries RetVal = null;
try
{
if (connection.DIDataSets().Select(DBAvailableDatabases.AvlDBPrefix + "='" + DataPrefix_UI.Trim('_') + "'").Length == 0)
{
DataPrefix_UI = connection.DIDataSetDefault();
}
if (connection.DILanguages(DataPrefix_UI).Select(Language.LanguageCode + "='" + LanguageCode_UI + "'").Length == 0)
{
LanguageCode_UI = connection.DILanguageCodeDefault(DataPrefix_UI);
}
RetVal = new DIQueries(DataPrefix_UI, LanguageCode_UI);
}
catch (Exception ex)
{
throw ex;
}
return RetVal;
}
public static DataTable GetAllDBLangaugeCodesByDbNid(int DBNid, DIConnection diConnection)
{
DataTable RetVal = null;
//DIConnection DIConnection;
if (DBNid != 0)
{
//DIConnection = Global.GetDbConnection(DBNid);
RetVal = diConnection.DILanguages(diConnection.DIDataSetDefault());
}
return RetVal;
}
/// <summary>
/// Insert Newly added records from DES to database
/// </summary>
/// <param name="sDestDBPath">Destination Database Path</param>
/// <param name="MsAccessPwd">Password</param>
/// <param name="sLng_Suffix">Lang suffix</param>
public void InsertNewRecords(String tempDESheetPath, string sDestDBPath, String MsAccessPwd)
{
DataView oDV = null;
DataRowView oDVRow;
string sDB_Prefix;
string sQry = "";
DIConnection DBConnection;
int i;
int j;
int iIndicatorNId = 0;
int iUnitNId = 0;
int[] iSubgroupNId = new int[this.DESheetInformation.Subgroup.Length];
int[] iIUSNId = new int[this.DESheetInformation.Subgroup.Length];
int iSectorNID = 0;
//int[] iClassNId = new int[Strings.Split(this.DESheetInformation.ClassGUID, "{}").Length];
int[] iClassNId = new int[this.DESheetInformation.ClassGUID.Split("{}".ToCharArray()).Length];
int iParentNId;
//Used for Class and Source
bool bNewIUS = false;
Boolean bNewSector = false;
bool bNewClass = false;
//*** True if any of I / U / S is new
bool SectorGlobal = false;
// -- Create New DiConnection with db as sDestDBPath
DBConnection = new DIConnection(DIServerType.MsAccess, "", "", sDestDBPath, "", MsAccessPwd);
//--- Using DAL for getting Database default Prefix
sDB_Prefix = DBConnection.DIDataSetDefault();
String sLng_Suffix = DBConnection.DILanguageCodeDefault(sDB_Prefix);
DIQueries DBQueries = new DIQueries(sDB_Prefix, DBConnection.DILanguageCodeDefault(sDB_Prefix));
// Get SectorNId
iSectorNID = GetSectorNId(DBConnection, DBQueries, out bNewSector, out bNewSector);
//*** If Selected Class has not been inserted by export routine then insert new Class
string[] sClassGuid;
string[] sClassName;
string[] sClassGlobal;
iParentNId = iSectorNID;
//sClassGuid = Strings.Split(this.DESheetInformation.ClassGUID,"{}");
sClassGuid = this.DESheetInformation.ClassGUID.Split("{}".ToCharArray());
sClassName = this.DESheetInformation.ClassName.Split("{}".ToCharArray());
sClassGlobal = this.DESheetInformation.ClassGlobal.Split("{}".ToCharArray());
//*** BugFix 29 Aug 2006 Do not try to insert class into database if indicator is directly associated with sector
if (this.DESheetInformation.ClassGUID != "" & this.DESheetInformation.ClassName != "")
{
for (i = 0; i <= sClassGuid.Length - 1; i++)
{
iClassNId[i] = GetClassNId(DBConnection, DBQueries, out bNewClass, sClassGuid[i].ToString(), SectorGlobal, iParentNId, sClassName[i].ToString());
iParentNId = Convert.ToInt32(iClassNId[i]);
}
}
//*** Indicator_NId
sQry = DBQueries.Indicators.GetIndicator(FilterFieldType.GId, "'" + this.DESheetInformation.IndicatorGUID + "'", FieldSelection.NId);
iIndicatorNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iIndicatorNId == 0)
{
bNewIUS = true;
//-- Getting insert query using DAL
sQry = DI_LibDAL.Queries.Indicator.Insert.InsertIndicator(sDB_Prefix, sLng_Suffix, this.DESheetInformation.Indicator.Replace("'", "''"), this.DESheetInformation.IndicatorGUID, "", false);
//-- Using DAL for query execution and getting identity value
DBConnection.ExecuteNonQuery(sQry);
iIndicatorNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
//*** Unit_NId
//-- Using DAL for getting and executing query for getting unit NId
sQry = DBQueries.Calculates.GetUnitNIdByGId(this.DESheetInformation.UnitGUID);
iUnitNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iUnitNId == 0)
{
bNewIUS = true;
//-- Getting insert query using DAL
sQry = DI_LibDAL.Queries.Unit.Insert.InsertUnit(sDB_Prefix, sLng_Suffix, this.DESheetInformation.Unit.Replace("'", "''"), this.DESheetInformation.UnitGUID, false);
//-- Using DAL for query execution and getting identity value
DBConnection.ExecuteNonQuery(sQry);
iUnitNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
//*** Subgroup_Val_NId
for (i = 0; i <= iSubgroupNId.Length - 1; i++)
{
sQry = DBQueries.Calculates.GetSubgroupValNIdByGId(this.DESheetInformation.SubgroupGUID[i]);
iSubgroupNId[i] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iSubgroupNId[i] == 0)
{
bNewIUS = true;
//-- Getting insert query using DAL
sQry = DBQueries.Calculates.InsertSubgroupVal(this.DESheetInformation.Subgroup[0].Replace("'", "''"), this.DESheetInformation.SubgroupGUID[0], false);
//-- Using DAL for query execution and getting identity value
DBConnection.ExecuteNonQuery(sQry);
iSubgroupNId[i] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
}
//*** IUSNId
if (bNewIUS)
{
//*** Insert New IUS
for (i = 0; i <= iSubgroupNId.Length - 1; i++)
{
//-- Get query from DAL
sQry = DBQueries.Calculates.InsertNIdsInIUSTable(iIndicatorNId, iUnitNId, iSubgroupNId[i]);
//-- Using DAL for query execution and getting identity value
DBConnection.ExecuteNonQuery(sQry);
iIUSNId[i] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
//*** Create Relationship with IndicatorClassification
string sICGuid;
sClassGuid = this.DESheetInformation.ClassGUID.Split("{}".ToCharArray());
sICGuid = "'" + this.DESheetInformation.SectorGUID + "'";
for (j = 0; j <= sClassGuid.Length - 1; j++)
{
sICGuid += ",'" + sClassGuid[j].ToString() + "'";
}
sQry = DBQueries.Calculates.GetICNIdByGId(sICGuid);
//-- Get Dataview using DAL
oDV = DBConnection.ExecuteDataTable(sQry).DefaultView;
foreach (DataRowView oDVRow1 in oDV)
{
//-- Using DAL for inserting IC IUS relation
sQry = DBQueries.Calculates.InsertIC_IUSRelation(Convert.ToInt32(oDVRow1["IC_NId"]), iIUSNId[i]);
DBConnection.ExecuteNonQuery(sQry);
}
}
}
else if (bNewSector || bNewClass)
{
for (i = 0; i <= iSubgroupNId.Length - 1; i++)
{
sQry = DBQueries.IUS.GetIUSByI_U_S(iIndicatorNId.ToString(), iUnitNId.ToString(), iSubgroupNId[i].ToString());
iIUSNId[i] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (bNewSector)
{
//-- Using DAL for inserting IC IUS relation
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSectorNID, iIUSNId[i]);
DBConnection.ExecuteNonQuery(sQry);
}
if (bNewClass)
{
for (j = 0; j <= iClassNId.Length - 1; j++)
{
//-- Using DAL for inserting IC IUS relation
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iClassNId[j], iIUSNId[i]);
try
{
DBConnection.ExecuteNonQuery(sQry);
}
catch (Exception ex)
{
}
}
}
}
}
else
{
int Index = 0;
foreach (int SubgroupNId in iSubgroupNId)
{
//-- Using DAL Query for getting IUSNId
sQry = DBQueries.Calculates.GetIUSNIdByIUS(iIndicatorNId, iUnitNId, SubgroupNId);
iIUSNId[Index] = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
Index += 1;
}
}
string sTimePeriod = string.Empty;
int iTimePeriod_NId = 0;
Hashtable oTimePeriod = new Hashtable();
string sAreaId = string.Empty;
int iArea_NId = 0;
Hashtable oAreaId = new Hashtable();
string sDataValue = string.Empty;
System.DateTime dtStartDate = System.DateTime.Now;
System.DateTime dtEndDate = System.DateTime.Now;
string sSubgroup = string.Empty;
string sSource = string.Empty;
int iSource_NId = 0;
Hashtable oSource = new Hashtable();
string sFootNote = "";
int iFootNote_NId = -1;
Hashtable oFootNote = new Hashtable();
string sData_Denominator = "";
IWorksheet DataSheet;
DIDatabase DIdatabase = null;
this.DIExcel = new DIExcel(tempDESheetPath);
// this.DIExcel = new DIExcel(this.TempLogFilePath);
DataSheet = DIExcel.GetWorksheet(0);
{
//For each record in excel sheet '*** Get TimePeriod_NId, Area_NId, Data Value, Subgroup, SourceNId
for (i = 10; i <= miCtrFillTo; i++)
{
try
{
if (DataSheet.Cells[i, 0].Value.ToString() == "" || DataSheet.Cells[i, 1].Value.ToString() == "" || DataSheet.Cells[i, 3].Value.ToString() == "" || DataSheet.Cells[i, 4].Value.ToString() == "" || DataSheet.Cells[i, 5].Value.ToString() == "")
{
}
//*** If TimePeriod, AreaID, DataValue,Subgroup, Source is blank leave this record
else
{
//*** TimePeriod
sTimePeriod = DataSheet.Cells[i, 0].Value.ToString();
iTimePeriod_NId = -1;
if (CheckDate(sTimePeriod))
{
//*** Check for Valid TimeFormat allowed in DE
if (oTimePeriod.ContainsKey(sTimePeriod))
{
iTimePeriod_NId = Convert.ToInt32(oTimePeriod[sTimePeriod]);
}
else
{
sQry = DBQueries.Calculates.GetTimeperiodNIdByTimePeriod(sTimePeriod);
iTimePeriod_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iTimePeriod_NId == 0)
{
// Using DAL for getting and executing Query for inserting timeperiod
sQry = DI_LibDAL.Queries.Timeperiod.Insert.InsertTimeperiod(sDB_Prefix, sTimePeriod);
DBConnection.ExecuteNonQuery(sQry);
iTimePeriod_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
oTimePeriod.Add(sTimePeriod, iTimePeriod_NId);
}
SetDate(sTimePeriod, ref dtStartDate, ref dtEndDate);
}
else
{
iTimePeriod_NId = -1;
}
//*** Area
sAreaId = DataSheet.Cells[i, 1].Value.ToString();
iArea_NId = -1;
if (oAreaId.ContainsKey(sAreaId))
{
iArea_NId = Convert.ToInt32(oAreaId[sAreaId]);
}
else
{
//-- Using DAL for getting AreaNId Using AreaID
sQry = DBQueries.Calculates.GetAreaNIdByAreaID(sAreaId);
iArea_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iArea_NId == 0)
{
oAreaId.Add(sAreaId, -1);
}
//*** Don't add New areas to database
else
{
oAreaId.Add(sAreaId, iArea_NId);
}
}
//*** Data value
sDataValue = DataSheet.Cells[i, 3].Value.ToString();
//*** Subgroup
sSubgroup = DataSheet.Cells[i, 4].Value.ToString();
//*** Source
sSource = DI_LibBAL.Utility.DICommon.RemoveQuotes(DataSheet.Cells[i, 5].Value.ToString());
iSource_NId = -1;
if (oSource.ContainsKey(sSource))
{
iSource_NId = Convert.ToInt32(oSource[sSource]);
}
else
{
sQry = DBQueries.IndicatorClassification.GetIC(FilterFieldType.Name, " '" + sSource + "'", ICType.Source, FieldSelection.NId);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iSource_NId == 0)
{
string[] sSourceArr;
sSourceArr = sSource.Split('_');
//Publisher_ISOCode_Year: Parent= Publisher; Child= Abbr_Year
if (sSourceArr.Length >= 2)
{
//*** Insert Parent
sQry = DBQueries.IndicatorClassification.GetIC(FilterFieldType.Name, " '" + sSourceArr[0] + "'", ICType.Source, FieldSelection.NId);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iSource_NId == 0)
{
sQry = DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(sDB_Prefix, sLng_Suffix, sSourceArr[0], Guid.NewGuid().ToString(), false, -1, "", ICType.Source);
DBConnection.ExecuteNonQuery(sQry);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
//*** Create relationship
for (j = 0; j <= iIUSNId.Length - 1; j++)
{
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]);
//'sQry = "INSERT INTO " & sDB_Prefix & "Indicator_Classifications_IUS" & " (IC_NId,IUSNId) VALUES (" & iSource_NId & "," & iIUSNId(j) & ")"
try
{
DBConnection.ExecuteNonQuery(sQry);
}
catch (Exception ex)
{
}
}
//*** Insert Source
// Using DAL for inserting Source
sQry = DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(sDB_Prefix, sLng_Suffix, sSource.Replace("'", "''"), Guid.NewGuid().ToString(), false, iSource_NId, "", ICType.Source);
DBConnection.ExecuteNonQuery(sQry);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
//*** Create relationship
for (j = 0; j <= iIUSNId.Length - 1; j++)
{
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]);
try
{
DBConnection.ExecuteNonQuery(sQry);
}
//' oDestDB.ExecuteNonQuery(sQry)
catch (Exception ex)
{
}
}
}
else
{
//iSource_NId = -1
if (sSource.Trim() != "")
{
//*** Insert Parent as "Global"
sQry = DBQueries.IndicatorClassification.GetIC(FilterFieldType.Name, "Global", ICType.Source, FieldSelection.NId);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iSource_NId == 0)
{
sQry = DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(sDB_Prefix, sLng_Suffix, "Global", Guid.NewGuid().ToString(), false, -1, "", ICType.Source);
DBConnection.ExecuteNonQuery(sQry);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
//*** Create relationship
for (j = 0; j <= iIUSNId.Length - 1; j++)
{
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]);
try
{
DBConnection.ExecuteNonQuery(sQry);
}
catch (Exception ex)
{
}
}
//*** Insert Source
sQry = DI_LibDAL.Queries.IndicatorClassification.Insert.InsertIC(sDB_Prefix, sLng_Suffix, "Global_" + sSource.Replace("'", "''"), Guid.NewGuid().ToString(), false, iSource_NId, "", ICType.Source);
DBConnection.ExecuteNonQuery(sQry);
iSource_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
//*** Create relationship
for (j = 0; j <= iIUSNId.Length - 1; j++)
{
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]);
try
{
DBConnection.ExecuteNonQuery(sQry);
}
catch (Exception ex)
{
}
}
}
}
}
else
{
//*** If New IUS was created then Create Relationship with Source
if (bNewIUS)
{
// using DAL query for getting parentNId
sQry = DBQueries.Calculates.GetICParentNIdByICNId(iSource_NId, "SR");
iParentNId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iParentNId != 0)
{
for (j = 0; j <= iIUSNId.Length - 1; j++)
{
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iParentNId, iIUSNId[j]);
try
{
DBConnection.ExecuteNonQuery(sQry);
}
catch (Exception ex)
{
//*** database maintains unique composite key for IC_NId and IUSNId - this will prevent duplicate entry if any
}
}
}
for (j = 0; j <= iIUSNId.Length - 1; j++)
{
sQry = DBQueries.Calculates.InsertIC_IUSRelation(iSource_NId, iIUSNId[j]);
try
{
DBConnection.ExecuteNonQuery(sQry);
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
}
}
}
oSource.Add(sSource, iSource_NId);
}
try
{
sFootNote = DataSheet.Cells[i, 6].Value.ToString();
}
catch (Exception ex)
{
sFootNote = "";
}
iFootNote_NId = -1;
if (sFootNote != "")
{
if (oFootNote.ContainsKey(sFootNote))
{
iFootNote_NId = Convert.ToInt32(oFootNote[sFootNote]);
}
else
{
sQry = DBQueries.Calculates.GetFootNoteNIdByFootNote(Utility.DICommon.EscapeWildcardChar(Utility.DICommon.RemoveQuotes(sFootNote)));
iFootNote_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery(sQry));
if (iFootNote_NId == 0)
{
sQry = DI_LibDAL.Queries.Footnote.Insert.InsertFootnote(sDB_Prefix, sLng_Suffix, sFootNote, Guid.NewGuid().ToString());
DBConnection.ExecuteNonQuery(sQry);
iFootNote_NId = Convert.ToInt32(DBConnection.ExecuteScalarSqlQuery("SELECT @@IDENTITY"));
}
oFootNote.Add(sFootNote, iFootNote_NId);
}
}
try
{
if (Utility.DICommon.IsNumeric(DataSheet.Cells[i, 7].Value.ToString(), System.Threading.Thread.CurrentThread.CurrentCulture))
{
sData_Denominator = DataSheet.Cells[i, 7].Value.ToString() + ",";
}
else
{
sData_Denominator = "";
}
}
catch (Exception ex)
{
sData_Denominator = "";
}
if (iIUSNId[0] == -1 | iTimePeriod_NId == -1 | iArea_NId == -1 | iSource_NId == -1 | sDataValue == "")
{
}
else
{
if (this.ApplicationWizardType == WizardType.HundredMinus)
{
sQry = DBQueries.Calculates.InsertDataForCalculate(iIUSNId[Array.IndexOf(this.DESheetInformation.Subgroup, sSubgroup)], iTimePeriod_NId, iArea_NId, sDataValue, "#" + dtStartDate.ToString("MM/dd/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "#", "#" + dtEndDate.ToString("MM/dd/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "#", "", iFootNote_NId, iSource_NId);
DBConnection.ExecuteNonQuery(sQry);
}
else
{
sQry = DBQueries.Calculates.InsertDataForCalculate(iIUSNId[Array.IndexOf(this.DESheetInformation.Subgroup, sSubgroup)], iTimePeriod_NId, iArea_NId, sDataValue, "#" + dtStartDate.ToString("MM/dd/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "#", "#" + dtEndDate.ToString("MM/dd/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo) + "#", sData_Denominator, iFootNote_NId, iSource_NId);
DBConnection.ExecuteNonQuery(sQry);
}
}
}
}
catch (Exception ex)
{
}
}
// Update Indicator Unit subgroupNId
DIdatabase = new DIDatabase(DBConnection, DBQueries);
DIdatabase.UpdateIndicatorUnitSubgroupNIDsInData();
DBConnection.Dispose();
}
//1.Time - 2.AreaId - 3.AreaName - 4.DataValue - 5.Subgroup - 6.Source - 7.Footnote - 8.Denominator - 12.SubgroupNId
//DataSheet = null;
if ((oDV != null))
{
oDV.Dispose();
oDV = null;
}
}
internal void ImportAssistants()
{
DIConnection SourceDBConnection = null;
DIQueries SourceDBQueries = null;
string DataPrefix = string.Empty;
string LanguageCode = string.Empty;
//get all source database name
foreach (string SourceFileNameWPath in this.SourceDatabaseFileNamesWPath)
{
//for each source database, import notes
try
{
SourceDBConnection = new DIConnection(new DIConnectionDetails(DIServerType.MsAccess,
string.Empty, string.Empty, SourceFileNameWPath, string.Empty, Common.Constants.DBPassword));
DataPrefix = SourceDBConnection.DIDataSetDefault();
LanguageCode = SourceDBConnection.DILanguageCodeDefault(DataPrefix);
SourceDBQueries = new DIQueries(DataPrefix, LanguageCode);
this.ImportAssistant(SourceDBConnection, SourceDBQueries);
}
catch (Exception)
{
}
finally
{
if (SourceDBConnection != null)
{
SourceDBConnection.Dispose();
this._TargetDBConnection.Dispose();
}
if (SourceDBQueries != null)
{
SourceDBQueries = null;
}
}
}
}
/// <summary>
/// Create chache results for a language
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="languageCode"></param>
private void CreateCacheResults(DIConnection dbConnection, string languageCode)
{
DITables TableNames;
try
{
//-- Get all tables by dataset and language basis
TableNames = new DITables(dbConnection.DIDataSetDefault(), languageCode);
//-- Create cache table (DI_Search_Results)
this.CreateCacheResultsForDISearchResults(dbConnection, languageCode, TableNames);
//-- Block
this.CreateCacheResultsForBlock(dbConnection, languageCode, TableNames);
//-- Quick Search (level)
this.CreateCacheResultsForLevel(dbConnection, languageCode, TableNames);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// check is DI7 database
/// </summary>
/// <returns></returns>
public static bool IsDI7Database(string databaseFileNameWPath)
{
bool RetVal = false;
string DataPrefix = string.Empty;
DIConnection diConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, databaseFileNameWPath, string.Empty, "unitednations2000");
DIQueries diQueries;
if (diConnection != null)
{
DataPrefix = diConnection.DIDataSetDefault();
diQueries = new DIQueries(DataPrefix, diConnection.DILanguageCodeDefault(DataPrefix));
if (diConnection.ExecuteDataTable(diQueries.DBVersion.GetRecords(DevInfo.Lib.DI_LibBAL.Converter.Database.Constants.Versions.DI7_0_0_0)).Rows.Count > 0)
{
RetVal = true;
}
}
return RetVal;
}
/// <summary>
/// Returns true if template or database is in DevInfo SP2 format
/// </summary>
/// <param name="fileNameWPath"></param>
/// <returns></returns>
/// <remarks>For MsAccess</remarks>
public static bool ISDevInfoSP2Database(string fileNameWPath)
{
bool RetVal = false;
DIConnection DBConnection = null;
DIQueries DBQueries = null;
try
{
DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, fileNameWPath, string.Empty, string.Empty);
DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault()));
RetVal = DICommon.ISDevInfoSP2Database(DBConnection, DBQueries, false);
}
catch (Exception)
{
RetVal = false;
// Do NOT throw any exception.
}
finally
{
if (DBConnection != null)
{
DBConnection.Dispose();
}
}
return RetVal;
}
/// <summary>
/// Returns true if template or database is in current DevInfo 6.0 format
/// </summary>
/// <param name="fileNameWPath"></param>
/// <returns></returns>
/// <remarks>For MsAccess</remarks>
public static bool ISDevInfoLatestDatabase(string fileNameWPath, ref string dbFilePostFix)
{
bool RetVal = false;
DIConnection DBConnection = null;
DIQueries DBQueries = null;
DBConverterDecorator DBConverterObject;
DI7_0_0_0DBConverter DI7_DatabaseConvertor;
try
{
DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, fileNameWPath, string.Empty, string.Empty);
DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault()));
DI7_DatabaseConvertor = new DI7_0_0_0DBConverter(DBConnection, DBQueries);
RetVal = DI7_DatabaseConvertor.IsValidDB(false);
dbFilePostFix = DI7_DatabaseConvertor.DBFilePostfix;
if (RetVal)
{
// if database/tempalte already in DI7 format then convert it into latest format
DBConverterObject = new DBConverterDecorator(DBConnection, DBQueries);
DBConverterObject.DoConversion(false);
}
}
catch (Exception)
{
RetVal = false;
// Do NOT throw any exception.
}
finally
{
if (DBConnection != null)
{
DBConnection.Dispose();
}
}
return RetVal;
}
public DataTable GetQDSSubgroupDetails(DIConnection dbConnection, string languageCode)
{
DataTable RetVal = null;
StringBuilder SBQry = new StringBuilder();
string StrQry = string.Empty;
DITables TableNames;
try
{
//-- Get table names for current dataset and language
TableNames = new DITables(dbConnection.DIDataSetDefault(), languageCode);
//-- Get QDSSubgroupDetailsTable
SBQry.Append("SELECT SVS." + SubgroupValsSubgroup.SubgroupValNId + ", sdv." + Subgroup.SubgroupName + " AS DimensionValue, SD." + SubgroupTypes.SubgroupTypeName + " AS Dimension,");
SBQry.Append(" SD." + SubgroupTypes.SubgroupTypeNId + " AS DimensionNId");
SBQry.Append(" FROM " + TableNames.SubgroupType + " AS SD INNER JOIN (" + TableNames.Subgroup + " AS SDV INNER JOIN "+ TableNames.SubgroupValsSubgroup +" AS SVS");
SBQry.Append(" ON SDV." + Subgroup.SubgroupNId + " = SVS." + SubgroupValsSubgroup.SubgroupNId + ") ON SD." + SubgroupTypes.SubgroupTypeNId + " = SDV." + Subgroup.SubgroupType);
StrQry = SBQry.ToString();
RetVal = dbConnection.ExecuteDataTable(StrQry);
}
catch (Exception)
{
throw;
}
return RetVal;
}
public DataSet GetQDSResults(DIConnection dbConnection, string searchIndicators, string searchICs, string searchAreas, string searchLanguage, bool isBlockResults)
{
DataSet RetVal = new DataSet();
string CatalogAdaptationUrl = string.Empty;
DataTable DtQDSResults = new DataTable();
DataTable DtQDSChildResults = new DataTable();
string NumericSearchAreas = string.Empty;
string QSSearchAreas = string.Empty;
DITables TableNames;
try
{
//-- Get all tables by dataset and language basis
TableNames = new DITables(dbConnection.DIDataSetDefault(), searchLanguage);
if (string.IsNullOrEmpty(searchAreas))
{
string[] DefaultAreaArr = this.GetDefaultAreasArr(dbConnection, TableNames);
if (DefaultAreaArr.Length > 0)
{
searchAreas = DefaultAreaArr[0];
}
}
#region "-- Seperate Normal and QS areas --"
//-- Seperate Numric and QS area in variable
foreach (string SptText in DICommon.SplitString(searchAreas, ","))
{
if (DICommon.IsNumeric(SptText))
{
NumericSearchAreas += "," + SptText;
}
else
{
QSSearchAreas += "," + SptText;
}
}
if (!string.IsNullOrEmpty(NumericSearchAreas))
{
searchAreas = NumericSearchAreas.Substring(1);
}
else
{
searchAreas = string.Empty;
}
if (!string.IsNullOrEmpty(QSSearchAreas))
{
QSSearchAreas = QSSearchAreas.Substring(1);
}
#endregion
if (string.IsNullOrEmpty(searchIndicators))
{
string[] DefaultIndArr = this.GetDefaultIndicatorsArr(dbConnection, TableNames);
if (DefaultIndArr.Length > 0)
{
searchIndicators = DefaultIndArr[0];
}
}
else
{
#region "-- Seperate indicator and IC --"
string IndNIds = string.Empty;
//-- Seperate Numric and QS area in variable
foreach (string SptText in DICommon.SplitString(searchIndicators, ","))
{
if (SptText.IndexOf("ic_") > -1)
{
IndNIds += "," + DI7OfflineSPHelper.GetIndicatorNIdsFromICNId(dbConnection, TableNames, Convert.ToInt32(SptText.Substring(3)));
}
else
{
IndNIds += "," + SptText;
}
}
if (!string.IsNullOrEmpty(IndNIds))
{
searchIndicators = IndNIds.Substring(1);
}
#endregion
}
#region "-- Get Parent Data --"
//-- Create an empty Parent Table, TmpSearchAreaa, TmpSearchIndicators
CacheUtility.CreateParentTbl(dbConnection, searchLanguage);
DI7OfflineSPHelper.CreateTmpAreaSearchTbl(dbConnection, searchAreas, false);
DI7OfflineSPHelper.CreateTmpIndSearchTbl(dbConnection, TableNames, searchIndicators, false);
//-- Get Record for Normal areas
if (!string.IsNullOrEmpty(searchAreas))
{
DI7OfflineSPHelper.GetSearchResults(dbConnection, searchLanguage, false);
}
#endregion "-- Get Parent Data --"
#region "-- Get Child Data --"
//-- Create an empty Child Table
CacheUtility.CreateChildTbl(dbConnection, searchLanguage);
//-- Get Record for Normal areas
if (!string.IsNullOrEmpty(searchAreas))
{
DtQDSChildResults = DI7OfflineSPHelper.GetSearchChildResults(dbConnection, searchLanguage, searchIndicators, false);
}
//-- Get Record for QS areas
if (!string.IsNullOrEmpty(QSSearchAreas))
{
//-- Create TmpSearchAreaa for QS areas
DI7OfflineSPHelper.CreateTmpAreaSearchTbl(dbConnection, QSSearchAreas, true);
DtQDSChildResults = DI7OfflineSPHelper.GetSearchChildResults(dbConnection, searchLanguage, searchIndicators, true);
}
#endregion "-- Get Child Data --"
#region "-- Reprocess Parent records --"
//-- Create an empty Child Area Table
CacheUtility.CreateChildAreaTbl(dbConnection, searchLanguage);
//-- Get distinct Indicator,unit,area … from ChildTable table and insert into TmpChildArea table
DI7OfflineSPHelper.InsertIntoTmpChildAreaTable(dbConnection, searchLanguage, TableNames);
//-- Use union (first select – parenttable, second table – tmpchildarea) and create new parent table
DI7OfflineSPHelper.GetNewParentTable(dbConnection, searchLanguage);
//-- Delete duplicate record for same IUS
DtQDSResults = DI7OfflineSPHelper.DeletedDuplicateRecordOfSameIUS(dbConnection);
DtQDSResults = this.AddIndicatorDescCol(DtQDSResults, TableNames, dbConnection);
#endregion
//-- Add table into Dataset
RetVal.Tables.Add(DtQDSResults);
RetVal.Tables.Add(DtQDSChildResults);
}
catch (Exception)
{
}
return RetVal;
}
private void UpdateMRDTable(DIConnection ObjDIConnection)
{
string Query = string.Empty;
string TempTableName = "temp1";
string DTData = ObjDIConnection.DIDataSetDefault() + "Data";
// 1. drop temp1 table
try
{
ObjDIConnection.ExecuteNonQuery("Drop table " + TempTableName);
}
catch (Exception)
{
}
// 2. Create Temp1 table for IsMRD calculation
Query = "Select MRDTable.*, T2.Timeperiod_nid into " + TempTableName + " from ( SELECT d.IUSNId, d.Area_NId, MAX(t.TimePeriod) AS timeperiod FROM " + ObjDIConnection.DIDataSetDefault() + "Data" + " d," + ObjDIConnection.DIDataSetDefault() + "TimePeriod" + " t WHERE d.TimePeriod_NId= t.TimePeriod_NId GROUP BY d.IUSNId,d.Area_NId) AS MRDTable , " + ObjDIConnection.DIDataSetDefault() + "TimePeriod" + " T2 where MRDTable.timeperiod=T2.Timeperiod";
ObjDIConnection.ExecuteNonQuery(Query);
// 3. set IsMrd to false in data table
Query = "UPDATE " + ObjDIConnection.DIDataSetDefault() + "Data" + " SET IsMRD=0, MultipleSource=0";
ObjDIConnection.ExecuteNonQuery(Query);
// 4. update IsMrd in DataTable
Query = "UPDATE " + DTData + " SET " + DTData + ".IsMRD=1, " + DTData + ".MultipleSource=1 FROM temp1 INNER JOIN " + DTData + " ON temp1.IUSNId = " + DTData + ".IUSNId AND temp1.Timeperiod_nid = " + DTData + ".TimePeriod_NId AND temp1.Area_NId = " + DTData + ".Area_NId";
ObjDIConnection.ExecuteNonQuery(Query);
//4. drop table
ObjDIConnection.ExecuteNonQuery("Drop table " + TempTableName);
}
/// <summary>
/// Register database in db.xml file
/// </summary>
/// <param name="requestParam"></param>
/// <returns></returns>
public string AdminRegisterDatabase(string requestParam)
{
string RetVal = string.Empty;
string[] Params;
string DBConnectionsFile = string.Empty;
XmlDocument XmlDoc;
XmlNode xmlNode;
string CategoryName = string.Empty;
string ConnName = string.Empty;
string DbConn = string.Empty;
string DefArea = string.Empty;
string Description = string.Empty;
string IsDefDb = string.Empty;
string IsRegCatalog = string.Empty;
XmlElement NewNode;
int NewId = 0;
string[] DBConnArr;
DIConnection ObjDIConnection = null;
string ServerType = string.Empty;
string ServerName = string.Empty;
string DbName = string.Empty;
string UserName = string.Empty;
string Password = string.Empty;
string QryStr = string.Empty;
DataTable DTCounts;
string CountStr = string.Empty;
XmlElement NewCategoryNode;
XmlNodeList ObjXmlNodeList;
int CategoryId = 0;
string DbConnWithEncryptPassword = string.Empty;
string DefAreaJSon = string.Empty;
string DefAreaCount = "0";
string[] DefAreaStr;
string[] DefIndStr;
string DefInd = string.Empty;
string DefIndJSon = string.Empty;
string strDefaultLanguage = string.Empty;
DBConverterDecorator objDBConverterDecorator = null;
DBVersionBuilder VersionBuilder = null;
string DbDefaultLanguage = string.Empty;
string[] DbAvailableLanguage = null;
string DbAvailableLanguageStr = string.Empty;
string isGlobalAllow = ConfigurationManager.AppSettings[Constants.WebConfigKey.IsGlobalAllow];
try
{
requestParam = HttpUtility.UrlDecode(requestParam);
Params = Global.SplitString(requestParam, Constants.Delimiters.ParamDelimiter);
CategoryName = Params[0];
ConnName = Params[1];
DbConn = Params[2];
if (Params.Length > 3)
{
DefArea = Params[3];
}
if (Params.Length > 4)
{
Description = Params[4];
}
if (Params.Length > 6)
{
IsRegCatalog = Params[6];
}
DBConnectionsFile = Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, ConfigurationManager.AppSettings[Constants.WebConfigKey.DBConnectionsFile]);
XmlDoc = new XmlDocument();
XmlDoc.Load(DBConnectionsFile);
xmlNode = XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root + "/" + Constants.XmlFile.Db.Tags.Category + "/" + Constants.XmlFile.Db.Tags.Database + "[@" + Constants.XmlFile.Db.Tags.DatabaseAttributes.Name + "='" + ConnName + "']");
if (xmlNode != null)
{
RetVal = "exists";
}
else
{
xmlNode = XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root + "/" + Constants.XmlFile.Db.Tags.Category + "[@" + Constants.XmlFile.Db.Tags.CategoryAttributes.Name + "='" + CategoryName + "']");
if (xmlNode == null)
{
NewCategoryNode = XmlDoc.CreateElement(Constants.XmlFile.Db.Tags.Category);
NewCategoryNode.SetAttribute(Constants.XmlFile.Db.Tags.CategoryAttributes.Name, CategoryName);
xmlNode = XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root).AppendChild(NewCategoryNode);
}
// Get old higher id
ObjXmlNodeList = XmlDoc.SelectNodes("/" + Constants.XmlFile.Db.Tags.Root + "/" + Constants.XmlFile.Db.Tags.Category + "/" + "child::node()");
foreach (XmlNode data in ObjXmlNodeList)
{
CategoryId = int.Parse(data.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.Id].Value);
if (NewId < CategoryId)
{
NewId = CategoryId;
}
}
NewId++; // Increase 1 for new id
// Split connection details in variables
DBConnArr = Global.SplitString(DbConn, "||");
ServerType = DBConnArr[0];
ServerName = DBConnArr[1];
DbName = DBConnArr[2];
UserName = DBConnArr[3];
if (DBConnArr.Length > 4)
{
Password = DBConnArr[4];
}
ObjDIConnection = new DIConnection(((DIServerType)Convert.ToInt32(ServerType)), ServerName, "", DbName, UserName, Password);
// Get default language for that db
QryStr = "SELECT Language_Code FROM ut_language WHERE Language_Default = 1";
DTCounts = ObjDIConnection.ExecuteDataTable(Regex.Replace(QryStr, "UT_", ObjDIConnection.DIDataSetDefault(), RegexOptions.IgnoreCase));
if (DTCounts.Rows.Count == 1) strDefaultLanguage = DTCounts.Rows[0]["Language_Code"].ToString();
else strDefaultLanguage = "en";
// Get counts from db
QryStr = "select DBMtd_AreaCnt, DBMtd_IndCnt, DBMtd_SrcCnt, DBMtd_DataCnt from ut_dbmetadata_" + strDefaultLanguage;
DTCounts = ObjDIConnection.ExecuteDataTable(Regex.Replace(QryStr, "UT_", ObjDIConnection.DIDataSetDefault(), RegexOptions.IgnoreCase));
DataRow Row = DTCounts.Rows[0];
CountStr = string.Format("{0:0,0}", Row[0]) + "_" + string.Format("{0:0,0}", Row[1]) + "_" + string.Format("{0:0,0}", Row[2]) + "_" + string.Format("{0:0,0}", Row[3]);
DbConnWithEncryptPassword = ServerType + "||" + ServerName + "||" + DbName + "||" + UserName;
if (!string.IsNullOrEmpty(Password))
{
DbConnWithEncryptPassword += "||" + Global.EncryptString(Password);
}
else
{
DbConnWithEncryptPassword += "||";
}
//get default language code in database
DbDefaultLanguage = ObjDIConnection.DILanguageCodeDefault(ObjDIConnection.DIDataSetDefault());
//get all avalilable language code in database
DbAvailableLanguage = Global.GetAllAvailableLanguageCode(ObjDIConnection);
if (DbAvailableLanguage.Length > 0)
{
DbAvailableLanguageStr = string.Join(",", DbAvailableLanguage);
}
if (Global.standalone_registry != "true")
{
this.RunDBScripts(ObjDIConnection, NewId.ToString(), DbDefaultLanguage.Substring(1));
VersionBuilder = new DBVersionBuilder(ObjDIConnection, new DIQueries(ObjDIConnection.DIDataSetDefault(), ObjDIConnection.DILanguageCodeDefault(ObjDIConnection.DIDataSetDefault())));
VersionBuilder.InsertVersionInfo(Constants.DBVersion.DI7_0_0_0, Constants.DBVersion.VersionsChangedDatesDI7_0_0_0, Constants.DBVersion.VersionCommentsDI7_0_0_0);
#region -- Get default values of indicator, area and language --
#region -- get default indicator and their Json data --
DefIndStr = GetDefaultIndicators(ObjDIConnection, strDefaultLanguage);
DefInd = DefIndStr[0];
DefIndJSon = DefIndStr[1];
#endregion
#region --get default area of level 1 and 2 (L1 + L2) by stored procedure with their json and counts --
DefAreaStr = GetDefaultAreas(ObjDIConnection, strDefaultLanguage);
DefArea = DefAreaStr[0];
DefAreaJSon = DefAreaStr[1];
DefAreaCount = DefAreaStr[2];
#endregion
#endregion
#region "Catalog"
if (IsRegCatalog == "true")
{
// Set description filed to update in service database
this.DataBaseDescription = Description.Replace("'", "''").Replace("\n", "<br />").Replace(@"""", @"\""");
if (InsertIntoCatalog(ObjDIConnection, Row, DbAvailableLanguageStr))
{
GetAndUpdateIndexedAreas(DbAvailableLanguage, ObjDIConnection);
GetAndUpdateIndexedIndicators(DbAvailableLanguage, ObjDIConnection);
}
}
#endregion "Catalog"
}
#region -- Set xml tag attribute values and save it in db.xml file --
//Create new element node and set its attributes
NewNode = XmlDoc.CreateElement(Constants.XmlFile.Db.Tags.Database);
NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.Id, NewId.ToString());
NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.Name, ConnName);
NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.SDMXDb, "false");
NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.Count, CountStr);
NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultLanguage, DbDefaultLanguage.Substring(1));
NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultIndicator, DefInd);
NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultIndicatorJSON, DefIndJSon);
NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultArea, DefArea);
NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultAreaJSON, DefAreaJSon);
NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultAreaCount, DefAreaCount);
NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.DatabaseConnection, DbConnWithEncryptPassword);
NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.AvailableLanguage, DbAvailableLanguageStr);
NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.LastModified, string.Format("{0:yyyy-MM-dd}", DateTime.Today.Date));
NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.LanguageCodeCSVFiles, string.Empty);
NewNode.SetAttribute(Constants.XmlFile.Db.Tags.DatabaseAttributes.IsSDMXHeaderCreated, "false");
if (DbAvailableLanguage != null)
{
foreach (string LanguageCode in DbAvailableLanguage)
{
NewNode.SetAttribute("desc_" + LanguageCode, Description);
}
}
xmlNode.AppendChild(NewNode);
XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root).Attributes[Constants.XmlFile.Db.Tags.RootAttributes.Default].Value = NewId.ToString();
//Save xml file
File.SetAttributes(DBConnectionsFile, FileAttributes.Normal);
XmlDoc.Save(DBConnectionsFile);
#endregion
GenerateAllPagesXML();
DeleteSitemapFiles();
RetVal = NewId.ToString();
}
}
catch (Exception ex)
{
Global.WriteErrorsInLogFolder("error in Registering new database");
Global.CreateExceptionString(ex, null);
}
finally
{
if (ObjDIConnection != null)
{
ObjDIConnection.Dispose();
ObjDIConnection = null;
}
}
return RetVal;
}
/// <summary>
/// Returns true if template or database is DevInfo compatible
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="isForOnlineDB"></param>
/// <returns></returns>
/// <remarks>It will validate on the basis of default langauge and dataset</remarks>
public static bool ISVaildDevInfoDatabase(DIConnectionDetails connectionDetails)
{
bool RetVal = false;
bool IsForOnlineDB;
DIConnection DBConnection = null;
DIQueries DbQueries;
DI5SP2DBConverter SP2Converter = null;
try
{
DBConnection = new DIConnection(connectionDetails);
//AvailableDB
DbQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault()));
// check DB_Available_Databases table exists
if (!string.IsNullOrEmpty(DBConnection.DIDataSetDefault()))
{
RetVal = true;
////// if exists then check for SP2 database
////SP2Converter = new DI5SP2DBConverter(DBConnection, DbQueries);
////switch (connectionDetails.ServerType)
////{
//// case DIServerType.Excel:
//// case DIServerType.MsAccess:
//// IsForOnlineDB = false;
//// break;
//// default:
//// IsForOnlineDB = true;
//// break;
////}
////RetVal = SP2Converter.IsValidDB(IsForOnlineDB);
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.Print(ex.Message);
RetVal = false;
}
finally
{
if (DBConnection != null)
{
DBConnection.Dispose();
}
}
return RetVal;
}
/// <summary>
/// Create and alter tables schema in database
/// </summary>
/// <param name="dbConnection"></param>
private void CreateNAlterSchemas(DIConnection dbConnection)
{
string StrQry = string.Empty;
DITables TableNames;
string DataPrefix = string.Empty;
try
{
//-- Get default data prefix in database
DataPrefix = dbConnection.DIDataSetDefault();
//-- Get all table names
TableNames = new DITables(dbConnection.DIDataSetDefault(), dbConnection.DILanguageCodeDefault(dbConnection.DIDataSetDefault()));
//-- Add timeperiod column into data
StrQry = "Alter table "+ TableNames.Data +" add column " + QDSConstants.QDSTables.Data.Columns.TimePeriod + " varchar(100)";
dbConnection.ExecuteNonQuery(StrQry);
//-- Add IsDefaultSG column into data
StrQry = "Alter table "+ TableNames.Data +" add column " + QDSConstants.QDSTables.Data.Columns.ISDefaultSG + " bit";
dbConnection.ExecuteNonQuery(StrQry);
//-- Create TempMRDRecords table
CacheUtility.CreateTempMRDRecordsTable(dbConnection);
//-- Update timeperiod values into data table
StrQry = "UPDATE "+ TableNames.Data +" AS d INNER JOIN "+ TableNames.TimePeriod +" AS t ON d."+ Data.TimePeriodNId +" = t."+ Timeperiods.TimePeriodNId +" SET d."+ QDSConstants.QDSTables.Data.Columns.TimePeriod +" = t." + Timeperiods.TimePeriod;
dbConnection.ExecuteNonQuery(StrQry);
//-- Update default sg into data table
StrQry = "UPDATE "+ TableNames.Data +" AS D INNER JOIN "+ TableNames.IndicatorUnitSubgroup +" AS IUS ON D." + Data.IUSNId + " = IUS." + Indicator_Unit_Subgroup.IUSNId + " SET D."+ QDSConstants.QDSTables.Data.Columns.ISDefaultSG +" = IUS." + Indicator_Unit_Subgroup.IsDefaultSubgroup;
dbConnection.ExecuteNonQuery(StrQry);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Returns true if template or database is DevInfo compatible
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="fileNameWPath"></param>
/// <returns></returns>
/// <remarks>It will validate on the basis of default langauge and dataset</remarks>
public static bool ISVaildDevInfoDatabase(string fileNameWPath)
{
bool RetVal = false;
bool IsForOnlineDB;
DIConnection DBConnection = null;
DIQueries DbQueries;
try
{
DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, fileNameWPath, string.Empty, string.Empty);
//AvailableDB
DbQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault()));
// check DB_Available_Databases table exists
if (!string.IsNullOrEmpty(DBConnection.DIDataSetDefault()))
{
RetVal = true;
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.Print(ex.Message);
RetVal = false;
}
finally
{
if (DBConnection != null)
{
DBConnection.Dispose();
}
}
return RetVal;
}
private bool IsAssistantTblExists(DIConnection dbConnection)
{
bool RetVal = false;
DIQueries DBQueries;
try
{
DBQueries = new DIQueries(dbConnection.DIDataSetDefault(), dbConnection.DILanguageCodeDefault(dbConnection.DIDataSetDefault()));
// Check availability of table
if (dbConnection.ExecuteScalarSqlQuery("SELECT count(*) FROM " + DBQueries.TablesName.Assistant + " WHERE 1=1") != null)
{
RetVal = true;
}
}
catch (Exception)
{
}
return RetVal;
}
/// <summary>
/// Retrun the Db info on the basis of connection name.
/// </summary>
/// <param name="connectionName">Connection Name</param>
/// <returns>database information contains ServerType{[]}ConnectionIndex{[]}DatasetPrefix</returns>
public string DatabaseInfo(string connectionName)
{
string Retval = string.Empty;
try
{
int Index = 0;
foreach (DatabasePreference.OnlineConnectionDetail ConnectionDetail in this._OnlineDatabaseDetails)
{
//-- Set the default database info, if given connection name matches with loop connection name.
if (ConnectionDetail.Connection.ToLower() == connectionName.ToLower())
{
int ServerType = Convert.ToInt32(ConnectionDetail.DIConnectionDetails.ServerType);
DIConnection DIConnection = new DIConnection(ConnectionDetail.DIConnectionDetails);
string DefaultDataSet = DIConnection.DIDataSetDefault();
//-- remove the "_" from prefix.
DefaultDataSet = DefaultDataSet.Substring(0, DefaultDataSet.Length - 1);
Retval = ServerType.ToString() + DELIMITER + Index.ToString() + DELIMITER + DefaultDataSet;
break;
}
Index += 1;
}
}
catch (Exception ex)
{
Retval = string.Empty;
}
return Retval;
}
internal static DataTable Get_Language_Specific_Data_Table(string language, string IndicatorNId, string AreaNIds, string TimePeriodNIds, string SourceNIds, DIConnection DIConnection)
{
DataTable RetVal;
string Query;
RetVal = null;
Query = string.Empty;
try
{
Query = "SELECT Data.Data_Value, Data.Data_Denominator, [Indicator].Indicator_GId, Unit.Unit_GId, SGV.Subgroup_Val_GId,TP.TimePeriod, Area.Area_ID, Source.IC_Name, Source.Nature, Footnote.FootNote FROM";
Query += " ";
Query += "(SELECT Data_Value, Data_Denominator, Indicator_NId, Unit_NId, Subgroup_Val_NId, TimePeriod_NId, Area_NId, Source_NId, FootNote_NId FROM UT_Data WHERE 1 = 1";
if (!string.IsNullOrEmpty(IndicatorNId))
{
Query += "AND Indicator_NId = " + IndicatorNId;
}
if (!string.IsNullOrEmpty(AreaNIds))
{
Query += " ";
Query += "AND Area_NID IN";
Query += " ";
Query += "(" + AreaNIds + ")";
}
if (!string.IsNullOrEmpty(TimePeriodNIds))
{
Query += " ";
Query += "AND TimePeriod_NId IN";
Query += " ";
Query += "(" + TimePeriodNIds + ")";
}
if (!string.IsNullOrEmpty(SourceNIds))
{
Query += " ";
Query += "AND Source_NId IN";
Query += " ";
Query += "(" + SourceNIds + ")";
}
Query += ")";
Query += " ";
Query += " AS Data,";
Query += " ";
Query += "(SELECT Indicator_NId, Indicator_GId FROM UT_Indicator_" + language + ")AS [Indicator],";
Query += " ";
Query += "(SELECT Unit_NId, Unit_GId FROM UT_Unit_" + language + ") AS Unit ,";
Query += " ";
Query += "(SELECT Subgroup_Val_NId, Subgroup_Val_GId FROM UT_Subgroup_Vals_" + language + ") AS SGV ,";
Query += " ";
Query += "(SELECT TimePeriod_NId, TimePeriod FROM UT_TimePeriod)AS TP, ";
Query += " ";
Query += "(SELECT Area_NId, Area_ID FROM UT_Area_" + language + " WHERE Area_Level = " + Global.registryAreaLevel + " ) AS Area, ";
Query += " ";
Query += "(SELECT IC_NId, IC_Name, Nature FROM UT_Indicator_Classifications_" + language + ") AS Source,";
Query += " ";
Query += "(SELECT FootNote_NId, FootNote FROM UT_Footnote_" + language + ") Footnote ";
Query += " WHERE Data.Unit_NId = Unit.Unit_NId AND Data.FootNote_NId = Footnote.FootNote_NId ";
Query += " AND Data.TimePeriod_NId = TP.TimePeriod_NId ";
Query += " AND Data.Area_NId = Area.Area_NId AND Data.Indicator_NId = [Indicator].Indicator_NId ";
Query += " AND Data.Source_NId = Source.IC_NId AND Data.Subgroup_Val_NId = SGV.Subgroup_Val_NId";
//Query += " group by TP.TimePeriod Having Count(Source.IC_Name) > 1";
RetVal = DIConnection.ExecuteDataTable(Regex.Replace(Query, "UT_", DIConnection.DIDataSetDefault(), RegexOptions.IgnoreCase));
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
throw ex;
}
finally
{
}
return RetVal;
}
/// <summary>
/// Set the selected connection details on the basis of DBinfo
/// </summary>
/// <param name="dbInfo"></param>
/// <remarks> In case of online connection, Dbinfo contains : ServerType{[]}ConnectionIndex{[]}DatasetPrefix
/// In case of offline connection, ServerType{[]}AcessdbPath (Applicable for Dekstop application) </remarks>
public void SetSelectedConnectionDetail(string dbInfo)
{
DIConnection DIConnection;
string[] Values = new string[0];
Values = Utility.DICommon.SplitString(dbInfo, DELIMITER);
if (Values.Length == 3)
{
//-- In case of Web, it return the connection for all the servert type.
this.SelectedConnectionDetail = this.OnlineDatabaseDetails[Convert.ToInt32(Values[1])].DIConnectionDetails;
this.SelectedConnectionName = this.OnlineDatabaseDetails[Convert.ToInt32(Values[1])].Connection;
for (int i = 0; i <= this.OnlineDatabaseDetails[Convert.ToInt32(Values[1])].OnlineDatasetInfo.Count - 1; i++)
{
if (this.OnlineDatabaseDetails[Convert.ToInt32(Values[1])].OnlineDatasetInfo[i].Prefix.ToLower() == Values[2].ToLower())
{
//-- set the selected dataset name and its prefix.
this.SelectedDatasetName = this.OnlineDatabaseDetails[Convert.ToInt32(Values[1])].OnlineDatasetInfo[i].DatasetName;
this.SelectedDatasetPrefix = this.OnlineDatabaseDetails[Convert.ToInt32(Values[1])].OnlineDatasetInfo[i].Prefix + "_";
break;
}
}
this.SelectedDbInfo = dbInfo;
DIConnection = new DIConnection(this.SelectedConnectionDetail);
//-- Seleted dataset language
if (DIConnection.DILanguages(this.SelectedDatasetPrefix).Select(DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Language.LanguageCode + " = '" + this._DatabaseLanguage.Substring(1) + "'").Length > 0)
{
//-- If the database language exists in the database
this.DatabaseLanguage = this._DatabaseLanguage;
}
else
{
//-- Set the default language as databaseLanguage, if the last selected language does not exists in the database.
this.DatabaseLanguage = DIConnection.DILanguageCodeDefault(this.SelectedDatasetPrefix);
}
}
else if (Values.Length == 2)
{
//-- Set the connection detail of access db (only for dekstop)
this.SelectedConnectionDetail = new DIConnectionDetails(DIServerType.MsAccess, "", "", Values[1], USERNAME, PASSWORD);
this.SelectedConnectionName = string.Empty;
this.SelectedDatasetName = System.IO.Path.GetFileName(Values[1]);
DIConnection = new DIConnection(this.SelectedConnectionDetail);
this.SelectedDatasetPrefix = DIConnection.DIDataSetDefault();
this.SelectedDbInfo = dbInfo;
//-- Seleted dataset language
this.DatabaseLanguage = DIConnection.DILanguageCodeDefault(this.SelectedDatasetPrefix);
}
}
/// <summary>
/// Get all available language code from database
/// </summary>
/// <param name="diConnection"></param>
/// <returns></returns>
public static string[] GetAllAvailableLanguageCode(DIConnection diConnection)
{
string[] RetVal;
DataTable DTAvailableLanguages = null;
List<string> AvlLanguages = new List<string>();
try
{
DTAvailableLanguages = diConnection.DILanguages(diConnection.DIDataSetDefault());
foreach (DataRow Row in DTAvailableLanguages.Rows)
{
AvlLanguages.Add(Row["Language_Code"].ToString());
}
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
}
RetVal = AvlLanguages.ToArray();
return RetVal;
}
/// Update connection details by selected db
/// </summary>
/// <param name="requestParam"></param>
/// <returns></returns>
public string AdminUpdateDbConnection(string requestParam)
{
string RetVal = string.Empty;
string[] Params;
string DBConnectionsFile = string.Empty;
XmlDocument XmlDoc;
XmlNode xmlNode;
string DbNId = string.Empty;
string ConnName = string.Empty;
string DbConn = string.Empty;
string Password = string.Empty;
string DefArea = string.Empty;
string Description = string.Empty;
string IsDefDb = string.Empty;
string CategoryName = string.Empty;
XmlElement NewCategoryElement;
XmlNode NewXmlNode;
XmlNode CategoryNode;
DIConnection ObjDIConnection = null;
string[] DbAvailableLanguage;
string strDefaultLanguage = string.Empty;
string CountStr = string.Empty;
DataTable DTCounts;
string QryStr = string.Empty;
string DbDefaultLanguage = string.Empty;
string DbAvailableLanguageStr = string.Empty;
string DefAreaJSon = string.Empty;
string DefAreaCount = "0";
string[] DefAreaStr;
string[] DefIndStr;
string DefInd = string.Empty;
string DefIndJSon = string.Empty;
DBConverterDecorator objDBConverterDecorator = null;
DBVersionBuilder VersionBuilder = null;
///Variables for creatin XLSLogfile
string[] DatabaseParams;
string XLSFileMsg = string.Empty;
string Server_HostName = string.Empty;
string DatabaseName = string.Empty;
string DataBaseUserName = string.Empty;
string[] DBConnArr;
try
{
requestParam = HttpUtility.UrlDecode(requestParam);
Params = Global.SplitString(requestParam, Constants.Delimiters.ParamDelimiter);
DbNId = Params[0];
ConnName = Params[1];
DbConn = Params[2];
Password = Params[3];
Description = Params[4];
IsDefDb = Params[5];
CategoryName = Params[6];
DBConnectionsFile = Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, ConfigurationManager.AppSettings[Constants.WebConfigKey.DBConnectionsFile]);
XmlDoc = new XmlDocument();
XmlDoc.Load(DBConnectionsFile);
DBConnArr = DbConn.Split(new string[] { "||" }, StringSplitOptions.None);
ObjDIConnection = new DIConnection(((DIServerType)Convert.ToInt32(DBConnArr[0])), DBConnArr[1].ToString(), string.Empty, DBConnArr[2].ToString(), DBConnArr[3].ToString(), Password);
if (!string.IsNullOrEmpty(Password))
{
Password = Global.EncryptString(Password);
DbConn = DbConn + Password;
}
QryStr = "SELECT Language_Code FROM ut_language WHERE Language_Default = 1";
DTCounts = ObjDIConnection.ExecuteDataTable(Regex.Replace(QryStr, "UT_", ObjDIConnection.DIDataSetDefault(), RegexOptions.IgnoreCase));
if (DTCounts.Rows.Count == 1) strDefaultLanguage = DTCounts.Rows[0]["Language_Code"].ToString();
else strDefaultLanguage = "en";
// Get counts from db
QryStr = "select DBMtd_AreaCnt, DBMtd_IndCnt, DBMtd_SrcCnt, DBMtd_DataCnt from ut_dbmetadata_" + strDefaultLanguage;
DTCounts = ObjDIConnection.ExecuteDataTable(Regex.Replace(QryStr, "UT_", ObjDIConnection.DIDataSetDefault(), RegexOptions.IgnoreCase));
DataRow Row = DTCounts.Rows[0];
CountStr = string.Format("{0:0,0}", Row[0]) + "_" + string.Format("{0:0,0}", Row[1]) + "_" + string.Format("{0:0,0}", Row[2]) + "_" + string.Format("{0:0,0}", Row[3]);
//get default language code in database
DbDefaultLanguage = ObjDIConnection.DILanguageCodeDefault(ObjDIConnection.DIDataSetDefault());
//get all avalilable language code in database
DbAvailableLanguage = Global.GetAllAvailableLanguageCode(ObjDIConnection);
if (DbAvailableLanguage.Length > 0)
{
DbAvailableLanguageStr = string.Join(",", DbAvailableLanguage);
}
#region -- get default indicator and their Json data --
DefIndStr = GetDefaultIndicators(ObjDIConnection, strDefaultLanguage);
DefInd = DefIndStr[0];
DefIndJSon = DefIndStr[1];
#endregion
#region --get default area of level 1 and 2 (L1 + L2) by stored procedure with their json and counts --
DefAreaStr = GetDefaultAreas(ObjDIConnection, strDefaultLanguage);
DefArea = DefAreaStr[0];
DefAreaJSon = DefAreaStr[1];
DefAreaCount = DefAreaStr[2];
#endregion
//Check connection name already exists or not
if (XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root + "/" + Constants.XmlFile.Db.Tags.Category + "/" + Constants.XmlFile.Db.Tags.Database + "[@" + Constants.XmlFile.Db.Tags.DatabaseAttributes.Name + "='" + ConnName + "']") != null && XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root + "/" + Constants.XmlFile.Db.Tags.Category + "/" + Constants.XmlFile.Db.Tags.Database + "[@" + Constants.XmlFile.Db.Tags.DatabaseAttributes.Name + "='" + ConnName + "']").Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.Id].Value != DbNId.ToString())
{
RetVal = "exists";
}
else
{
xmlNode = XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root + "/" + Constants.XmlFile.Db.Tags.Category + "/" + Constants.XmlFile.Db.Tags.Database + "[@" + Constants.XmlFile.Db.Tags.DatabaseAttributes.Id + "=" + DbNId + "]");
if (xmlNode.Attributes["langcode_csvfiles"] == null)
{
XmlAttribute attrCSV = XmlDoc.CreateAttribute("langcode_csvfiles");
attrCSV.Value = string.Empty;
xmlNode.Attributes.Append(attrCSV);
}
else
{
xmlNode.Attributes["langcode_csvfiles"].Value = string.Empty;
}
if (xmlNode.ParentNode.Attributes[Constants.XmlFile.Db.Tags.CategoryAttributes.Name].Value == CategoryName)
{
//Update for same category name
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.Name].Value = ConnName;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DatabaseConnection].Value = DbConn;
if (xmlNode.Attributes["desc" + DbDefaultLanguage] == null)
{
XmlAttribute attrDesc = XmlDoc.CreateAttribute("desc" + DbDefaultLanguage);
attrDesc.Value = Description;
xmlNode.Attributes.Append(attrDesc);
}
else
{
xmlNode.Attributes["desc" + DbDefaultLanguage].Value = Description;
}
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.Count].Value = CountStr;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.LastModified].Value = string.Format("{0:yyyy-MM-dd}", DateTime.Today.Date);
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.AvailableLanguage].Value = DbAvailableLanguageStr;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultLanguage].Value = DbDefaultLanguage.Substring(1);
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultIndicator].Value = DefInd;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultIndicatorJSON].Value = DefIndJSon;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultArea].Value = DefArea;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultAreaJSON].Value = DefAreaJSon;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultAreaCount].Value = DefAreaCount;
//Update default dbNId
if (IsDefDb == "true")
{
XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root).Attributes[Constants.XmlFile.Db.Tags.RootAttributes.Default].Value = DbNId;
}
File.SetAttributes(DBConnectionsFile, FileAttributes.Normal);
XmlDoc.Save(DBConnectionsFile);
//Update Metadata Description in the database
ObjDIConnection.ExecuteNonQuery("UPDATE " + ObjDIConnection.DIDataSetDefault() + "dbmetadata" + ObjDIConnection.DILanguageCodeDefault(ObjDIConnection.DIDataSetDefault()).ToString() + " SET DBMtd_Desc = '" + Description.Replace("'", "''") + "'");
RetVal = "true";
}
else if (xmlNode.ParentNode.Attributes[Constants.XmlFile.Db.Tags.CategoryAttributes.Name].Value != CategoryName)
{
//Update when category name has changed for same id
//Update values in node
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.Name].Value = ConnName;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DatabaseConnection].Value = DbConn;
xmlNode.Attributes["desc_en"].Value = Description;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.Count].Value = CountStr;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.LastModified].Value = string.Format("{0:yyyy-MM-dd}", DateTime.Today.Date);
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.AvailableLanguage].Value = DbAvailableLanguageStr;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultLanguage].Value = DbDefaultLanguage.Substring(1);
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultIndicator].Value = DefInd;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultIndicatorJSON].Value = DefIndJSon;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultArea].Value = DefArea;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultAreaJSON].Value = DefAreaJSon;
xmlNode.Attributes[Constants.XmlFile.Db.Tags.DatabaseAttributes.DefaultAreaCount].Value = DefAreaCount;
//Update default dbNId
if (IsDefDb == "true")
{
XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root).Attributes[Constants.XmlFile.Db.Tags.RootAttributes.Default].Value = DbNId;
}
//Copy updated node values in new node
NewXmlNode = xmlNode;
//Remove current node
xmlNode.ParentNode.RemoveChild(xmlNode);
//Read the category node
CategoryNode = XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root + "/" + Constants.XmlFile.Db.Tags.Category + "[@" + Constants.XmlFile.Db.Tags.CategoryAttributes.Name + "='" + CategoryName + "']");
//Create a category node if not exists
if (CategoryNode == null)
{
NewCategoryElement = XmlDoc.CreateElement(Constants.XmlFile.Db.Tags.Category);
NewCategoryElement.SetAttribute(Constants.XmlFile.Db.Tags.CategoryAttributes.Name, CategoryName);
CategoryNode = XmlDoc.SelectSingleNode("/" + Constants.XmlFile.Db.Tags.Root).AppendChild(NewCategoryElement);
}
//Append the new node in category
CategoryNode.AppendChild(NewXmlNode);
File.SetAttributes(DBConnectionsFile, FileAttributes.Normal);
XmlDoc.Save(DBConnectionsFile);
RetVal = "true";
//Update Metadata Description in the database
ObjDIConnection.ExecuteNonQuery("UPDATE " + ObjDIConnection.DIDataSetDefault() + "dbmetadata" + ObjDIConnection.DILanguageCodeDefault(ObjDIConnection.DIDataSetDefault()).ToString() + " SET DBMtd_Desc = '" + Description.Replace("'", "''") + "'");
}
if (Global.standalone_registry != "true")
{
this.RunDBScripts(ObjDIConnection, DbNId, DbDefaultLanguage.Substring(1));
// Generate language file because RunDBScripts method deletes existing language file, so user will not be able to execute furter functionality.
GenerateAllPagesXML();
VersionBuilder = new DBVersionBuilder(ObjDIConnection, new DIQueries(ObjDIConnection.DIDataSetDefault(), ObjDIConnection.DILanguageCodeDefault(ObjDIConnection.DIDataSetDefault())));
VersionBuilder.InsertVersionInfo(Constants.DBVersion.DI7_0_0_0, Constants.DBVersion.VersionsChangedDatesDI7_0_0_0, Constants.DBVersion.VersionCommentsDI7_0_0_0);
}
// Set description filed to update in service database
this.DataBaseDescription = Description.Replace("'", "''").Replace("\n", "<br />").Replace(@"""", @"\""");
GetAndUpdateIndexedAreas(DbAvailableLanguage, ObjDIConnection);
GetAndUpdateIndexedIndicators(DbAvailableLanguage, ObjDIConnection);
UpdateAdaptations(ObjDIConnection);
DeleteSitemapFiles();
}
#region "Call method to write log in XLS file"
if (RetVal == "true")
{
DatabaseParams = DbConn.Split(new string[] { "||" }, StringSplitOptions.None);
if (!string.IsNullOrEmpty(DatabaseParams[1].ToString()))
{
Server_HostName = DatabaseParams[1].ToString();
}
if (!string.IsNullOrEmpty(DatabaseParams[2].ToString()))
{
DatabaseName = DatabaseParams[2].ToString();
}
if (!string.IsNullOrEmpty(DatabaseParams[3].ToString()))
{
DataBaseUserName = DatabaseParams[3].ToString();
}
//"Connection Name:{0}, Server/Host Name:{1}, Database Name:{2}, User Name:{3}, Description:{4}";
XLSFileMsg = string.Format(Constants.CSVLogMessage.UpdateConnection, ConnName, Server_HostName, DatabaseName, DataBaseUserName, Description);
WriteLogInXLSFile(Constants.AdminModules.DatabaseSettings.ToString(), XLSFileMsg);
}
#endregion
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
}
return RetVal;
}
public static string GetMapServerLangCode(string UILanguageCode)
{
string RetVal = string.Empty;
string MapServerDsURL = DIMapServer.WebServiceInstance.GetMapServerURL();
DIConnection MapServerDBConnection = new DIConnection(Global.GetMapServerConnectionDetails());
if (MapServerDBConnection.ExecuteDataTable(DIQueries.CheckLanguageExists(MapServerDBConnection.DIDataSetDefault(), UILanguageCode)).Rows[0][0].ToString().Trim() != "0")
{
RetVal = UILanguageCode;
}
else
{
DataTable table = MapServerDBConnection.ExecuteDataTable(DIQueries.GetDefaultLangauge(MapServerDBConnection.DIDataSetDefault()));
if (table.Rows.Count != 0)
{
string mapServerDefaultLangCode = table.Rows[0][2].ToString();
RetVal = mapServerDefaultLangCode;
}
}
return RetVal;
}
public string GetDataDescription(string requestParam)
{
string RetVal = string.Empty;
DataTable DTMetadata = null;
string[] DBDetails = requestParam.Split(new string[] { "[~]" }, StringSplitOptions.None);
DIConnection ObjDIConnection = null;
ObjDIConnection = new DIConnection(((DIServerType)Convert.ToInt32(0)), DBDetails[0].ToString(), string.Empty, DBDetails[1].ToString(), DBDetails[2].ToString(), DBDetails[3].ToString());
DTMetadata = ObjDIConnection.ExecuteDataTable("SELECT DBMtd_Desc FROM " + ObjDIConnection.DIDataSetDefault() + "dbmetadata" + ObjDIConnection.DILanguageCodeDefault(ObjDIConnection.DIDataSetDefault()).ToString());
if (DTMetadata.Rows.Count > 0)
{
RetVal = DTMetadata.Rows[0]["DBMtd_Desc"].ToString();
}
return RetVal;
}
internal bool ExportMDB(string destinationDBNameWPath, string tempFolderPath, bool createNewDatabase)
{
bool RetVal = false;
int ProgressCount = 0;
try
{
if (createNewDatabase)
{
this.ExportMDB(destinationDBNameWPath, tempFolderPath);
}
else
{
DIExport.RaiseExportProgressBarInitialize(100);
//- Establish Connection with Destination Database in temp folder
this.DestDBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, destinationDBNameWPath, string.Empty, string.Empty);
this.DestDBQueries = new DIQueries(DestDBConnection.DIDataSetDefault(), DestDBConnection.DILanguageCodeDefault(DestDBConnection.DIDataSetDefault()));
this.DBTableNames = new DITables(DestDBConnection.DIDataSetDefault(), DestDBConnection.DILanguageCodeDefault(DestDBConnection.DIDataSetDefault()));
this.SourceTableNames = this.SourceDBQueries.TablesName;
// if database/tempalte already in DI6 format then convert it into latest format
DBConverterDecorator DBConverter = new DBConverterDecorator(this.DestDBConnection, this.DestDBQueries);
DBConverter.DoConversion(false);
//-- Get LanguageTable
this.LanguageTable = this.DestDBConnection.DILanguages(this.DestDBConnection.DIDataSetDefault());
DIExport.RaiseExportProgressBarIncrement(1);
//- Export Indicator, Unit, Subgroupval, IUS
this.ProcessIndicators();
DIExport.RaiseExportProgressBarIncrement(10);
// Export linked IC information
this.ProcessIndicatorClassifications();
DIExport.RaiseExportProgressBarIncrement(20);
//- Export Area
this.ProcessAreas();
DIExport.RaiseExportProgressBarIncrement(30);
//- Export TimePeriod
this.ProcessTimePeriods();
DIExport.RaiseExportProgressBarIncrement(40);
//- Export Data
this.ExportData();
DIExport.RaiseExportProgressBarIncrement(80);
this.ProcessOtherTables();
DIExport.RaiseExportProgressBarIncrement(90);
DIExport.RaiseExportProgressBarIncrement(ProgressCount++);
this.DeleteTable(TEMP_TABLE_Data, this.DestDBConnection);
DIExport.RaiseExportProgressBarIncrement(100);
}
}
catch
{
throw;
}
return RetVal;
}
private bool UpdateAdaptations(DIConnection objDIConnection)
{
bool RetVal = false;
string[] YearsArr;
string AdaptationName = string.Empty;
string Description = string.Empty;
string Version = "-1";
bool IsDesktop = false;
bool IsWeb = true;
string WebURL = string.Empty;
string AreaCount = string.Empty;
string IUSCount = string.Empty;
string TimePeriodsCount = string.Empty;
string DataValuesCount = string.Empty;
string StartYear = string.Empty;
string EndYear = string.Empty;
string LastModifiedOn = string.Empty;
string AreaNId = string.Empty;
string SubNation = string.Empty;
string CatalogImage = string.Empty;
string DbAvailableLanguageStr = string.Empty;
string[] DbAvailableLanguage = null;
string LangCode_CSVFiles = string.Empty;
string QryStr = string.Empty;
string strDefaultLanguage = string.Empty;
DataTable DTCounts;
string ParamStr = string.Empty;
try
{
AdaptationName = Global.adaptation_name;
WebURL = Global.GetAdaptationUrl();
//DBMtd_AreaCnt, DBMtd_IndCnt, DBMtd_SrcCnt, DBMtd_DataCnt
QryStr = "SELECT Language_Code FROM ut_language WHERE Language_Default = 1";
DTCounts = objDIConnection.ExecuteDataTable(Regex.Replace(QryStr, "UT_", objDIConnection.DIDataSetDefault(), RegexOptions.IgnoreCase));
if (DTCounts.Rows.Count == 1) strDefaultLanguage = DTCounts.Rows[0]["Language_Code"].ToString();
else strDefaultLanguage = "en";
// Get counts from db
QryStr = "select DBMtd_AreaCnt, DBMtd_IndCnt, DBMtd_SrcCnt, DBMtd_DataCnt from ut_dbmetadata_" + strDefaultLanguage;
DTCounts = objDIConnection.ExecuteDataTable(Regex.Replace(QryStr, "UT_", objDIConnection.DIDataSetDefault(), RegexOptions.IgnoreCase));
DataRow dataCountsRow = DTCounts.Rows[0];
AreaCount = dataCountsRow[0].ToString();
IUSCount = dataCountsRow[1].ToString();
TimePeriodsCount = dataCountsRow[2].ToString();
DataValuesCount = dataCountsRow[3].ToString();
YearsArr = Global.GetStartEndYear(objDIConnection);
StartYear = YearsArr[0];
EndYear = YearsArr[1];
LastModifiedOn = string.Format("{0:dd-MM-yyyy}", DateTime.Today.Date);
AreaNId = Global.area_nid;
SubNation = Global.sub_nation;
Description = this.DataBaseDescription;
CatalogImage = WebURL + "/stock/themes/default/images/cust/logo.png";
DbAvailableLanguage = Global.GetAllAvailableLanguageCode(objDIConnection);
if (DbAvailableLanguage.Length > 0)
{
DbAvailableLanguageStr = string.Join(",", DbAvailableLanguage);
}
ParamStr = AdaptationName;
ParamStr += Constants.Delimiters.ParamDelimiter + Description;
ParamStr += Constants.Delimiters.ParamDelimiter + Version;
ParamStr += Constants.Delimiters.ParamDelimiter + IsDesktop;
ParamStr += Constants.Delimiters.ParamDelimiter + IsWeb;
ParamStr += Constants.Delimiters.ParamDelimiter + WebURL;
ParamStr += Constants.Delimiters.ParamDelimiter + AreaCount;
ParamStr += Constants.Delimiters.ParamDelimiter + IUSCount;
ParamStr += Constants.Delimiters.ParamDelimiter + TimePeriodsCount;
ParamStr += Constants.Delimiters.ParamDelimiter + DataValuesCount;
ParamStr += Constants.Delimiters.ParamDelimiter + StartYear;
ParamStr += Constants.Delimiters.ParamDelimiter + EndYear;
ParamStr += Constants.Delimiters.ParamDelimiter + LastModifiedOn;
ParamStr += Constants.Delimiters.ParamDelimiter + AreaNId;
ParamStr += Constants.Delimiters.ParamDelimiter + SubNation;
ParamStr += Constants.Delimiters.ParamDelimiter + CatalogImage;
ParamStr += Constants.Delimiters.ParamDelimiter + Global.DbAdmName;
ParamStr += Constants.Delimiters.ParamDelimiter + Global.DbAdmInstitution;
ParamStr += Constants.Delimiters.ParamDelimiter + Global.DbAdmEmail;
ParamStr += Constants.Delimiters.ParamDelimiter + Global.UnicefRegion;
ParamStr += Constants.Delimiters.ParamDelimiter + Global.AdaptationYear;
ParamStr += Constants.Delimiters.ParamDelimiter + DbAvailableLanguageStr;
ParamStr += Constants.Delimiters.ParamDelimiter + LangCode_CSVFiles;
if (AdminSaveAdaptation(ParamStr) == "true")
{
RetVal = true;
}
}
catch (Exception ex)
{
RetVal = false;
Global.CreateExceptionString(ex, null);
}
return RetVal;
}
