public async Task <IActionResult> PutUserSelection([FromRoute] long id, [FromBody] UserSelection userSelection)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
if (id != userSelection.UserSelectionId)
{
return(BadRequest());
}
_context.Entry(userSelection).State = EntityState.Modified;
try
{
await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!UserSelectionExists(id))
{
return(NotFound());
}
else
{
throw;
}
}
return(NoContent());
}
public void AddItems(UserSelection userSelection, IEnumerable <TemplateInfo> templates, Func <TemplateInfo, string> getName)
{
foreach (var template in templates)
{
AddItem(userSelection, template, getName);
}
}
protected async Task AddRightClickTemplatesAsync(string destinationPath, IEnumerable <ITemplateInfo> rightClickTemplates, string projectName, string projectType, string framework, string platform, string language)
{
// Add new items
foreach (var item in rightClickTemplates)
{
GenContext.Current = new FakeContextProvider
{
ProjectName = projectName,
DestinationPath = destinationPath,
GenerationOutputPath = GenContext.GetTempGenerationPath(projectName),
};
var context = new UserSelectionContext(language, platform)
{
ProjectType = projectType,
FrontEndFramework = framework,
};
var newUserSelection = new UserSelection(context)
{
HomeName = string.Empty,
ItemGenerationType = ItemGenerationType.GenerateAndMerge,
};
var templateInfo = GenContext.ToolBox.Repo.GetTemplateInfo(item, context);
_fixture.AddItem(newUserSelection, templateInfo, BaseGenAndBuildFixture.GetDefaultName);
await NewItemGenController.Instance.UnsafeGenerateNewItemAsync(item.GetTemplateType(), newUserSelection);
NewItemGenController.Instance.UnsafeFinishGeneration(newUserSelection);
}
}
public void RebuildLicenses_AddRemovePage()
{
var licenses = new List <SummaryLicenseViewModel>();
licenses.Add(new SummaryLicenseViewModel(new TemplateLicense()
{
Text = "TestLicense", Url = "Test"
}));
var userSelection = new UserSelection()
{
ProjectType = "SplitView",
Framework = "MVVMLight",
HomeName = "Main"
};
userSelection.Pages.Add(("Main", _fixture.Repository.Get(t => t.Identity == "wts.Page.Blank").FirstOrDefault()));
userSelection.Features.Add(("SettingStorage", _fixture.Repository.Get(t => t.Identity == "wts.Feat.SettingsStorage").FirstOrDefault()));
LicensesService.RebuildLicenses(userSelection, licenses);
Assert.True(licenses.Count == 3);
Assert.False(licenses.Any(l => l.Text == "TestLicense"));
Assert.True(licenses.Any(l => l.Text == "MVVM Light"));
Assert.True(licenses.Any(l => l.Text == "Microsoft.Toolkit.Uwp"));
Assert.True(licenses.Any(l => l.Text == "Newtonsoft.Json"));
}
public void RunStarted(object automationObject, Dictionary <string, string> replacementsDictionary, WizardRunKind runKind, object[] customParams)
{
try
{
if (runKind == WizardRunKind.AsNewProject || runKind == WizardRunKind.AsMultiProject)
{
_replacementsDictionary = replacementsDictionary;
GenContext.Current = this;
_userSelection = NewProjectGenController.Instance.GetUserSelection(GenContext.CurrentLanguage);
}
}
catch (WizardBackoutException)
{
var projectDirectory = replacementsDictionary["$destinationdirectory$"];
var solutionDirectory = replacementsDictionary["$solutiondirectory$"];
if (GenContext.ToolBox.Repo.SyncInProgress)
{
GenContext.ToolBox.Repo.CancelSynchronization();
}
CleanupDirectories(projectDirectory, solutionDirectory);
throw;
}
}
protected void btnSelect_Click(object sender, EventArgs e)
{
UserSelection newSelection = populateSelectionParameters();
UserDAL newUserDal = new UserDAL();
IList <Entity.User> users = newUserDal.GetUsers(newSelection);
if (users.Count > 50000)
{
lblError.Text = "Your selection criterion returns more than 50000 records. Please redefine the criterions.";
return;
}
gvUsers.DataSource = users;
gvUsers.PageIndex = 0;
gvUsers.DataBind();
if (users.Count > 10)
{
lblResult.Text = string.Format("Showing result(s) 1-10 of {0}", users.Count.ToString());
}
else if (users.Count > 0)
{
lblResult.Text = string.Format("Showing result(s) 1-{0} of {0}", users.Count.ToString());
}
else
{
lblResult.Text = string.Empty;
}
}
public async void GenerateEmptyProject(string projectType, string framework, string language)
{
SetupFixtureAndContext(language);
var projectTemplate = _fixture.Templates
.FirstOrDefault(t => t.GetTemplateType() == TemplateType.Project &&
t.GetProjectTypeList().Contains(projectType) &&
t.GetFrameworkList().Contains(framework));
var projectName = $"{projectType}{framework}";
ProjectName = projectName;
OutputPath = Path.Combine(_fixture.TestProjectsPath, projectName, projectName);
var userSelection = new UserSelection
{
Framework = framework,
ProjectType = projectType,
HomeName = "Main",
Language = language
};
AddLayoutItems(userSelection, projectTemplate);
await GenController.UnsafeGenerateAsync(userSelection);
//Build solution
var outputPath = Path.Combine(_fixture.TestProjectsPath, projectName);
var result = BuildSolution(projectName, outputPath);
//Assert
Assert.True(result.exitCode.Equals(0), $"Solution {projectTemplate.Name} was not built successfully. {Environment.NewLine}Errors found: {GetErrorLines(result.outputFile)}.{Environment.NewLine}Please see {Path.GetFullPath(result.outputFile)} for more details.");
//Clean
Directory.Delete(outputPath, true);
}
public void RunStarted(object automationObject, Dictionary <string, string> replacementsDictionary, WizardRunKind runKind, object[] customParams)
{
var solutionDirectory = replacementsDictionary["$solutiondirectory$"];
try
{
if (runKind == WizardRunKind.AsNewProject || runKind == WizardRunKind.AsMultiProject)
{
_replacementsDictionary = replacementsDictionary;
GenContext.Current = this;
_userSelection = NewProjectGenController.Instance.GetUserSelection();
}
}
catch (WizardBackoutException)
{
if (Directory.Exists(solutionDirectory))
{
Directory.Delete(solutionDirectory, true);
}
throw;
}
}
private bool AlreadyAdded(UserSelection userSelection, TemplateInfo item)
{
return(userSelection.Pages.Any(p => p.TemplateId == item.TemplateId) ||
userSelection.Features.Any(f => f.TemplateId == item.TemplateId) ||
userSelection.Services.Any(f => f.TemplateId == item.TemplateId) ||
userSelection.Testing.Any(f => f.TemplateId == item.TemplateId));
}
private void SendTelemetry(WizardTypeEnum wizardType, UserSelection userSelection)
{
if (userSelection is null)
{
SendTelemetryWizardCanceled(wizardType);
return;
}
WizardActionEnum wizardAction;
switch (userSelection.ItemGenerationType)
{
case ItemGenerationType.Generate:
wizardAction = WizardActionEnum.GenerateItem;
break;
case ItemGenerationType.GenerateAndMerge:
wizardAction = WizardActionEnum.GenerateAndMergeItem;
break;
default:
wizardAction = WizardActionEnum.GenerateProject;
break;
}
SendTelemetryWizardComplete(wizardType, wizardAction);
}
private void btn_UserSelect_Click(object sender, RoutedEventArgs e)
{
UserSelection userSelect = new UserSelection();
userSelect.Show();
this.Close();
} //btn_Login_Click
private async Task AddTemplateAsync(TemplateInfoViewModel selectedTemplate)
{
if (!selectedTemplate.Disabled && selectedTemplate.CanBeAdded)
{
await UserSelection.AddAsync(TemplateOrigin.UserSelection, selectedTemplate);
}
}
private void OnFrameworkSelected()
{
AddPages.LoadData(Framework.Selected.Name);
AddFeatures.LoadData(Framework.Selected.Name);
UserSelection.Initialize(ProjectType.Selected.Name, Framework.Selected.Name, Language);
WizardStatus.IsLoading = false;
}
public override bool IsSelectionEnabled(MetadataType metadataType)
{
bool result = false;
if (!UserSelection.HasItemsAddedByUser)
{
result = true;
}
else
{
var vm = new QuestionDialogViewModel(metadataType);
var questionDialog = new QuestionDialogWindow(vm);
questionDialog.ShowDialog();
if (vm.Result == DialogResult.Accept)
{
UserSelection.ResetUserSelection();
result = true;
}
else
{
result = false;
}
}
if (result == true)
{
AddPages.ResetUserSelection();
AddFeatures.ResetTemplatesCount();
}
return(result);
}
public override UserSelection CreateUserSelection()
{
var userSelection = new UserSelection()
{
Framework = ConfigFramework,
ProjectType = ConfigProjectType,
HomeName = string.Empty
};
var template = GetActiveTemplate();
if (template != null)
{
var dependencies = GenComposer.GetAllDependencies(template.Template, ConfigFramework);
userSelection.Pages.Clear();
userSelection.Features.Clear();
AddTemplate(userSelection, NewItemSetup.ItemName, template.Template, ConfigTemplateType);
foreach (var dependencyTemplate in dependencies)
{
AddTemplate(userSelection, dependencyTemplate.GetDefaultName(), dependencyTemplate, dependencyTemplate.GetTemplateType());
}
}
return(userSelection);
}
protected void btnShowReport_Click(object sender, EventArgs e)
{
EmailContentDAL newContentDal = new EmailContentDAL();
UserSelection newSelection = new UserSelection();
EmailContent emailContent = newContentDal.GetEmailContentByID(BatchID, out newSelection);
if (newSelection != null)
{
PopulateSelectionCriterion(newSelection);
}
if (emailContent != null)
{
PopulateEmailContent(emailContent);
}
EmailDeliveryDAL newDal = new EmailDeliveryDAL();
IList <EmailDelivery> emails = newDal.GetSendEmail(BatchID);
gvEmailSend.PageIndex = 0;
gvEmailSend.DataSource = emails;
gvEmailSend.DataBind();
if (emails.Count > 10)
{
lblResult.Text = string.Format("Showing result(s) 1-10 of {0}", emails.Count.ToString());
}
else if (emails.Count > 0)
{
lblResult.Text = string.Format("Showing result(s) 1-{0} of {0}", emails.Count.ToString());
}
else
{
lblResult.Text = string.Empty;
}
}
public override bool IsSelectionEnabled(MetadataType metadataType)
{
bool result = false;
if (!UserSelection.HasItemsAddedByUser)
{
result = true;
}
else
{
var vm = new QuestionDialogViewModel(metadataType);
var questionDialog = new QuestionDialogWindow(vm);
questionDialog.Owner = WizardShell.Current;
questionDialog.ShowDialog();
if (vm.Result == DialogResult.Accept)
{
UserSelection.ResetUserSelection();
result = true;
}
else
{
result = false;
}
}
if (result == true)
{
StepsViewModels.Values.ToList().ForEach(vm => vm.ResetTemplatesCount());
}
return(result);
}
private UserSelection GetUserSelection()
{
var userSelection = new UserSelection(ConfigProjectType, ConfigFramework, Language);
userSelection.Add((TemplateSelection.Name, TemplateSelection.Template));
return(userSelection);
}
private void OnCompositionTool()
{
var compositionTool = new CompositionToolWindow(UserSelection.GetUserSelection());
compositionTool.Owner = WizardShell.Current;
compositionTool.ShowDialog();
}
private void conttrail_Click(object sender, EventArgs e)
{
this.Hide();
UserSelection userSelection = new UserSelection();
userSelection.Show();
}
public UserSelection SetupProject(string projectType, string framework, string language, Func <ITemplateInfo, string> getName = null)
{
var userSelection = new UserSelection
{
Framework = framework,
ProjectType = projectType,
Language = language,
};
var layouts = GenComposer.GetLayoutTemplates(userSelection.ProjectType, userSelection.Framework);
foreach (var item in layouts)
{
if (getName != null)
{
AddItem(userSelection, item.Template, getName);
}
else
{
AddItem(userSelection, item.Layout.Name, item.Template);
}
}
userSelection.HomeName = userSelection.Pages.FirstOrDefault().name;
return(userSelection);
}
public void AddItem(UserSelection userSelection, string itemName, ITemplateInfo template)
{
switch (template.GetTemplateType())
{
case TemplateType.Page:
userSelection.Pages.Add((itemName, template));
break;
case TemplateType.Feature:
userSelection.Features.Add((itemName, template));
break;
}
_usedNames.Add(itemName);
var dependencies = GenComposer.GetAllDependencies(template, userSelection.Framework);
foreach (var item in dependencies)
{
if (!AlreadyAdded(userSelection, item))
{
AddItem(userSelection, item.GetDefaultName(), item);
}
}
}
public async void GenerateEmptyProject(string name, string framework, string projId)
{
var projectTemplate = GenerationTestsFixture.Templates.Where(t => t.Identity == projId).FirstOrDefault();
var projectName = $"{name}{framework}";
using (var context = GenContext.CreateNew(projectName, Path.Combine(_fixture.TestProjectsPath, projectName, projectName)))
{
var userSelection = new UserSelection
{
Framework = framework,
ProjectType = projectTemplate.GetProjectType(),
};
AddLayoutItems(userSelection, projectTemplate);
await GenController.UnsafeGenerateAsync(userSelection);
//Build solution
var outputPath = Path.Combine(_fixture.TestProjectsPath, projectName);
var result = BuildSolution(projectName, outputPath);
//Assert
Assert.True(result.exitCode.Equals(0), $"Solution {projectTemplate.Name} was not built successfully. {Environment.NewLine}Errors found: {GetErrorLines(result.outputFile)}.{Environment.NewLine}Please see {Path.GetFullPath(result.outputFile)} for more details.");
//Clean
Directory.Delete(outputPath, true);
}
}
public void RunStarted(object automationObject, Dictionary <string, string> replacementsDictionary, WizardRunKind runKind, object[] customParams)
{
try
{
if (runKind == WizardRunKind.AsNewProject || runKind == WizardRunKind.AsMultiProject)
{
_replacementsDictionary = replacementsDictionary;
GenContext.Current = this;
_userSelection = WizardLauncher.Instance.StartNewProject(_replacementsDictionary["$wts.platform$"], GenContext.CurrentLanguage, new VSStyleValuesProvider());
}
}
catch (WizardBackoutException)
{
var projectDirectory = replacementsDictionary["$destinationdirectory$"];
var solutionDirectory = replacementsDictionary["$solutiondirectory$"];
if (GenContext.ToolBox.Repo.SyncInProgress)
{
GenContext.ToolBox.Repo.CancelSynchronization();
}
CleanupDirectories(DestinationPath);
throw;
}
}
public void Compose_BasicGeneration()
{
GenContext.Current = new TestContextProvider()
{
DestinationPath = string.Empty,
ProjectName = "TestProject",
};
var context = new UserSelectionContext(ProgrammingLanguages.CSharp, "test")
{
ProjectType = "pt1",
FrontEndFramework = "fx1",
};
var userSelection = new UserSelection(context)
{
HomeName = "TestHome",
};
userSelection.Add(new UserSelectionItem()
{
Name = "Main", TemplateId = "Microsoft.Templates.Test.PageTemplate.CSharp"
}, TemplateType.Page);
var genQueue = GenComposer.Compose(userSelection);
AssertBasicParameters(genQueue, context);
}
public void Compose_GenerationWithPropertyBag()
{
GenContext.Current = new TestContextProvider()
{
DestinationPath = string.Empty,
ProjectName = "TestProject",
};
var context = new UserSelectionContext(ProgrammingLanguages.CSharp, "test")
{
ProjectType = "pt1",
FrontEndFramework = "fx4",
};
context.PropertyBag.Add("pb1", "value1");
var userSelection = new UserSelection(context)
{
HomeName = "TestHome",
};
userSelection.Add(new UserSelectionItem()
{
Name = "MainPage", TemplateId = "Microsoft.Templates.Test.PageTemplatePropertyBag.CSharp"
}, TemplateType.Page);
var genQueue = GenComposer.Compose(userSelection);
AssertBasicParameters(genQueue, context);
var property = new KeyValuePair <string, string>("wts.generation.pb1", "value1");
Assert.True(genQueue.Where(g => g.Name == "ProjectTemplatePropertyBag").All(g => g.Parameters.Contains(property)));
}
public async void GenerateAllPagesAndFeaturesRandomNames(string projectType, string framework)
{
var targetProjectTemplate = GenerationTestsFixture.Templates.FirstOrDefault(t => t.GetTemplateType() == TemplateType.Project && t.GetProjectTypeList().Contains(projectType) && t.GetFrameworkList().Contains(framework) && !t.GetIsHidden());
var projectName = $"{projectType}{framework}AllRandom";
ProjectName = projectName;
OutputPath = Path.Combine(_fixture.TestProjectsPath, projectName, projectName);
var userSelection = new UserSelection
{
Framework = framework,
ProjectType = projectType,
HomeName = "Main"
};
AddLayoutItems(userSelection, targetProjectTemplate);
AddItems(userSelection, GetTemplates(framework, TemplateType.Page), GetRandomName);
AddItems(userSelection, GetTemplates(framework, TemplateType.Feature), GetRandomName);
await GenController.UnsafeGenerateAsync(userSelection);
//Build solution
var outputPath = Path.Combine(_fixture.TestProjectsPath, projectName);
var result = BuildSolution(projectName, outputPath);
//Assert
Assert.True(result.exitCode.Equals(0), $"Solution {targetProjectTemplate.Name} was not built successfully. {Environment.NewLine}Errors found: {GetErrorLines(result.outputFile)}.{Environment.NewLine}Please see {Path.GetFullPath(result.outputFile)} for more details.");
//Clean
Directory.Delete(outputPath, true);
}
private void AddTemplate(TemplateInfoViewModel selectedTemplate)
{
if (selectedTemplate.MultipleInstance || !UserSelection.IsTemplateAdded(selectedTemplate))
{
UserSelection.Add(TemplateOrigin.UserSelection, selectedTemplate);
}
}
private async Task AddTemplateAsync(TemplateInfoViewModel selectedTemplate)
{
if (selectedTemplate.MultipleInstance || !UserSelection.IsTemplateAdded(selectedTemplate))
{
await UserSelection.AddAsync(TemplateOrigin.UserSelection, selectedTemplate);
}
}
public async Task <IActionResult> Edit(int id, [Bind("UserId,FullName,ObjectName,Description,Reuse,Reduce,Recycle,Photo,Ecoscore,EcoscoreId")] UserSelection userSelection)
{
if (id != userSelection.UserId)
{
return(NotFound());
}
if (ModelState.IsValid)
{
try
{
_context.Update(userSelection);
await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!UserSelectionExists(userSelection.UserId))
{
return(NotFound());
}
else
{
throw;
}
}
return(RedirectToAction(nameof(Index)));
}
ViewData["EcoscoreId"] = new SelectList(_context.EcoScore.OrderBy(c => c.EcoscoreId), "EcoscoreId", "EcoscoreId", userSelection.EcoscoreId);
ViewData["objectName"] = new SelectList(_context.EcoScore, "objectName", "objectName", userSelection.ObjectName);
return(View(userSelection));
}
/// <summary>
/// Get Area Level using user selection's indicator,time and area nids
/// </summary>
/// <param name="userSelection"></param>
/// <param name="showAllAreaLevel">If True all area level will returned and user selection is not considered </param>
/// <returns></returns>
public string GetAreaLevelByUserselection(UserSelection.UserSelection userSelection, bool showAllAreaLevel)
{
string RetVal = string.Empty;
StringBuilder SbQuery = new StringBuilder();
// If available area level will be returned . Indicator, timeperiod and area selection in userselection is not considered for filtering
if (showAllAreaLevel)
{
SbQuery.Append("SELECT DISTINCT AL." + Area_Level.AreaLevelName + ",AL." + Area_Level.AreaLevel + " FROM " + TablesName.AreaLevel + " AL");
}
else
{
// selected Area level considering selected area in user selection
if (userSelection.AreaNIds.Length > 0)
{
SbQuery.Append("SELECT DISTINCT AL." + Area_Level.AreaLevelName + ", AL." + Area_Level.AreaLevel + " FROM " + TablesName.AreaLevel + " AL, ");
SbQuery.Append(TablesName.Area + " A ");
// Where claues
SbQuery.Append(" WHERE AL." + Area_Level.AreaLevel + "=A." + DIColumns.Area.AreaLevel + " AND A." + DIColumns.Area.AreaNId + " in (" + userSelection.AreaNIds + ")");
}
// Selected area levels using area nid in data table
else
{
SbQuery.Append("SELECT DISTINCT AL." + Area_Level.AreaLevelName + ", AL." + Area_Level.AreaLevel + " FROM " + TablesName.AreaLevel + " AL, ");
SbQuery.Append(TablesName.Area + " A, " + TablesName.Data + " D");
//-- Include Indicator_Unit_Subgroup in FROM clause in case ShowIUS = false (Join on basis of indicatorNId)
if (userSelection.IndicatorNIds.Length > 0 && userSelection.ShowIUS == false)
{
SbQuery.Append(", " + TablesName.IndicatorUnitSubgroup + " IUS");
}
SbQuery.Append(" WHERE AL." + Area_Level.AreaLevel + "=A." + DIColumns.Area.AreaLevel + " AND D." + DIColumns.Data.AreaNId + "=A." + DIColumns.Area.AreaNId);
// If user selection has indicator then filter out only those data in data table where IUSNid match with selected indicators
if (userSelection.IndicatorNIds.Length > 0)
{
if (userSelection.ShowIUS)
{
SbQuery.Append(" AND D." + DIColumns.Data.IUSNId + " in (" + userSelection.IndicatorNIds + ")");
}
else
{
SbQuery.Append(" AND IUS." + Indicator_Unit_Subgroup.IUSNId + "=D." + DIColumns.Data.IUSNId + " AND IUS." + Indicator_Unit_Subgroup.IndicatorNId + " in (" + userSelection.IndicatorNIds + ")");
}
}
if (userSelection.TimePeriodNIds.Length > 0)
{
SbQuery.Append(" AND D." + DIColumns.Data.TimePeriodNId + " in (" + userSelection.TimePeriodNIds + ")");
}
}
}
RetVal = SbQuery.ToString();
return RetVal;
}
/// <summary>
/// Get auto selected AreaNId, DataNId, IUSNId, TimeperiodNId and TimePeriod
/// </summary>
/// <param name="userSelection"></param>
/// <returns></returns>
public string GetAutoSelectedIUSTimePeriodArea(UserSelection.UserSelection userSelection)
{
string RetVal = string.Empty;
try
{
RetVal = "SELECT D." + DIColumns.Data.DataNId + ",D." + DIColumns.Data.IUSNId + ",D." + DIColumns.Data.AreaNId + ",D." + DIColumns.Data.TimePeriodNId;
RetVal += ",T." + DIColumns.Timeperiods.TimePeriod;
RetVal += " FROM " + this.TablesName.Data + " D," + this.TablesName.TimePeriod + " T";
RetVal += " WHERE D." + DIColumns.Data.TimePeriodNId + " = T." + DIColumns.Timeperiods.TimePeriodNId;
if (!string.IsNullOrEmpty(userSelection.IndicatorNIds))
{
RetVal += " AND D." + DIColumns.Data.IUSNId + " IN (" + userSelection.IndicatorNIds + ")";
}
if (!string.IsNullOrEmpty(userSelection.IndicatorNIds))
{
RetVal += " AND D." + DIColumns.Data.IUSNId + " IN (" + userSelection.IndicatorNIds + ")";
}
if (!string.IsNullOrEmpty(userSelection.AreaNIds))
{
RetVal += " AND D." + DIColumns.Data.AreaNId + " IN (" + userSelection.AreaNIds + ")";
}
if (!string.IsNullOrEmpty(userSelection.TimePeriodNIds))
{
RetVal += " AND D." + DIColumns.Data.TimePeriodNId + " IN (" + userSelection.TimePeriodNIds + ")";
}
if (!string.IsNullOrEmpty(userSelection.SourceNIds))
{
RetVal += " AND D." + DIColumns.Data.SourceNId + " IN (" + userSelection.SourceNIds + ")";
}
}
catch (Exception)
{
}
return RetVal;
}
/// <summary>
/// Get Indicator details(NId,Name,Global,GId)fitered on the bais of selections in user selection
/// </summary>
/// <param name="userselection"></param>
/// <returns></returns>
public string GetIndicatorList(UserSelection.UserSelection userselection)
{
string retval = string.Empty;
StringBuilder sbQuery = new StringBuilder();
// Select Clause : select Ind NId, Name,GLobal and GId
sbQuery.Append("SELECT DISTINCT I." + DIColumns.Indicator.IndicatorNId + ", I." + DIColumns.Indicator.IndicatorName + ", I." + DIColumns.Indicator.IndicatorGlobal + ", I." + DIColumns.Indicator.IndicatorGId);
// From Clause
sbQuery.Append(" FROM " + this.TablesName.IndicatorClassifications + " IC," + this.TablesName.Data + " D,");
sbQuery.Append(this.TablesName.IndicatorUnitSubgroup + " IUS," + this.TablesName.Indicator + " I,");
sbQuery.Append(this.TablesName.Area + " A," + this.TablesName.TimePeriod + " T ");
// Where Clause
sbQuery.Append("WHERE IUS." + DIColumns.Indicator_Unit_Subgroup.IndicatorNId + " = I." + DIColumns.Indicator.IndicatorNId);
sbQuery.Append(" AND D." + DIColumns.Data.IUSNId + " = IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + "AND IC." + DIColumns.IndicatorClassifications.ICNId + " = D." + DIColumns.Data.SourceNId);
sbQuery.Append("AND D." + DIColumns.Data.TimePeriodNId + "=T." + DIColumns.Timeperiods.TimePeriodNId + " AND D." + DIColumns.Data.AreaNId + " =A." + DIColumns.Area.AreaNId);
// Adding user selection selections in where clause
if (userselection.IndicatorNIds.Length > 0)
{
//If show IUS then match userselection selected nid with IUSNIds
if (userselection.ShowIUS)
{
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + " in (" + userselection.IndicatorNIds + ")");
}
else
{
//If show IUS then match userselection selected nid with IndicatorNIds
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.IndicatorNId + " in (" + userselection.IndicatorNIds + ") ");
}
}
// If area in found in user selection include them in query where clause
if (userselection.AreaNIds.Length > 0)
{
sbQuery.Append(" AND A." + DIColumns.Area.AreaNId + " in (" + userselection.AreaNIds + ")");
}
// If timeperiod in found in user selection include them in query where clause
if (userselection.TimePeriodNIds.Length > 0)
{
sbQuery.Append(" AND T." + DIColumns.Timeperiods.TimePeriodNId + " in (" + userselection.TimePeriodNIds + ")");
}
//// If fiter is applied for notes. Consider notes filter
//if ((userselection.DataViewFilters.CommentFilter) && userselection.DataViewFilters.CommentDataNIds.Length > 0)
//{
// sbQuery.Append(" AND D.Data_NId IN (" + userselection.DataViewFilters.CommentDataNIds + ")");
//}
// Return query
retval = sbQuery.ToString();
return retval;
}
/// <summary>
/// Get Unit details(NId,Name,Global,GId)fitered on the bais of selections in user selection
/// </summary>
/// <param name="userselection"></param>
/// <returns></returns>
public String GetUnitList(UserSelection.UserSelection userselection)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT DISTINCT ");
if (userselection.DataViewFilters.ShowUnitByIndicator)
{
// Include indicator columns
sbQuery.Append("I." + DIColumns.Indicator.IndicatorNId + ", U." + DIColumns.Unit.UnitNId + ", I." + DIColumns.Indicator.IndicatorName + ", I." + DIColumns.Indicator.IndicatorGlobal + ", I." + DIColumns.Indicator.IndicatorNId + ",");
}
else
{
// Get unit Nid
sbQuery.Append("U." + DIColumns.Unit.UnitNId);
}
//_Query &= ",U.Unit_Name, U.Unit_Global, U.Unit_GId"
sbQuery.Append("U." + DIColumns.Unit.UnitName + ",U." + DIColumns.Unit.UnitGlobal + ",U." + DIColumns.Unit.UnitGId);
//From clause
sbQuery.Append(" FROM " + this.TablesName.IndicatorClassifications + " IC," + this.TablesName.Data + " D,");
sbQuery.Append(this.TablesName.SubgroupVals + " S," + this.TablesName.IndicatorUnitSubgroup + " IUS,");
sbQuery.Append(this.TablesName.Unit + " U," + this.TablesName.Indicator + " I,");
sbQuery.Append(this.TablesName.Area + " A," + this.TablesName.TimePeriod + " T ");
// where clause
sbQuery.Append("WHERE S." + DIColumns.SubgroupVals.SubgroupValNId + " = IUS." + DIColumns.Indicator_Unit_Subgroup.SubgroupValNId + " AND IUS." + DIColumns.Indicator_Unit_Subgroup.UnitNId + " = U." + DIColumns.Unit.UnitNId + " And ");
sbQuery.Append("IUS." + DIColumns.Indicator_Unit_Subgroup.IndicatorNId + " = I." + DIColumns.Indicator.IndicatorNId + " AND D.");
sbQuery.Append(DIColumns.Data.IUSNId + " = IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + " AND IC." + DIColumns.IndicatorClassifications.ICNId + "= D." + DIColumns.Data.SourceNId);
sbQuery.Append(" AND D." + DIColumns.Data.TimePeriodNId + "=T." + DIColumns.Timeperiods.TimePeriodNId + " AND D." + DIColumns.Data.AreaNId + " =A." + DIColumns.Area.AreaNId);
// If indicator in found in user selection include them in query where clause
if (userselection.IndicatorNIds.Length > 0)
{
if (userselection.ShowIUS)
{
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + " in (" + userselection.IndicatorNIds + ")");
}
else
{
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.IndicatorNId + " in (" + userselection.IndicatorNIds + ") ");
}
}
// If area in found in user selection include them in query where clause
if (userselection.AreaNIds.Length > 0)
{
sbQuery.Append(" AND A." + DIColumns.Area.AreaNId + " in (" + userselection.AreaNIds + ")");
}
// If timeperiod in found in user selection include them in query where clause
if (userselection.TimePeriodNIds.Length > 0)
{
sbQuery.Append(" AND T." + DIColumns.Timeperiods.TimePeriodNId + " in (" + userselection.TimePeriodNIds + ")");
}
//// If fiter is applied for notes. Consider notes filter
//if ((userselection.DataViewFilters.CommentFilter) && userselection.DataViewFilters.CommentDataNIds.Length > 0)
//{
// sbQuery.Append(" AND D.Data_NId IN (" + userselection.DataViewFilters.CommentDataNIds + ")");
//}
RetVal = sbQuery.ToString();
return RetVal;
}
/// <summary>
/// Get indicator and subgroup details based on userselection and show IUS
/// </summary>
/// <param name="showSubgroup"></param>
/// <returns></returns>
public String Indicator_Subgroup_Selected(Boolean showSubgroup, UserSelection.UserSelection userSelection, DIServerType serverType)
{
String RetVal;
StringBuilder sbQuery = new StringBuilder();
// If show subgroup, then get subgroup information
if (showSubgroup)
{
// Select Ind NId,Name,Ind_Global,SubgroupValNId,SubgroupVal,subgroupVal_Global, IUSNID
sbQuery.Append("SELECT DISTINCT I." + DIColumns.Indicator.IndicatorNId + ", I." + DIColumns.Indicator.IndicatorName + ", I." + DIColumns.Indicator.IndicatorGlobal);
sbQuery.Append(", S." + DIColumns.SubgroupVals.SubgroupValNId + ", S." + DIColumns.SubgroupVals.SubgroupVal + ", S." + DIColumns.SubgroupVals.SubgroupValGlobal + ", IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId);
sbQuery.Append(" FROM " + this.TablesName.IndicatorClassifications + " IC," + this.TablesName.Data + " D,");
sbQuery.Append(this.TablesName.SubgroupVals + " S," + this.TablesName.IndicatorUnitSubgroup + " IUS,");
sbQuery.Append(this.TablesName.Indicator + " I, " + this.TablesName.Area + " A, " + this.TablesName.TimePeriod + " T");
sbQuery.Append(" WHERE D." + DIColumns.Data.IUSNId + " = IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + " AND D." + DIColumns.Data.TimePeriodNId + "= T." + DIColumns.Timeperiods.TimePeriodNId + " AND D." + DIColumns.Data.AreaNId + " =A." + DIColumns.Area.AreaNId + " AND D." + DIColumns.Data.SourceNId + "=IC." + DIColumns.IndicatorClassifications.ICNId);
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.IndicatorNId + " =I." + DIColumns.Indicator.IndicatorNId + " AND IUS." + DIColumns.Indicator_Unit_Subgroup.SubgroupValNId + " =S." + DIColumns.SubgroupVals.SubgroupValNId);
// If Subgroup deleted
if (userSelection.DataViewFilters.DeletedSubgroupNIds.Length > 0)
{
if (userSelection.DataViewFilters.ShowSubgroupByIndicator)
{
sbQuery.Append(" AND " + TypeCastToString("IUS." + DIColumns.Indicator_Unit_Subgroup.IndicatorNId, serverType) + " +'_' + ");
sbQuery.Append(TypeCastToString("IUS." + DIColumns.Indicator_Unit_Subgroup.SubgroupValNId, serverType));
sbQuery.Append(" NOT IN (" + userSelection.DataViewFilters.DeletedSubgroupNIds + ")");
}
else
{
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.SubgroupValNId + " NOT IN (" + userSelection.DataViewFilters.DeletedSubgroupNIds + ")");
}
}
}
else
{
sbQuery.Append("SELECT DISTINCT I.Indicator_NId,I.Indicator_Name, I.Indicator_Global");
sbQuery.Append(" FROM " + this.TablesName.IndicatorClassifications + " IC," + this.TablesName.Data + " D,");
sbQuery.Append(this.TablesName.IndicatorUnitSubgroup + " IUS,");
sbQuery.Append(this.TablesName.Indicator + " I, " + this.TablesName.Area + " A," + this.TablesName.TimePeriod + " T");
sbQuery.Append(" WHERE D." + DIColumns.Data.IUSNId + " = IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + " AND D." + DIColumns.Data.TimePeriodNId + " =T." + DIColumns.Timeperiods.TimePeriodNId + " AND D." + DIColumns.Data.AreaNId + " =A." + DIColumns.Area.AreaNId + " AND D." + DIColumns.Data.SourceNId + "=IC." + DIColumns.IndicatorClassifications.ICNId);
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.IndicatorNId + "=I." + DIColumns.Indicator.IndicatorNId);
}
//'*** Filters for dataview Indicator-Time-Area
//If user selection have indicator NId then include them in filter criteria of where clause
if (userSelection.IndicatorNIds.Length > 0)
{
if (userSelection.ShowIUS)
{
// Match userselection indicatorNIds are matched with IUSNId
//_Query &= " AND D.IUSNID in (" & .Indicators & ")"
sbQuery.Append(" AND D." + DIColumns.Data.IUSNId + " in (" + userSelection.IndicatorNIds + ")");
}
else
{
// Match userselection indicatorNIds are matched with IndicatorNId
//_Query &= " AND IUS.Indicator_Nid in (" & .Indicators & ") "
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.IndicatorNId + " in (" + userSelection.IndicatorNIds + ") ");
}
}
// Include selected Area in Search
if (userSelection.AreaNIds.Length > 0)
{
// Match for selected NIds in Data table
//_Query &= " AND D.Area_NId in (" & .Areas & ")"
sbQuery.Append(" AND D." + DIColumns.Data.AreaNId + " in (" + userSelection.AreaNIds + ")");
}
if (userSelection.TimePeriodNIds.Length > 0)
{
//_Query &= " AND D.Timeperiod_NId in (" & .Time & ")"
sbQuery.Append(" AND D." + DIColumns.Data.TimePeriodNId + " in (" + userSelection.TimePeriodNIds + ")");
}
// -- Append order by clause
sbQuery.Append(" ORDER BY I." + DIColumns.Indicator.IndicatorName);
RetVal = sbQuery.ToString();
return RetVal;
}
public string GetPresentationData(UserSelection.UserSelection UserSelection, DI_LibDAL.Connection.DIServerType DIServerType)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
string sDataViewBasicQuery = string.Empty;
// Get DataView Basic Query
sDataViewBasicQuery = GetGenericSelectFromWhereClause();
// Indicator Filters
if (UserSelection.IndicatorNIds.Length > 0)
{
if (UserSelection.ShowIUS == true)
{
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + " IN (" + UserSelection.IndicatorNIds + ")");
}
else
{
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.IndicatorNId + " IN (" + UserSelection.IndicatorNIds + ") ");
}
}
// Time Period Filters
if (UserSelection.TimePeriodNIds.Length > 0)
{
sbQuery.Append(" AND T." + DIColumns.Timeperiods.TimePeriodNId + " IN (" + UserSelection.TimePeriodNIds + ")");
}
// Area Filters
if (UserSelection.AreaNIds.Length > 0)
{
sbQuery.Append(" AND A." + DIColumns.Area.AreaNId + " IN (" + UserSelection.AreaNIds + ")");
}
// Dataview Filters
sbQuery.Append(UserSelection.DataViewFilters.SQL_GetDataViewFilters(DIServerType, false));
RetVal = sDataViewBasicQuery + sbQuery.ToString();
return RetVal;
}
/// <summary>
/// Get Source details(sourceNId,Name,Global,recomendedSource) fitered on the bais of selections in user selection
/// If userselection contain ShowSourceByIUS then include indicator unit and subgroup columns
/// </summary>
/// <param name="userselection"></param>
/// <returns></returns>
public String GetSourceList(UserSelection.UserSelection userselection)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT DISTINCT ");
// if ShowSourceByIUS then include indicator , unit and subgroup
if (userselection.DataViewFilters.ShowSourceByIUS)
{
// Include indicator,unit,subgroup columns
sbQuery.Append("D." + DIColumns.Data.IUSNId + ", D." + DIColumns.Data.SourceNId + ", I." + DIColumns.Indicator.IndicatorName + ", I." + DIColumns.Indicator.IndicatorGlobal + ", I." + DIColumns.Indicator.IndicatorNId + ",");
sbQuery.Append(" U." + DIColumns.Unit.UnitName + ", S." + DIColumns.SubgroupVals.SubgroupVal + ", U." + DIColumns.Unit.UnitGlobal + ", S." + DIColumns.SubgroupVals.SubgroupValGlobal);
}
else
{
// Get Source Nid from data table
sbQuery.Append("D." + DIColumns.Data.SourceNId);
}
// Add other source information from ic Table(ICName,global and recomended source)
sbQuery.Append(",IC." + DIColumns.IndicatorClassifications.ICName + ",IC." + DIColumns.IndicatorClassifications.ICGlobal + ",ICIUS." + DIColumns.IndicatorClassificationsIUS.RecommendedSource);
// From Clause
sbQuery.Append(" FROM " + this.TablesName.IndicatorClassifications + " IC," + this.TablesName.Data + " D,");
sbQuery.Append(this.TablesName.SubgroupVals + " S," + this.TablesName.IndicatorUnitSubgroup + " IUS,");
sbQuery.Append(this.TablesName.Unit + " U," + this.TablesName.Indicator + " I,");
sbQuery.Append(this.TablesName.Area + " A," + this.TablesName.TimePeriod + " T,");
sbQuery.Append(this.TablesName.IndicatorClassificationsIUS + " ICIUS ");
//Where clause
sbQuery.Append("WHERE S." + DIColumns.SubgroupVals.SubgroupValNId + " = IUS." + DIColumns.Indicator_Unit_Subgroup.SubgroupValNId + " AND IUS." + DIColumns.Indicator_Unit_Subgroup.UnitNId + " = U." + DIColumns.Unit.UnitNId + " And ");
sbQuery.Append(" IUS." + DIColumns.Indicator_Unit_Subgroup.IndicatorNId + " = I." + DIColumns.Indicator.IndicatorNId + " AND D.");
sbQuery.Append(DIColumns.Data.IUSNId + " = IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + "AND IC." + DIColumns.IndicatorClassifications.ICNId + "= D." + DIColumns.Data.SourceNId);
sbQuery.Append("AND D." + DIColumns.Data.TimePeriodNId + "=T." + DIColumns.Timeperiods.TimePeriodNId + " AND D." + DIColumns.Data.AreaNId + " =A." + DIColumns.Area.AreaNId);
sbQuery.Append("AND IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + "=ICIUS." + DIColumns.IndicatorClassificationsIUS.IUSNId + " AND ICIUS." + DIColumns.IndicatorClassificationsIUS.ICNId + "=IC." + DIColumns.IndicatorClassificationsIUS.ICNId);
// If indicator in found in user selection include them in query where clause
if (userselection.IndicatorNIds.Length > 0)
{
if (userselection.ShowIUS)
{
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + " in (" + userselection.IndicatorNIds + ")");
}
else
{
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.IndicatorNId + " in (" + userselection.IndicatorNIds + ") ");
}
}
// If area in found in user selection include them in query where clause
if (userselection.AreaNIds.Length > 0)
{
sbQuery.Append(" AND A." + DIColumns.Area.AreaNId + " in (" + userselection.AreaNIds + ")");
}
// If timeperiod in found in user selection include them in query where clause
if (userselection.TimePeriodNIds.Length > 0)
{
sbQuery.Append(" AND T." + DIColumns.Timeperiods.TimePeriodNId + " in (" + userselection.TimePeriodNIds + ")");
}
//// If fiter is applied for notes. Consider notes filter
//if ((userselection.DataViewFilters.CommentFilter) && userselection.DataViewFilters.CommentDataNIds.Length > 0)
//{
// sbQuery.Append(" AND D.Data_NId IN (" + userselection.DataViewFilters.CommentDataNIds + ")");
//}
// Return query
RetVal = sbQuery.ToString();
return RetVal;
}
/// <summary>
/// Gets DISTINCT INdicator_Name, Unit_Name, SubgroupVal for which Data exists in DataBase, applying filter of userSelection's I, U, S, A, T, Source.
/// </summary>
/// <param name="userSelections">userSelection object</param>
/// <returns>SQl query.</returns>
public string GetAutoDistinctIUS(UserSelection.UserSelection userSelections)
{
string RetVal = string.Empty;
RetVal = "SELECT Distinct I." + DIColumns.Indicator.IndicatorName + ", I." + DIColumns.Indicator.IndicatorGlobal + ", U." + DIColumns.Unit.UnitName + ", U." + DIColumns.Unit.UnitGlobal + ", S." + DIColumns.SubgroupVals.SubgroupVal + ", S." + DIColumns.SubgroupVals.SubgroupValGlobal + ", I." + DIColumns.Indicator.IndicatorNId + ", U." + DIColumns.Unit.UnitNId + ", S." + DIColumns.SubgroupVals.SubgroupValNId + ", IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId +
" FROM (" + this.TablesName.SubgroupVals + " AS S INNER JOIN (" + this.TablesName.Unit + " AS U INNER JOIN (" + this.TablesName.Indicator + " AS I INNER JOIN " + this.TablesName.IndicatorUnitSubgroup + " AS IUS ON I." + DIColumns.Indicator.IndicatorNId + " = IUS." + DIColumns.Indicator_Unit_Subgroup.IndicatorNId + ") ON U." + DIColumns.Unit.UnitNId + " = IUS." + DIColumns.Indicator_Unit_Subgroup.UnitNId + ") ON S." + DIColumns.SubgroupVals.SubgroupValNId + " = IUS." + DIColumns.Indicator_Unit_Subgroup.SubgroupValNId + ") INNER JOIN " + this.TablesName.Data + " AS D ON IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + " = D." + DIColumns.Data.IUSNId +
" Where 1 = 1";
// Check if Userselection.ShowIUS is true
if (!string.IsNullOrEmpty(userSelections.IndicatorNIds) & (userSelections.ShowIUS))
{
RetVal += " AND IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + " IN (" + userSelections.IndicatorNIds + ")";
}
else
{
if (!string.IsNullOrEmpty(userSelections.IndicatorNIds))
{
RetVal += " AND I." + DIColumns.Indicator.IndicatorNId + " IN (" + userSelections.IndicatorNIds + ")";
}
if (!string.IsNullOrEmpty(userSelections.UnitNIds))
{
RetVal += " AND (U." + DIColumns.Unit.UnitNId + " ) IN (" + userSelections.UnitNIds + ")";
}
//subgroupvalNids
if (!string.IsNullOrEmpty(userSelections.SubgroupValNIds))
{
RetVal += " AND (S." + DIColumns.SubgroupVals.SubgroupValNId + ") IN (" + userSelections.SubgroupValNIds + ")";
}
}
// TimePeriod NID filter
if (!string.IsNullOrEmpty(userSelections.TimePeriodNIds))
{
RetVal += " AND (D." + DIColumns.Timeperiods.TimePeriodNId + ") IN (" + userSelections.TimePeriodNIds + ")";
}
// AreaNid filter
if (!string.IsNullOrEmpty(userSelections.AreaNIds))
{
RetVal += " AND (D." + DIColumns.Area.AreaNId + ") IN (" + userSelections.AreaNIds + ")";
}
// Source NID filter
if (!string.IsNullOrEmpty(userSelections.SourceNIds))
{
RetVal += " AND (D." + DIColumns.Data.SourceNId + ") IN (" + userSelections.SourceNIds + ")";
}
return RetVal;
}
/// <summary>
/// Gets records form data table based on User selection
/// </summary>
/// <param name="UserSelection">an instance of User selection object</param>
/// <param name="fieldSelection">NId, Name, Light, Heavy</param>
/// <returns></returns>
public string GetDataByIUSTimePeriodAreaSource(UserSelection.UserSelection UserSelection, FieldSelection fieldSelection)
{
string RetVal = string.Empty;
RetVal = GetDataNIDs(UserSelection.IndicatorNIds, UserSelection.TimePeriodNIds, UserSelection.AreaNIds, UserSelection.SourceNIds, UserSelection.ShowIUS, fieldSelection, false);
return RetVal;
}
/// <summary>
/// Gets records form data table based on User selection
/// </summary>
/// <param name="UserSelection">an instance of User selection object</param>
/// <param name="DIServerType">Server type required for database specific syntax like concat etc.</param>
/// <param name="dataNIds">
/// Comma delimited datanids which may be blank.
/// If datanid is provided then filter is applied on the basis datanids instead of indicatornid + timeperiodnids + areanids
/// </param>
/// <returns>Sql query text</returns>
public string GetDataNIDByIUSTimePeriodAreaSource(UserSelection.UserSelection UserSelection, DI_LibDAL.Connection.DIServerType DIServerType, string dataNIds)
{
string RetVal = string.Empty;
RetVal = GetDataIUSSourceNIDByUserSelection(UserSelection, FieldSelection.NId, DIServerType, dataNIds);
return RetVal;
}
/// <summary>
/// Get auto selected timeperiods between from and to time periods. if toTimeperiod is empty, it will return Timperiods greater then fromTimeperiods
/// </summary>
/// <param name="fromTimperiod"></param>
/// <param name="toTimeperiod"></param>
/// <param name="userSelection"></param>
/// <returns></returns>
public string GetAutoSelectedTimePeriodsRange(string fromTimperiod, string toTimeperiod, UserSelection.UserSelection userSelection)
{
string RetVal = string.Empty;
try
{
RetVal = " SELECT DISTINCT T." + DIColumns.Timeperiods.TimePeriodNId + ",T." + DIColumns.Timeperiods.TimePeriod;
RetVal += " FROM " + this.TablesName.TimePeriod + " T," + this.TablesName.Data + " D";
RetVal += " WHERE T." + DIColumns.Timeperiods.TimePeriodNId + " = D." + DIColumns.Data.TimePeriodNId;
if (!string.IsNullOrEmpty(userSelection.IndicatorNIds))
{
if (userSelection.ShowIUS)
{
RetVal += " AND D." + DIColumns.Data.IUSNId + " IN (" + userSelection.IndicatorNIds + ")";
}
else
{
RetVal += " AND D." + DIColumns.Data.IndicatorNId + " IN (" + userSelection.IndicatorNIds + ")";
}
}
if (!string.IsNullOrEmpty(userSelection.AreaNIds))
{
RetVal += " AND D." + DIColumns.Data.AreaNId + " IN (" + userSelection.AreaNIds + ")";
}
if (!string.IsNullOrEmpty(userSelection.SourceNIds))
{
RetVal += " AND D." + DIColumns.Data.SourceNId + " IN (" + userSelection.SourceNIds + ")";
}
if (!string.IsNullOrEmpty(toTimeperiod))
{
RetVal += " AND T." + DIColumns.Timeperiods.TimePeriod + " BETWEEN '" + fromTimperiod + "' and '" + toTimeperiod + "'";
}
else if (!string.IsNullOrEmpty(fromTimperiod))
{
RetVal += " AND T." + DIColumns.Timeperiods.TimePeriod + " >= '" + fromTimperiod + "'";
}
}
catch (Exception)
{
}
return RetVal;
}
/// <summary>
/// Gets records form data table based on User selection
/// </summary>
///<param name="UserSelection">an instance of User selection object</param>
/// <param name="fieldSelection">NId, Name, Light, Heavy</param>
/// <param name="DIServerType">Server type required for database specific syntax like concat etc.</param>
/// <param name="dataNIds">
/// Comma delimited datanids which may be blank.
/// If datanid is provided then filter is applied on the basis datanids instead of indicatornid + timeperiodnids + areanids
/// </param>
/// <returns></returns>
private string GetDataIUSSourceNIDByUserSelection(UserSelection.UserSelection UserSelection, FieldSelection fieldSelection, DI_LibDAL.Connection.DIServerType DIServerType, string dataNIds)
{
//
// --------- sDataNIDs (Length > 0 then apply Filters) ---------
//
// -- sDataNIDs will have comma seperated Data NIDs only when this function is being used to Apply Filters on the already created Data View
// -- In such a case sDataNIDs will hold all the DataNIDs of the created DataView
//
// -- 1. Will be used with Filters only
// -- 2. If sDataNIDs is not blank then it means that this function is being used to set Filters
// -- 3. TIME PERIOD Table - Will require a Check for MRD filter also - In this Case Time Period Table will be used
// -- 4. In this Case no Filters on Area, Time and Indicator
//
// -- SPECIAL CASE
// -- With too many DataNIDs, the Query might fail - Maximum limit set to 60,000 length
// -- In such a scenario, sDataNIDs filter will not be used and the Filters for Area, Time and Indicator will be used
// -- Also MRD will be applied if applicable
//
string RetVal = string.Empty;
string indicatorNIds = UserSelection.IndicatorNIds;
string timePeriodNIds = UserSelection.TimePeriodNIds;
string areaNIds = UserSelection.AreaNIds;
string sourceNIds = UserSelection.SourceNIds;
bool showIUS = UserSelection.ShowIUS;
bool UseIUSTable = false;
int iDataNIDLen = dataNIds.Length;
int iMaxDataNIdLen = 60000;
StringBuilder sbQuery = new StringBuilder();
// SELECT Clause
switch (fieldSelection)
{
case FieldSelection.NId:
sbQuery.Append("SELECT D." + DIColumns.Data.DataNId + ", D." + DIColumns.Data.IUSNId + ", D." + DIColumns.Data.TimePeriodNId + ", D." + DIColumns.Data.AreaNId + ", D." + DIColumns.Data.SourceNId);
// Use TimePeriod Table (Check comments on top - MRD case)
if (iDataNIDLen > 0 && UserSelection.DataViewFilters.MostRecentData)
{
// Check comments on top - MRD case
sbQuery.Append(", T." + DIColumns.Timeperiods.TimePeriod);
}
break;
case FieldSelection.Name:
sbQuery.Append("SELECT I." + DIColumns.Indicator.IndicatorName + ",U." + DIColumns.Unit.UnitName + ",SGV." + DIColumns.SubgroupVals.SubgroupVal + ",T." + DIColumns.Timeperiods.TimePeriod + ",A." + DIColumns.Area.AreaName + ",D." + DIColumns.Data.DataValue + ",IC." + DIColumns.IndicatorClassifications.ICName);
break;
case FieldSelection.Light:
sbQuery.Append("SELECT IUS." + DIColumns.Data.IUSNId + ",I." + DIColumns.Indicator.IndicatorNId + ",I." + DIColumns.Indicator.IndicatorName + ",U." + DIColumns.Unit.UnitNId + ",U." + DIColumns.Unit.UnitName + ",SGV." + DIColumns.SubgroupVals.SubgroupValNId + ",SGV." + DIColumns.SubgroupVals.SubgroupVal + ",T." + DIColumns.Timeperiods.TimePeriodNId + ",T." + DIColumns.Timeperiods.TimePeriod + ",A." + DIColumns.Area.AreaNId + ",A." + DIColumns.Area.AreaName + ",D." + DIColumns.Data.DataValue + ",IC." + DIColumns.IndicatorClassifications.ICNId + ",IC." + DIColumns.IndicatorClassifications.ICName);
break;
case FieldSelection.Heavy:
sbQuery.Append(this.GetGenericSelectClause());
break;
default:
break;
}
// FROM Clause
switch (fieldSelection)
{
case FieldSelection.NId:
sbQuery.Append(" FROM " + this.TablesName.Data + " AS D");
// Filters on - Unit and Subgroup
if (UserSelection.DataViewFilters.DeletedSubgroupNIds.Length > 0 || UserSelection.DataViewFilters.DeletedUnitNIds.Length > 0)
{
// -- DataView - If Filtered by Unit or Subgroup
// -- Use IUS Table in this case
UseIUSTable = true;
}
// Use IUS Table
if (UseIUSTable)
{
sbQuery.Append(", " + this.TablesName.IndicatorUnitSubgroup + " AS IUS ");
}
// Use Time Period Table
if (iDataNIDLen > 0 && UserSelection.DataViewFilters.MostRecentData)
{
// Check comments on top
sbQuery.Append(", " + this.TablesName.TimePeriod + " AS T ");
}
break;
case FieldSelection.Name:
case FieldSelection.Light:
case FieldSelection.Heavy:
sbQuery.Append(this.GetGenericFromClause());
break;
default:
break;
}
// WHERE Clause
switch (fieldSelection)
{
case FieldSelection.NId:
sbQuery.Append(" WHERE 1=1 ");
if (UseIUSTable)
{
// Join between IUS and Data Table
sbQuery.Append(" AND D." + DIColumns.Data.IUSNId + "=IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId);
}
// Use Time Period Table - MRD Filter
if (iDataNIDLen > 0 && UserSelection.DataViewFilters.MostRecentData)
{
// Check comments on top - MRD Filter
sbQuery.Append(" AND D." + DIColumns.Data.TimePeriodNId + "=T." + DIColumns.Timeperiods.TimePeriodNId);
}
break;
case FieldSelection.Name:
case FieldSelection.Light:
case FieldSelection.Heavy:
sbQuery.Append(this.GetGenericWhereClause());
break;
default:
break;
}
// Filters on Area, Indicator and Time Period
if (iDataNIDLen == 0 || iDataNIDLen > iMaxDataNIdLen)
{
// Area
if (areaNIds.Trim().Length > 0)
{
sbQuery.Append(" AND D." + DIColumns.Data.AreaNId + " IN (" + areaNIds + ")");
}
// Indicator
if (indicatorNIds.Trim().Length > 0)
{
if (showIUS == false)
{
sbQuery.Append(" AND D." + DIColumns.Data.IndicatorNId + " IN (" + indicatorNIds + ") ");
}
else
{
sbQuery.Append(" AND D." + DIColumns.Data.IUSNId + " IN (" + indicatorNIds + ")");
}
}
// Time Period
if (timePeriodNIds.Trim().Length > 0)
{
sbQuery.Append(" AND D." + DIColumns.Data.TimePeriodNId + " IN (" + timePeriodNIds + ")");
}
// Source
if (sourceNIds.Trim().Length > 0)
{
sbQuery.Append(" AND D." + DIColumns.Data.SourceNId + " IN (" + sourceNIds + ")");
}
}
else
{
// IF the length of sDataNIDs > 0 then apply filter on DataNIDs
sbQuery.Append(" AND D." + DIColumns.Data.DataNId + " IN (" + dataNIds + ")");
}
// ---------------------------------------------
// --------- Filters - DataView --------- Exclude Deleted Data Point filters as they will be part of main dataview
// ---------------------------------------------
if (UserSelection.DataViewFilters.MostRecentData == true)
{
// For MRD case Deleted records should be discarded ie filter clause for deleted record should be considered
sbQuery.Append(UserSelection.DataViewFilters.SQL_GetDataViewFilters(DIServerType, false));
}
else
{
// For other case Deleted records should be part of dataview ie filter clause for deleted record should be excluded
sbQuery.Append(UserSelection.DataViewFilters.SQL_GetDataViewFilters(DIServerType, true));
}
RetVal = sbQuery.ToString();
return RetVal;
}