/// <summary>
/// Extract keyword from DataTable and update Database with Keywords
/// </summary>
/// <param name="presentationFile">Excel work book to read</param>
private int GetKeywordAndUpdateDatabase(Presentation.PresentationType presType, String presTitle, String userKeyword, DataTable presentationDataTable, string PresFile)
{
int RetVal = -1;
// If File is an xls File then get presTitle,UKeyword from Excel File
System.Data.DataTable ExcelSheetDataTable;
System.Data.DataTable UKeywordsDataTable;
String Pres_FileName = string.Empty;
PresentationType Pres_Type = PresentationType.M;
String Title = String.Empty;
String UKeyword = String.Empty;
String Keywords = String.Empty;
String FileNameForTitle = String.Empty; // In Case of Graph and Table title will be title + subtitles and filename
int Map_Suffix_Lenght = 10; //" - Map.xls"
try
{
//If Excel File.
if (PresFile.EndsWith(DICommon.FileExtension.Excel))
{
// Using Excel Control Library
DIExcel DIExl = new DIExcel(PresFile);
// Getting Pres fileName For Database Field
Pres_FileName = Path.GetFileName(PresFile);
// Get Presentation Type pres type abbreviation. Which is entered in Database.M:Map ,T:Table,G:Graph
Pres_Type = GetPresTypeForGalleryDB(presType);
// Get title from excel Presentaion
// In case of Map Title Column will be name of the file minus " - Map.xls"
if (presType==Presentation.PresentationType.Map)
{
// In case of Map Title Column will be name of the file minus " - Map.xls"
Title = this.GetPresentationTitle(Pres_Type, Pres_FileName, DIExl);
//Title = Pres_FileName.Substring(0, Pres_FileName.Length - Map_Suffix_Lenght);
}
// Presentaion is table or Graph
else
{
// Get Presentation Title
Title = this.GetPresentationTitle(Pres_Type, Pres_FileName, DIExl);
}
// --- Get UserKeywords from Excel Presentaion ---
// Get Keywords Sheet into DataTable
UKeywordsDataTable = DIExl.GetDataTableFromSheet(ExcelSheetName.KEYWORDS);
// Get UserKeyword
UKeyword = this.GetUserKeyWords(UKeywordsDataTable);
}
//In case of Web application title and Ukeyword will be same as passed by client application
else
{
Title = presTitle;
UKeyword = userKeyword;
Pres_Type = GetPresTypeForGalleryDB(presType);
}
//Getting Keywords from presentation Data table
Keywords = this.GetKeyWordsFromPresentationDataTable(presentationDataTable);
// Update Database
RetVal = UpdateDBTables(PresFile, Pres_Type, Title, UKeyword, Keywords);
Pres_Nid = RetVal;
}
catch (Exception ex)
{
//Successs = false;
}
return RetVal;
}
/// <summary>
/// Read excel workbook and update Database with Keywords
/// </summary>
/// <param name="presentationFile">Excel work book to read</param>
private Int32 GetKeywordAndUpdateDatabase_Old(string presentationFile)
{
Int32 RetVal = 0;
System.Data.DataTable ExcelSheetDataTable;
System.Data.DataTable UKeywordsDataTable;
String Pres_FileName = string.Empty;
PresentationType Pres_Type = PresentationType.M;
String Title = String.Empty;
String UKeyword = String.Empty;
String Keywords = String.Empty;
String FileNameForTitle = String.Empty; // In Case of Graph and Table title will be title + subtitles and filename
int Map_Suffix_Lenght = 10; //" - Map.xls"
try
{
// Using Excel Control Library
DIExcel DIExl = new DIExcel(presentationFile);
// Getting Pres fileName For Database Field
Pres_FileName = Path.GetFileName(presentationFile);
//Start of getting Data from Excel Sheet
// Check For Presention Type
if (presentationFile.Contains(Presentation.Map_Suffix)) // Presention is Map : Get Title and Data
{
// Get Presentation Type for Database table when presentation file is Map
//Pres_Type = Pres_Type_M;
Pres_Type = PresentationType.M;
// In case of Map Title Column will be name of the file minus " - Map.xls"
Title = Pres_FileName.Substring(0, Pres_FileName.Length - Map_Suffix_Lenght);
// Using Excel Control Function to get value from Sheet
ExcelSheetDataTable = DIExl.GetDataTableFromSheet(ExcelSheetName.MAPDATA);
}
else // Get Title and Data for Graph and Map Presentioan
{
// Get Presentation Type for Database table when presentation file is table or Graph
// Setting Presention Type On the basis of suffix
if (presentationFile.Contains(Presentation.Graph_Suffix) || presentationFile.ToLower().Contains(Presentation.Graph_Suffix.ToLower()))
{
Pres_Type = PresentationType.G;
}
else if (presentationFile.Contains(Presentation.Table_Suffix) || presentationFile.ToLower().Contains(Presentation.Table_Suffix.ToLower()))
{
Pres_Type = PresentationType.T;
}
// Get Presentation Title
Title = this.GetPresentationTitle(Pres_Type, Pres_FileName, DIExl);
// DataTable In case of Graph or Table
ExcelSheetDataTable = DIExl.GetDataTableFromSheet(ExcelSheetName.DATA);
}
//End of getting Data from Excel Sheet
//Getting Keywords
Keywords = this.GetKeyWordsFromPresentationDataTable(ExcelSheetDataTable);
// --- Get UserKeywords ---
// Get Keywords Sheet into DataTable
UKeywordsDataTable = DIExl.GetDataTableFromSheet(ExcelSheetName.KEYWORDS);
// Get UserKeyword
UKeyword = this.GetUserKeyWords(UKeywordsDataTable);
// Update Database
Pres_Nid = UpdateDBTables(presentationFile, Pres_Type, Title, UKeyword, Keywords);
RetVal = Pres_Nid;
}
catch (Exception ex)
{
//Successs = false;
}
return RetVal;
}
/// <summary>
/// Read excel workbook and update Database with Keywords
/// This will Get UserKeywords from Excel and Call overloaded GetKeywordAndUpdateDatabase function with
/// two parameter presFile path and UKeywords
/// </summary>
/// <param name="presentationFile">Excel work book to read</param>
private Int32 GetKeywordAndUpdateDatabase(string presentationFile)
{
Int32 RetVal = 0;
System.Data.DataTable UKeywordsDataTable;
String Pres_FileName = string.Empty;
String UKeyword = String.Empty;
String Keywords = String.Empty;
String FileNameForTitle = String.Empty; // In Case of Graph and Table title will be title + subtitles and filename
try
{
// Using Excel Control Library
DIExcel DIExl = new DIExcel(presentationFile);
// --- Get UserKeywords ---
// Get Keywords Sheet into DataTable
UKeywordsDataTable = DIExl.GetDataTableFromSheet(ExcelSheetName.KEYWORDS);
// Get UserKeyword
UKeyword = this.GetUserKeyWords(UKeywordsDataTable);
//close excel
DIExl.Close();
// Call Other overload
RetVal = this.GetKeywordAndUpdateDatabase(presentationFile, UKeyword);
}
catch (Exception ex)
{
}
return RetVal;
}
private static bool ISValidDESSheet(DIExcel desFile, int sheetIndex)
{
bool RetVal = false;
DataTable SheetTable = null;
try
{
SheetTable = desFile.GetDataTableFromSheet(desFile.GetSheetName(sheetIndex));
// check rows count ( it should be greater than 9) & columns count should be grtthan 7
if (SheetTable != null && SheetTable.Rows.Count > 9 && SheetTable.Columns.Count > 7)
{
// check 4th row [ indicator =<indicator>]
if (!string.IsNullOrEmpty(Convert.ToString(SheetTable.Rows[3][0])) &&
!string.IsNullOrEmpty(Convert.ToString(SheetTable.Rows[3][1])))
{
// check 6th row [ unit =<unit>]
if (!string.IsNullOrEmpty(Convert.ToString(SheetTable.Rows[5][0])) &&
!string.IsNullOrEmpty(Convert.ToString(SheetTable.Rows[5][1])))
{
RetVal = true;
}
}
}
}
catch (Exception)
{
RetVal = false;
}
return RetVal;
}
/// <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;
}
/// <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;
}
public bool ImportMetataFromExcel(MetadataElementType categoryType, MetaDataType metadataType, int elementNId, string xlsFileNameWPath, string xsltFldrPath)
{
bool RetVal = true;
string MetadataText = string.Empty;
string FirstColumnValue = string.Empty;
string SecondColumnValue = string.Empty;
string ThirdColumnValue = string.Empty;
string ElementName = string.Empty;
string ElementGID = string.Empty;
DataTable ExcelDataTable = null;
DataRow Row;
DI7MetaDataBuilder MDBuilder;
DI7MetadataCategoryBuilder MDCategoryBuilder;
DI7MetadataCategoryInfo MetadataCategory;
DIExcel ExcelFile = null;
try
{
// -- Get data table from excel file
ExcelFile = new DIExcel(xlsFileNameWPath);
ExcelDataTable = ExcelFile.GetDataTableFromSheet(ExcelFile.GetSheetName(0));
// -- create database builder objects
MDCategoryBuilder = new DI7MetadataCategoryBuilder(this.DBConnection, this.DBQueries);
MDBuilder = new DI7MetaDataBuilder(this.DBConnection, this.DBQueries);
// -- import metadata reports with category
for (int RowIndex = 1; RowIndex < ExcelDataTable.Rows.Count; RowIndex++)
{
Row = ExcelDataTable.Rows[RowIndex];
FirstColumnValue = Convert.ToString(Row[0]);
SecondColumnValue = Convert.ToString(Row[1]);
ThirdColumnValue = Convert.ToString(Row[2]);
// get element name
if (string.IsNullOrEmpty(ElementName))
{
if (string.IsNullOrEmpty(FirstColumnValue))
{
break;
}
else
{
ElementName = FirstColumnValue;
continue;
}
}
// get element gid/id
if (string.IsNullOrEmpty(ElementGID))
{
if (string.IsNullOrEmpty(FirstColumnValue))
{
break;
}
else
{
ElementGID = FirstColumnValue;
// get element nid by element gid
if (metadataType == MetaDataType.Source)
{
elementNId = this.GetElementNidByGID(ElementName, metadataType);
}
else
{
elementNId = this.GetElementNidByGID(ElementGID, metadataType);
}
// Skip title row by incrementing row index
RowIndex++;
continue;
}
}
// continue if row is blank
if (string.IsNullOrEmpty(FirstColumnValue) && string.IsNullOrEmpty(SecondColumnValue) && string.IsNullOrEmpty(ThirdColumnValue))
{
// reset element value
elementNId = 0;
ElementName = string.Empty;
ElementGID = string.Empty;
continue;
}
else if (elementNId > 0)
{
// import metadata report with metadata category
// get metadata category and metedata report
MetadataCategory = new DI7MetadataCategoryInfo();
MetadataCategory.CategoryName = SecondColumnValue;
MetadataCategory.CategoryGID = FirstColumnValue;
MetadataCategory.CategoryType = DIQueries.MetadataElementTypeText[categoryType];
// import metadata category
MetadataCategory.CategoryNId = MDCategoryBuilder.CheckNInsertCategory(MetadataCategory);
// import metadata report
if (MetadataCategory.CategoryNId > 0)
{
MDBuilder.InsertORUpdateMetadataInfo(metadataType, MetadataCategory.CategoryNId.ToString(), elementNId, this.ReplaceNewLineInMetadataReport(ThirdColumnValue));
}
}
}
}
catch (Exception)
{
RetVal = false;
}
finally
{
if (ExcelFile != null)
{
ExcelFile.Close();
}
}
return RetVal;
}
