public bool TrendExists(TrendAnalysisTheme trend)
{
string query = "SELECT CASE WHEN EXISTS (" +
"select * from trendanalysis_themes (nolock) " +
"WHERE " + (string.IsNullOrEmpty(trend.assignment_group_parent_parent) ? "assignment_group_parent_parent IS NULL " : "assignment_group_parent_parent = @Organisation ") +
"AND " + (string.IsNullOrEmpty(trend.assignment_group_parent) ? "assignment_group_parent IS NULL " : "assignment_group_parent = @SubOrganisation ") +
"AND " + (string.IsNullOrEmpty(trend.configuration_item_application) ? "configuration_item_application IS NULL " : "configuration_item_application = @Application ") +
"AND " + (string.IsNullOrEmpty(trend.TrendThemeGroup) ? "TrendThemeGroup IS NULL " : "TrendThemeGroup = @Theme ") +
"AND " + (string.IsNullOrEmpty(trend.TrendKeyWordGroup) ? "TrendKeyWordGroup IS NULL " : "TrendKeyWordGroup = @Group ") +
"AND " + (string.IsNullOrEmpty(trend.TrendKeyword) ? "TrendKeyword IS NULL " : "TrendKeyword = @Keyword ") +
") THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END";
using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDbConn1"].ConnectionString))
{
return(db.Query <bool>
(query, new
{
Organisation = trend.assignment_group_parent_parent,
SubOrganisation = trend.assignment_group_parent,
Application = trend.configuration_item_application,
Theme = trend.TrendThemeGroup,
Group = trend.TrendKeyWordGroup,
Keyword = trend.TrendKeyword
}).ToList().First());
}
}
public ViewResult AddTrend(TrendAnalysisTheme trendAnalysisTheme)
{
ConfigureVM configureVM = new ConfigureVM();
TrendAnalysisThemeRepository trendRepository = new TrendAnalysisThemeRepository();
configureVM.Organisations = new List <Organisation>()
{
null
};
configureVM.Organisations.AddRange(trendRepository.Organisations());
configureVM.SubOrganisations = new List <SubOrganisation>()
{
null
};
configureVM.SubOrganisations.AddRange(trendRepository.SubOrganisations());
configureVM.Applications = new List <Application>()
{
null
};
configureVM.Applications.AddRange(trendRepository.Applications());
TrendAnalysisThemeRepositoryHelper trendRepositoryHelper = new TrendAnalysisThemeRepositoryHelper();
configureVM.Themes = trendRepositoryHelper.TableToThemeEntity(trendRepository.GetThemes(trendAnalysisTheme.assignment_group_parent_parent, trendAnalysisTheme.assignment_group_parent, trendAnalysisTheme.configuration_item_application));
return(View("Configure", configureVM));
}
public int updateNullKeyword(TrendAnalysisTheme trend)
{
using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDbConn1"].ConnectionString))
{
return(db.QuerySingle <int>
("DECLARE @Id integer " +
"SELECT @Id = TrendThemeID FROM TrendAnalysis_Themes " +
"WHERE " + (string.IsNullOrEmpty(trend.assignment_group_parent_parent) ? "assignment_group_parent_parent IS NULL " : "assignment_group_parent_parent = @Organisation ") +
"AND " + (string.IsNullOrEmpty(trend.assignment_group_parent) ? "assignment_group_parent IS NULL " : "assignment_group_parent = @SubOrganisation ") +
"AND " + (string.IsNullOrEmpty(trend.configuration_item_application) ? "configuration_item_application IS NULL " : "configuration_item_application = @Application ") +
"AND TrendThemeGroup = @Theme " +
"AND TrendKeyWordGroup = @Group " +
"AND TrendKeyword IS NULL " +
"UPDATE trendanalysis_themes SET TrendKeyword = @Keyword WHERE TrendThemeID = @Id " +
"SELECT @Id ",
new
{
Organisation = trend.assignment_group_parent_parent,
SubOrganisation = trend.assignment_group_parent,
Application = trend.configuration_item_application,
Theme = trend.TrendThemeGroup,
Group = trend.TrendKeyWordGroup,
Keyword = trend.TrendKeyword
}));
}
}
public int InsertKeyword(int id, string keyword)
{
TrendAnalysisThemeRepository trendRepository = new TrendAnalysisThemeRepository();
TrendAnalysisTheme trendAnalysisTheme = trendRepository.Get(id);
trendAnalysisTheme.TrendKeyword = keyword;
return(this.InsertKeyword(trendAnalysisTheme));
}
public int InsertKeyword(TrendAnalysisTheme trendAnalysisTheme)
{
TrendAnalysisThemeRepository trendRepository = new TrendAnalysisThemeRepository();
if (trendRepository.TrendExists(trendAnalysisTheme))
{
return(0);
}
if (trendRepository.NullKeywordExists(trendAnalysisTheme))
{
return(trendRepository.updateNullKeyword(trendAnalysisTheme));
}
else
{
return(trendRepository.InsertTrend(trendAnalysisTheme));
}
}
public int InsertGroup(TrendAnalysisTheme trendAnalysisTheme)
{
TrendAnalysisThemeRepository trendRepository = new TrendAnalysisThemeRepository();
if (trendRepository.TrendExists(trendAnalysisTheme))
{
return(0);
}
if (trendRepository.NullGroupExists(trendAnalysisTheme))
{
return(trendRepository.UpdateNullGroup(trendAnalysisTheme));
}
else
{
return(trendRepository.InsertTrend(trendAnalysisTheme));
}
}
public int InsertTrend(TrendAnalysisTheme trend)
{
using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDbConn1"].ConnectionString))
{
return(db.QuerySingle <int>
("DECLARE @Id integer " +
"SELECT @Id = MAX(TrendThemeID)+1 FROM TrendAnalysis_Themes " +
"INSERT INTO trendanalysis_themes (TrendThemeID, assignment_group_parent_parent, assignment_group_parent, " +
"configuration_item_application, TrendThemeGroup, TrendKeyWordGroup, TrendKeyword) " +
"VALUES(@Id, @Organisation, " +
"@SubOrganisation, @Application, @Theme, @Group, @Keyword) " +
"SELECT @Id",
new
{
Organisation = trend.assignment_group_parent_parent,
SubOrganisation = trend.assignment_group_parent,
Application = trend.configuration_item_application,
Theme = trend.TrendThemeGroup,
Group = trend.TrendKeyWordGroup,
Keyword = trend.TrendKeyword
}));
}
}
