/// <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;
}
/// <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;
}
}
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;
}
/// <summary>
/// Generate SDMX Files from DES
/// </summary>
/// <param name="DESFileNameWithPath"></param>
/// <param name="DSDFileNameWPath"></param>
/// <param name="outputFolder"></param>
/// <returns></returns>
public static bool Generate_SDMXML_Data(string DESFileNameWithPath, string DSDFileNameWPath, string outputFolder)
{
bool RetVal = false;
DIConnection DBConnection = null;
DIQueries DBQueries = null;
int count = 0;
// Utility.TemporaryFileNamePath
DataTable Table = DevInfo.Lib.DI_LibBAL.Import.DAImport.DES.DataEntrySpreadsheets.GetDataTableForAllDESSheets(DESFileNameWithPath);
string TempDatabaseName = string.Empty;
string TempDatabaseNameCompact = string.Empty;
TempDatabaseName = Path.Combine(Path.GetDirectoryName(DESFileNameWithPath), DICommon.GetValidFileName(DateTime.Now.Ticks.ToString() + DICommon.FileExtension.Template));
Dictionary<string, int> IndicatorList = new Dictionary<string, int>();
Dictionary<string, int> UnitList = new Dictionary<string, int>();
Dictionary<string, int> AreaList = new Dictionary<string, int>();
Dictionary<string, int> SgValList = new Dictionary<string, int>();
Dictionary<string, int> TimepeirodList = new Dictionary<string, int>();
Dictionary<string, int> SourceList = new Dictionary<string, int>();
try
{
DevInfo.Lib.DI_LibDAL.Resources.Resource.GetBlankDevInfoDBFile(TempDatabaseName);
DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, TempDatabaseName, string.Empty, string.Empty);
DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault()));
DIDataValueHelper.MergeTextualandNumericDataValueColumn(DBConnection, DBQueries);
IndicatorBuilder IndBuilder = new IndicatorBuilder(DBConnection, DBQueries);
UnitBuilder UBuilder = new UnitBuilder(DBConnection, DBQueries);
DI6SubgroupValBuilder SGBuilder = new DI6SubgroupValBuilder(DBConnection, DBQueries);
AreaBuilder AreaBuilderObj = new AreaBuilder(DBConnection, DBQueries);
IndicatorClassificationBuilder ICBuilder = new IndicatorClassificationBuilder(DBConnection, DBQueries);
TimeperiodBuilder TBuilder = new TimeperiodBuilder(DBConnection, DBQueries);
SourceBuilder SrcBuilder = new SourceBuilder(DBConnection, DBQueries);
DIDatabase DatabaseObj = new DIDatabase(DBConnection, DBQueries);
IUSBuilder IUSBuilderObj = new IUSBuilder(DBConnection, DBQueries);
foreach (DataRow Row in Table.Rows)
{
int IndicatorNId = 0;
int UnitNId = 0;
int SGValNId = 0;
int AreaNId = 0;
int TimeperiodNId = 0;
int SourceNid = 0;
int IUSNID = 0;
//-- Import Indicator
if (IndicatorList.ContainsKey(Convert.ToString(Row[Indicator.IndicatorGId])))
{
IndicatorNId = IndicatorList[Convert.ToString(Row[Indicator.IndicatorGId])];
}
else
{
IndicatorNId = IndBuilder.ImportIndicator(Convert.ToString(Row[Indicator.IndicatorName]), Convert.ToString(Row[Indicator.IndicatorGId]), false);
IndicatorList.Add(Convert.ToString(Row[Indicator.IndicatorGId]), IndicatorNId);
}
//-- Import Unit
if (UnitList.ContainsKey(Convert.ToString(Row[Unit.UnitGId])))
{
UnitNId = UnitList[Convert.ToString(Row[Unit.UnitGId])];
}
else
{
UnitNId = UBuilder.ImportUnit(Convert.ToString(Row[Unit.UnitGId]), Convert.ToString(Row[Unit.UnitName]).Trim(), false);
UnitList.Add(Convert.ToString(Row[Unit.UnitGId]), UnitNId);
}
//-- Import SubgroupVal
if (!string.IsNullOrEmpty(Convert.ToString(Row[SubgroupVals.SubgroupVal])))
{
if (SgValList.ContainsKey(Convert.ToString(Row[SubgroupVals.SubgroupValGId])))
{
SGValNId = SgValList[Convert.ToString(Row[SubgroupVals.SubgroupValGId])];
}
else
{
SGValNId = SGBuilder.ImportSubgroupVal(Convert.ToString(Row[SubgroupVals.SubgroupVal]), Convert.ToString(Row[SubgroupVals.SubgroupValGId]), false);
SgValList.Add(Convert.ToString(Row[SubgroupVals.SubgroupValGId]), SGValNId);
}
}
//-- Import Area
if (AreaList.ContainsKey(Convert.ToString(Row[Area.AreaID])))
{
AreaNId = AreaList[Convert.ToString(Row[Area.AreaID])];
}
else
{
AreaNId = AreaBuilderObj.ImportArea(Convert.ToString(Row[Area.AreaName]), Convert.ToString(Row[Area.AreaID]), String.Empty, false);
AreaList.Add(Convert.ToString(Row[Area.AreaID]), AreaNId);
}
//-- Import Timeperiod
if (TimepeirodList.ContainsKey(Convert.ToString(Row[Timeperiods.TimePeriod])))
{
TimeperiodNId = TimepeirodList[Convert.ToString(Row[Timeperiods.TimePeriod])];
}
else
{
TimeperiodNId = TBuilder.CheckNCreateTimeperiod(Convert.ToString(Row[Timeperiods.TimePeriod]));
TimepeirodList.Add(Convert.ToString(Row[Timeperiods.TimePeriod]), TimeperiodNId);
}
//-- Import Source
if (SourceList.ContainsKey(Convert.ToString(Row[IndicatorClassifications.ICName])))
{
SourceNid = SourceList[Convert.ToString(Row[IndicatorClassifications.ICName])];
}
else
{
SourceNid = SrcBuilder.CheckNCreateSource(Convert.ToString(Row[IndicatorClassifications.ICName]));
SourceList.Add(Convert.ToString(Row[IndicatorClassifications.ICName]), SourceNid);
}
IUSNID = IUSBuilderObj.ImportIUS(IndicatorNId, UnitNId, SGValNId, 0, 0, DBQueries, DBConnection);
if (IUSNID > 0 && AreaNId > 0 && TimeperiodNId > 0 && !string.IsNullOrEmpty(Convert.ToString(Row[Data.DataValue])))
{
DatabaseObj.CheckNCreateData(AreaNId, IUSNID, SourceNid, TimeperiodNId, Convert.ToString(Row[Data.DataValue]));
}
}
DatabaseObj.UpdateIndicatorUnitSubgroupNIDsInData();
//-- Compact Database into TempFile
TempDatabaseNameCompact = Path.Combine(Path.GetDirectoryName(DESFileNameWithPath), DICommon.GetValidFileName(DateTime.Now.Ticks.ToString() + DICommon.FileExtension.Template));
DIDatabase.CompactDataBase(ref DBConnection, DBQueries, TempDatabaseNameCompact, true);
DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, TempDatabaseNameCompact, string.Empty, string.Empty);
DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault()));
if (!Directory.Exists(Path.Combine(outputFolder, DBQueries.LanguageCode.Trim('_'))))
{
Directory.CreateDirectory(Path.Combine(outputFolder, DBQueries.LanguageCode.Trim('_')));
}
System.Xml.XmlDocument XmlDoc = SDMXUtility.Get_Query(SDMXSchemaType.Two_One, null, QueryFormats.StructureSpecificTS, DataReturnDetailTypes.Full, Guid.NewGuid().ToString().Replace("-", "").Replace("_", ""), DBConnection, DBQueries);
RetVal = SDMXUtility.Generate_Data(SDMXSchemaType.Two_One, XmlDoc, DevInfo.Lib.DI_LibSDMX.DataFormats.StructureSpecificTS, DBConnection, DBQueries, outputFolder);
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
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;
}
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>
/// 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;
}
/// 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;
}
/// <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;
}
/// <summary>
/// Returns the DIQueries object
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="dataPrefix"></param>
/// <returns></returns>
public static DIQueries GetDBQueries(DIConnection dbConnection, string dataPrefix)
{
DIQueries RetVal = null;
string LanguageCode = string.Empty;
//-- Set Default dataprefix if not passed
if (string.IsNullOrEmpty(dataPrefix))
dataPrefix = dbConnection.DIDataSetDefault();
if (dbConnection.IsValidDILanguage(dataPrefix, DataAdmin.DAAppliationLangCode))
{
LanguageCode = "_" + DataAdmin.DAAppliationLangCode;
}
else
{
LanguageCode = dbConnection.DILanguageCodeDefault(dataPrefix);
}
RetVal = new DIQueries(dataPrefix, LanguageCode);
return RetVal;
}
private void ConnectToDatabaseNCreateConverter(string fileNameWPath)
{
DIConnection DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, fileNameWPath, string.Empty, string.Empty);
DIQueries DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault()));
this.CreateDBConverter(DBConnection, DBQueries);
}
public static bool CompactNUpdateOnlineDataBase(ref DIConnection sourceDBConnection, DIQueries dbQueries)
{
bool RetVal = false;
DIConnectionDetails SourceDBConnectionDetails;
DBMetadataTableBuilder DBMetadataTable;
DIDatabase DBDatabase;
string DataPrefix = string.Empty;
try
{
if (sourceDBConnection != null)
{
// update counts in DBMetadata table( only if database/template is in DI6 format)
if (dbQueries == null)
{
DataPrefix = sourceDBConnection.DIDataSetDefault();
dbQueries = new DIQueries(DataPrefix, sourceDBConnection.DILanguageCodeDefault(DataPrefix));
}
DBMetadataTable = new DBMetadataTableBuilder(sourceDBConnection, dbQueries);
if (DBMetadataTable.IsDBMetadataTableExists())
{
DBMetadataTable.GetNUpdateCounts();
}
if (DIDatabase.SeperateDataValueColumn)
{
// Check orgTextual_Data_value exists or not. If column exists then move textual & numeric values into their respective column.
DIDataValueHelper.SeparateTextualandNemericData(sourceDBConnection, dbQueries);
}
// update indicator unit and subgroup nids in Data table
DBDatabase = new DIDatabase(sourceDBConnection, dbQueries);
DBDatabase.UpdateIndicatorUnitSubgroupNIDsInData();
// remove FootnoteNId inconsistency. (replace 0 or null FootnoteNId by -1 in UT_data table).
DBDatabase.RemoveFootnoteNIdsInconsistencyInData();
RetVal = true;
}
}
catch (Exception ex)
{
RetVal = false;
ExceptionFacade.ThrowException(ex);
}
return RetVal;
}
/// <summary>
/// Compact the database mainly Access database(.mdb) and save as specified destination file.
/// </summary>
///<param name="sourceDBConnection"> Source database connection</param>
/// <param name="destFilePath">destination file name with path.</param>
/// <param name="disposeConnection"> Set true when calling this methods for finish process otherwise false.For only saving the database, pass False and for finish , pass true. </param>
/// <returns>true, if success.</returns>
/// <remarks>Before calling this function,dont dispose source database connection </remarks>
public static bool CompactDataBase(ref DIConnection sourceDBConnection, DIQueries dbQueries, string destFilePath, bool disposeConnection)
{
bool RetVal = false;
bool IsFileOpen = false;
DIConnectionDetails SourceDBConnectionDetails;
DBMetadataTableBuilder DBMetadataTable;
DIDatabase DBDatabase;
string SourceDBNameWPath;
JRO.JetEngine je;
string DataPrefix = string.Empty;
DI7MetadataCategoryBuilder DI7MetadataCategory;
// -- NOTE: USE sDestFile only when sDestFile <> sSourceDB
try
{
if (sourceDBConnection != null && !string.IsNullOrEmpty(destFilePath))
{
//Drop index for ut_data table for IUSNID, areanid column
DIDatabase.DropDefaultIndex(sourceDBConnection);
// update counts in DBMetadata table( only if database/template is in DI6 format)
if (dbQueries == null)
{
DataPrefix = sourceDBConnection.DIDataSetDefault();
dbQueries = new DIQueries(DataPrefix, sourceDBConnection.DILanguageCodeDefault(DataPrefix));
}
DBMetadataTable = new DBMetadataTableBuilder(sourceDBConnection, dbQueries);
if (DBMetadataTable.IsDBMetadataTableExists())
{
DBMetadataTable.GetNUpdateCounts();
}
//Updating XSLT from Resourse File into database.
DI7MetadataCategory = new DI7MetadataCategoryBuilder(sourceDBConnection, dbQueries);
DI7MetadataCategory.UpdateXSLT(dbQueries.DataPrefix);
// update database name in DB_Available table
sourceDBConnection.InsertNewDBFileName(dbQueries.DataPrefix, DICommon.RemoveQuotes(System.IO.Path.GetFileName(destFilePath)));
if (DIDatabase.SeperateDataValueColumn)
{
// Check orgTextual_Data_value exists or not. If column exists then move textual & numeric values into their respective column.
DIDataValueHelper.SeparateTextualandNemericData(sourceDBConnection, dbQueries);
}
// update indicator unit and subgroup nids in Data table
DBDatabase = new DIDatabase(sourceDBConnection, dbQueries);
DBDatabase.UpdateIndicatorUnitSubgroupNIDsInData();
// remove FootnoteNId inconsistency. (replace 0 or null FootnoteNId by -1 in UT_data table).
DBDatabase.RemoveFootnoteNIdsInconsistencyInData();
// Update auto calculated columns ( IC table - Publisher, Year & title ,Indicator table- Data_Exists, area table - data_exist, IUS table - subgroup_nids & data_exist) into the database/template
DBDatabase.UpdateAutoCalculatedFieldsInTables();
// Update auto calculated column of DI7
DBDatabase.UpdateAutoCalculatedDI7FieldsInTables();
//Update those subgroupVals which is not associated with any subgroup type.Then insert association with others for those subgroup
DBDatabase.UpdateSubgroupValsInOthersSGDimensionInTables();
//Create index for ut_data table for IUSNID, areanid column
DIDatabase.CreateDefaultIndex(sourceDBConnection);
// dispose source database connection
SourceDBConnectionDetails = sourceDBConnection.ConnectionStringParameters;
SourceDBNameWPath = SourceDBConnectionDetails.DbName;
sourceDBConnection.Dispose();
System.Threading.Thread.Sleep(10);
try
{
if (File.Exists(destFilePath))
{
File.SetAttributes(destFilePath, FileAttributes.Normal);
File.Delete(destFilePath);
}
}
catch { }
//-- Copy SourceFile to temp file so that any existing connection on database shall not stop compact database process.
//string TempFile = DICommon.GetValidFileName(DateTime.Now.ToString()) + Path.GetExtension(SourceDBNameWPath);
//File.Copy(SourceDBNameWPath, TempFile, true);
try
{
// compacting the database
je = new JRO.JetEngine();
je.CompactDatabase("Data Source=\"" + SourceDBNameWPath + "\";Jet OLEDB:Database Password="******"Data Source=\"" + destFilePath + "\";Jet OLEDB:Database Password="******"Database already exists"))
IsFileOpen = true;
}
// reconnect to source database
if (!disposeConnection)
{
sourceDBConnection = new DIConnection(SourceDBConnectionDetails);
}
if (IsFileOpen == false)
RetVal = true;
}
}
catch (Exception ex)
{
RetVal = false;
ExceptionFacade.ThrowException(ex);
}
return RetVal;
}
/// <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 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>
/// DevInfo_5_0 Area Spreadsheet (should have 5 columns only staring the value from 6th Row - AreaID, AreaName, AreaLevel, AreaGID, ParentGID)
/// </summary>
/// <param name="templateFileName"></param>
/// <param name="xlsFilenames"></param>
/// <param name="tempFolderPath"></param>
/// <param name="trgDBQueries">Instance of target queries object</param>
/// <param name="languageName">languageName</param>
/// <returns></returns>
public bool CreateTemplateFrmAreaSpreadsheet(string templateFileName, List<string> xlsFilenames, string tempFolderPath, DIQueries trgDBQueries, string languageName)
{
bool RetVal = false;
DIConnection DBConnection = null;
DIQueries DBQueries;
AreaBuilder AreaBuilderObj;
DIDatabase TempTemplateFile;
string DataPrefix = string.Empty;
string LanguageCode = string.Empty;
LanguageBuilder DILanguageBuilder;
try
{
//create temp template file
TempTemplateFile = new DIDatabase(templateFileName);
TempTemplateFile.Dispose();
//create DIConnection, queries and area objects
DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, templateFileName, string.Empty, string.Empty);
if (trgDBQueries != null)
{
DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), trgDBQueries.LanguageCode);
DataPrefix = DBQueries.DataPrefix;
LanguageCode = trgDBQueries.LanguageCode.Replace("_", "");
// create langauge dependent tables if not exists in the new template
this.UpdateTablesForTargetLanguage(languageName, DBConnection, DBQueries, DataPrefix, LanguageCode);
}
else
{
DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault()));
}
AreaBuilderObj = new AreaBuilder(DBConnection, DBQueries);
//get areas from excel files
foreach (string XlsFileName in xlsFilenames)
{
try
{
// insert area into template
DIExcel AreaXlsFile = new DIExcel(XlsFileName);
AreaInfo NewArea;
DataTable TempTable = AreaXlsFile.GetDataTableFromSheet(AreaXlsFile.GetSheetName(0));
//check it is a valid area excel file or not
if (TempTable.Rows.Count > 5 & TempTable.Columns.Count > 1)
{
if (TempTable.Rows[2][0].ToString() == DITemplate.AreaIdString
& TempTable.Rows[2][1].ToString() == DITemplate.AreaNameString
& TempTable.Rows[2][2].ToString() == DITemplate.AreaLevelString
& TempTable.Rows[2][3].ToString() == DITemplate.AreaGIdString
& TempTable.Rows[2][4].ToString() == DITemplate.AreaParentGIdString)
{
// delete empty rows
for (int i = 0; i < 4; i++)
{
TempTable.Rows[0].Delete();
}
TempTable.AcceptChanges();
// sort the table by level
TempTable.DefaultView.Sort = TempTable.Columns[this.AreaSheetColumns[AreaSheetColumnType.AreaLevel].ColumnIndex].ColumnName;
foreach (DataRowView Row in TempTable.DefaultView)
{
try
{
// get area info from temp table
NewArea = new AreaInfo();
NewArea.ID = DICommon.RemoveQuotes(Row[this.AreaSheetColumns[AreaSheetColumnType.AreaID].ColumnIndex].ToString());
NewArea.Name = DICommon.RemoveQuotes(Row[this.AreaSheetColumns[AreaSheetColumnType.AreaName].ColumnIndex].ToString());
NewArea.Level = Convert.ToInt32(Row[this.AreaSheetColumns[AreaSheetColumnType.AreaLevel].ColumnIndex]);
NewArea.GID = DICommon.RemoveQuotes(Row[this.AreaSheetColumns[AreaSheetColumnType.AreaGID].ColumnIndex].ToString());
NewArea.Parent = new AreaInfo();
NewArea.Parent.ID = DICommon.RemoveQuotes(Row[this.AreaSheetColumns[AreaSheetColumnType.PareaGID].ColumnIndex].ToString());
//insert area into template
// Step 1: If Area_Parent_ID is blank then set Area_Parent_NID to -1 and Area_Level=1
if (string.IsNullOrEmpty(NewArea.Parent.ID))
{
NewArea.Parent.Nid = -1;
NewArea.Level = 1;
}
else
{
// Step 2: If Area_Parent_ID is not blank then get Area_Parent_NID
NewArea.Parent.Nid = AreaBuilderObj.GetAreaNidByAreaID(NewArea.Parent.ID);
if (NewArea.Parent.Nid <= 0)
{ // Step 2.1: If Area_Parent_NID <=0 then set Area_Parent_NID to -1 and Area_Level=1
NewArea.Parent.Nid = -1;
NewArea.Level = 1;
}
else
{ // Step 2.1: If Area_Parent_NID >0 then Area_Level=Area_Parent_Level+1
//get parent area level
NewArea.Parent.Level = AreaBuilderObj.GetAreaLevelByAreaID(NewArea.Parent.ID);
NewArea.Level = NewArea.Parent.Level + 1;
}
}
// insert area into template
AreaBuilderObj.InsertIntoDatabase(NewArea.Name, NewArea.ID, NewArea.GID, NewArea.Level, NewArea.Parent.Nid);
}
catch (Exception ex)
{
// do nothing
}
}
}
}
}
catch (Exception)
{
//
}
}
RetVal = true;
}
catch (Exception ex)
{
RetVal = false;
throw new ApplicationException(ex.ToString());
}
finally
{
if (DBConnection != null)
{
DBConnection.Dispose();
}
}
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>
/// DevInfo_5_0 Indicator Spreadsheet (should have 2 columns only staring the value from 6th Row - Indicator,Indicator_GId)
/// </summary>
/// <param name="templateFileName"></param>
/// <param name="xlsFilenames"></param>
/// <param name="tempFolderPath"></param>
/// <returns></returns>
public bool CreateTemplateFrmIndicatorSpreadsheet(string templateFileName, List<string> xlsFilenames, string tempFolderPath, DIQueries trgQueries, string languageName)
{
bool RetVal = false;
DIConnection DBConnection = null;
DIQueries DBQueries;
IndicatorBuilder IndicatorBuilderObj;
DIDatabase TempTemplateFile;
string DataPrefix = string.Empty;
string LanguageCode = string.Empty;
try
{
//create temp template file
TempTemplateFile = new DIDatabase(templateFileName);
TempTemplateFile.Dispose();
//create DIConnection, queries and indicator objects
DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, templateFileName, string.Empty, string.Empty);
if (trgQueries == null)
{
DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault()));
}
else
{
DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), trgQueries.LanguageCode);
DataPrefix = DBQueries.DataPrefix;
LanguageCode = trgQueries.LanguageCode.Replace("_", "");
this.UpdateTablesForTargetLanguage(languageName, DBConnection, DBQueries, DataPrefix, LanguageCode);
}
IndicatorBuilderObj = new IndicatorBuilder(DBConnection, DBQueries);
//get indicators from excel files
foreach (string XlsFileName in xlsFilenames)
{
try
{
// insert indicators into template
DIExcel IndicatorXlsFile = new DIExcel(XlsFileName);
IndicatorInfo NewIndicator;
DataTable TempTable = IndicatorXlsFile.GetDataTableFromSheet(IndicatorXlsFile.GetSheetName(0));
//check it is a valid indicator excel file or not
if (TempTable.Rows.Count > 5 & TempTable.Columns.Count > 1)
{
if (TempTable.Rows[2][0].ToString() == DITemplate.IndicatorString)
{
//starting index should be 5
for (int i = 5; i < TempTable.Rows.Count; i++)
{
NewIndicator = new IndicatorInfo();
//indicator Name
NewIndicator.Name = DICommon.RemoveQuotes(TempTable.Rows[i][0].ToString());
//indicator GId
NewIndicator.GID = DICommon.RemoveQuotes(TempTable.Rows[i][1].ToString());
//insert indicator into template
IndicatorBuilderObj.CheckNCreateIndicator(NewIndicator);
}
}
}
}
catch (Exception)
{
//
}
}
RetVal = true;
}
catch (Exception ex)
{
RetVal = false;
throw new ApplicationException(ex.ToString());
}
finally
{
if (DBConnection != null)
{
DBConnection.Dispose();
}
}
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;
}
/// <summary>
/// Initializes a new instance of the Freetext class with the specified database connection details.
/// </summary>
/// <param name="serverType"></param>
/// <param name="serverName"></param>
/// <param name="portNo"></param>
/// <param name="databaseName"></param>
/// <param name="userName"></param>
/// <param name="password"></param>
/// <param name="languageFilePath"></param>
/// <remarks>
/// Use this constructor when hosting application is itself not using an instance of DAL
/// Hosting application needs to additionally set DatabasePrefix and LanguageCode properties
/// </remarks>
public FreeText(DIServerType serverType, string serverName, string portNo, string databaseName, string userName, string password)
{
try
{
DIConnection = new DIConnection(serverType, serverName, portNo, databaseName, userName, password);
}
catch (Exception ex)
{
throw ex;
}
// Get Default DataSet Prefix for database.
this._DatabasePrefix = DIConnection.DIDataSetDefault();
// Get Default Language Code for Default DataSet.
this._LanguageCode = DIConnection.DILanguageCodeDefault(_DatabasePrefix);
DIQueries = new DIQueries(_DatabasePrefix, _LanguageCode);
//add Advance search fields (Indicator,Unit,Subgroup,AreaName,AreaId and Timeperiod)to collection (_AdvanceSearchFields)
SetAdvanceSearchFields();
}
/// <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>
/// Get all languge codes exists in database
/// </summary>
/// <param name="dbConnection"></param>
/// <returns></returns>
public static List<string> GetAllDbLangCodes(DIConnection dbConnection)
{
List<string> RetVal = new List<string>();
string StrQry = string.Empty;
DataTable DtAllLanguages = null;
DITables TableNames;
string DataPrefix = string.Empty;
try
{
//-- Get default data prefix in database
DataPrefix = dbConnection.DIDataSetDefault();
//-- Get all table names
TableNames = new DITables(DataPrefix, dbConnection.DILanguageCodeDefault(DataPrefix));
StrQry = "SELECT " + Language.LanguageCode + " FROM " + TableNames.Language;
DtAllLanguages = dbConnection.ExecuteDataTable(StrQry);
foreach (DataRow Row in DtAllLanguages.Rows)
{
RetVal.Add(Convert.ToString(Row[0]));
}
}
catch (Exception)
{
throw;
}
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>
/// 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;
}
/// <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;
}
}
/// <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;
}
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;
}
/// <summary>
/// Updates specified DataBase name as parameter, in DB_Available Table.
/// </summary>
/// <param name="availableDbName"></param>
public void UpdateAvialableDBInSavedFile(string availableDbWName)
{
DIConnectionDetails ConnectionDetails;
DIConnection AvailableDBConnection;
string DatasetPrefix;
string LanguageCode;
DIQueries AvailableDBQuery;
ImportQueries Query;
//' --Create connection with saved file
ConnectionDetails = new DIConnectionDetails(DIServerType.MsAccess, string.Empty, string.Empty, availableDbWName, string.Empty, DAImportCommon.Constants.DBPassword);
AvailableDBConnection = new DIConnection(ConnectionDetails);
DatasetPrefix = AvailableDBConnection.DIDataSetDefault();
LanguageCode = AvailableDBConnection.DILanguageCodeDefault(DatasetPrefix);
AvailableDBQuery = new DIQueries(DatasetPrefix, LanguageCode);
Query = new ImportQueries(AvailableDBQuery);
//' - Update Avialble DB fileName in DB_Available_Database table.
AvailableDBConnection.ExecuteNonQuery(Query.UpdateDB_Available_DatabaseTable(Path.GetFileNameWithoutExtension(availableDbWName)));
//' -- Dispose Connection
AvailableDBConnection.Dispose();
AvailableDBQuery = null;
Query = null;
}
