public static List <Record_Variable> GetThematicFocus(Guid languageID, List <Guid> listOfRecordIds)
{
Guid startImplementationYear = new Guid(AppSettingsUtility.GetString(AppSettingsKeys.StartYearVariableID));
Guid endImplementationYear = new Guid(AppSettingsUtility.GetString(AppSettingsKeys.EndYearVariableID));
int currentYear = DateTime.Now.Year;
List <Guid> recordIdFilteredYear = new List <Guid>();
using (var context = new TSMContext())
{
var filteredRecordsID = listOfRecordIds;
var result1 = (from rv in context.Record_Variables where filteredRecordsID.Contains(rv.RecordID) && rv.LanguageID == languageID && (rv.VariableID == startImplementationYear || rv.VariableID == endImplementationYear) select rv).ToList();
foreach (var rec in listOfRecordIds)
{
RecordYear RecVar = new RecordYear();
try
{
var rec1 = result1.Where(r => r.RecordID == rec && r.VariableID == startImplementationYear).FirstOrDefault().Value;
var rec2 = result1.Where(r => r.RecordID == rec && r.VariableID == endImplementationYear).FirstOrDefault().Value;
RecVar.RecordID = rec;
RecVar.StartYear = Convert.ToInt32(rec1);
RecVar.EndYear = Convert.ToInt32(rec2);
if (RecVar.EndYear >= currentYear)
{
recordIdFilteredYear.Add(rec);
}
}
catch { }
}
var ThematicFocusIds = (from v in context.Variables where v.VariableCategory == VariableCategory.ThematicFocus select v.ID);
var query = (from rv in context.Record_Variables.Include("Variable")
where recordIdFilteredYear.Contains(rv.RecordID)
join vl in context.Variable_Languages
on rv.VariableID equals vl.ID
where vl.LanguageID == languageID && ThematicFocusIds.Contains(rv.VariableID) &&
rv.Variable.IsDeleted == false && rv.Variable.IsActive == true && rv.Value == "true"
group vl
by vl.DisplayName into grps
select new
{
name = grps.Key,
count = grps.Count()
}).OrderBy(vl => vl.name);
List <Record_Variable> listRecordVariable = new List <Record_Variable>();
foreach (var item in query)
{
Record_Variable recordVariable = new Record_Variable();
recordVariable.VariableName = item.name.ToString();
recordVariable.RecordCount = item.count;
listRecordVariable.Add(recordVariable);
}
return(listRecordVariable);
}
}
/// <summary>
/// line chart home page load
/// </summary>
/// <param name="languageID"></param>
/// <returns></returns>
public static List <Record_Variable> GetRecordInYear(Guid languageID)
{
Guid yearVariableID = new Guid(AppSettingsUtility.GetString(AppSettingsKeys.YearVariableID));
Guid startImplementationYear = new Guid(AppSettingsUtility.GetString(AppSettingsKeys.StartYearVariableID));
Guid endImplementationYear = new Guid(AppSettingsUtility.GetString(AppSettingsKeys.EndYearVariableID));
List <Record_Variable> recordVariables = new List <Record_Variable>();
List <Record_Variable> results = new List <Record_Variable>();
List <Guid> recordIds = new List <Guid>();
using (var context = new TSMContext())
{
recordVariables = context.Record_Variables.Where(rv => rv.LanguageID == languageID && (rv.VariableID == yearVariableID || rv.VariableID == startImplementationYear || rv.VariableID == endImplementationYear)).ToList();
recordIds = context.Record_Languages.Where(rl => rl.LanguageID == languageID).Select(rl => rl.ID).Distinct().ToList();
}
int currentYear = DateTime.Now.Year;
int startYear = Convert.ToInt32(recordVariables.Where(rv => rv.VariableID == startImplementationYear).OrderBy(rv => rv.Value).FirstOrDefault().Value);
var recordYears = new List <RecordYear>();
foreach (var recordId in recordIds)
{
if (recordVariables.Any(rv => rv.RecordID == recordId))
{
var recordYear = new RecordYear();
recordYear.RecordID = recordId;
//Start
var start = recordVariables.Where(rv => rv.RecordID == recordId && rv.VariableID == startImplementationYear && rv.Value != "").FirstOrDefault();
if (start != null)
{
recordYear.StartYear = Convert.ToInt32(start.Value);
}
//End
var end = recordVariables.Where(rv => rv.RecordID == recordId && rv.VariableID == endImplementationYear && rv.Value != "").FirstOrDefault();
if (end != null)
{
recordYear.EndYear = Convert.ToInt32(end.Value);
}
//Year
var year = recordVariables.Where(rv => rv.RecordID == recordId && rv.VariableID == yearVariableID && rv.Value != "").FirstOrDefault();
if (year != null && recordYear.StartYear == 0)
{
recordYear.StartYear = Convert.ToInt32(year.Value);
recordYear.EndYear = recordYear.StartYear + 5;
}
recordYears.Add(recordYear);
}
}
for (int count = startYear; count <= currentYear; count++)
{
var result = new Record_Variable();
result.Value = count.ToString();
result.RecordCount = recordYears.Where(ry => ry.StartYear <= count && ry.EndYear >= count).Count();
results.Add(result);
}
return(results);
}
/// <summary>
/// Get record per document in current or filtered year
/// </summary>
/// <param name="languageID"></param>
/// <returns></returns>
public static List <Record_Variable> GetRecordPerDocumentType(Guid languageID, List <Guid> listOfRecordIds)
{
Guid yearVariableID = new Guid(AppSettingsUtility.GetString(AppSettingsKeys.YearVariableID));
var documentTypeVariableID = new Guid(AppSettingsUtility.GetString(AppSettingsKeys.DocumentTypeVariableID));
Guid startImplementationYear = new Guid(AppSettingsUtility.GetString(AppSettingsKeys.StartYearVariableID));
Guid endImplementationYear = new Guid(AppSettingsUtility.GetString(AppSettingsKeys.EndYearVariableID));
List <Record_Variable> listRecordVariable = new List <Record_Variable>();
List <Guid> recordIds = new List <Guid>();
int currentYear = DateTime.Now.Year;
using (var context = new TSMContext())
{
// var filteredRecords = GetRecordList(languageID, selectedRegions, selectedCountries, selectedSectors, selectedDocumentTypes, documentTypeVariableID);
//var filteredRecordsID = filteredRecords.Select(r => r.ID).ToList();
var filteredRecordsID = listOfRecordIds;
var result = (from rv in context.Record_Variables where filteredRecordsID.Contains(rv.RecordID) && rv.LanguageID == languageID && (rv.VariableID == startImplementationYear || rv.VariableID == endImplementationYear) select rv).ToList();
recordIds = context.Record_Languages.Where(rl => rl.LanguageID == languageID).Select(rl => rl.ID).Distinct().ToList();
List <Record_Variable> listRecVar = new List <Record_Variable>();
List <RecordYear> ryear = new List <RecordYear>();
foreach (var rec in listOfRecordIds)
{
RecordYear RecVar = new RecordYear();
try
{
var rec1 = result.Where(r => r.RecordID == rec && r.VariableID == startImplementationYear).FirstOrDefault().Value;
var rec2 = result.Where(r => r.RecordID == rec && r.VariableID == endImplementationYear).FirstOrDefault().Value;
RecVar.RecordID = rec;
RecVar.StartYear = Convert.ToInt32(rec1);
RecVar.EndYear = Convert.ToInt32(rec2);
if (RecVar.EndYear >= currentYear)
{
ryear.Add(RecVar);
}
}
catch
{
var resultSubstitute = context.Record_Variables.Where(r => r.RecordID == rec && r.VariableID == yearVariableID && r.LanguageID == languageID).FirstOrDefault();
if (resultSubstitute != null)
{
if (resultSubstitute.Value != "")
{
RecVar.RecordID = rec;
RecVar.StartYear = Convert.ToInt32(resultSubstitute.Value);
RecVar.EndYear = Convert.ToInt32(resultSubstitute.Value) + 5;
if (RecVar.EndYear >= currentYear)
{
ryear.Add(RecVar);
}
}
}
}
//var rec1 = rec.Where(ry => ry.StartYear <= count && ry.EndYear >= count).Count();
}
foreach (var item in ryear)
{
Record_Variable recordVariable = new Record_Variable();
recordVariable = (from rv in context.Record_Variables where rv.VariableID == documentTypeVariableID && rv.RecordID == item.RecordID && rv.LanguageID == languageID select rv).ToList().FirstOrDefault();
if (recordVariable != null)
{
listRecordVariable.Add(recordVariable);
}
}
var groupListRecord = from i in listRecordVariable group i by i.Value into grps select new { value = grps.Key, recordCount = grps.Count() };
List <Record_Variable> recordVariables = new List <Record_Variable>();
Record_Variable recordVaraible;
foreach (var record in groupListRecord)
{
recordVaraible = new Record_Variable();
recordVaraible.Value = record.value;
recordVaraible.RecordCount = record.recordCount;
recordVariables.Add(recordVaraible);
}
return(recordVariables);
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public static DocumentData GetSitcAndRecord(Guid languageID, List <Guid> listRecord)
{
Guid startImplementationYear = new Guid(AppSettingsUtility.GetString(AppSettingsKeys.StartYearVariableID));
Guid endImplementationYear = new Guid(AppSettingsUtility.GetString(AppSettingsKeys.EndYearVariableID));
//List<Record_Variable> listRecordVariable = new List<Record_Variable>();
List <Guid> recordIdFilteredYear = new List <Guid>();
int currentYear = DateTime.Now.Year;
List <RecordYear> ryear = new List <RecordYear>();
using (var context = new TSMContext())
{
var filteredRecordsID = listRecord;
var result1 = (from rv in context.Record_Variables where filteredRecordsID.Contains(rv.RecordID) && rv.LanguageID == languageID && (rv.VariableID == startImplementationYear || rv.VariableID == endImplementationYear) select rv).ToList();
foreach (var rec in listRecord)
{
RecordYear RecVar = new RecordYear();
try
{
var rec1 = result1.Where(r => r.RecordID == rec && r.VariableID == startImplementationYear).FirstOrDefault().Value;
var rec2 = result1.Where(r => r.RecordID == rec && r.VariableID == endImplementationYear).FirstOrDefault().Value;
RecVar.RecordID = rec;
RecVar.StartYear = Convert.ToInt32(rec1);
RecVar.EndYear = Convert.ToInt32(rec2);
if (RecVar.EndYear >= currentYear)
{
recordIdFilteredYear.Add(rec);
}
}
catch { }
}
var filterSectorRecords = (from r in context.Records where recordIdFilteredYear.Contains(r.ID) && r.Sectors.Count > 0 && r.IsDeleted == false && r.IsActive == true select r).ToList();
int serviceCount = 0;
int productCount = 0;
foreach (var rec in filterSectorRecords)
{
foreach (var r in rec.Sectors)
{
if (r.Type == SectorType.Product)
{
productCount = productCount + 1;
}
else if (r.Type == SectorType.Service)
{
serviceCount = serviceCount + 1;
}
}
}
DocumentData documentData = new DocumentData();
documentData.ResultWithSitcWTOCount = serviceCount;
documentData.ResultOfOtherRecordCount = productCount;
return(documentData);
//var filteredRecords = GetRecordList(languageID, selectedRegions, selectedCountries, selectedSectors, selectedDocumentTypes, documentTypeVariableID);
//var filteredRecordsID = filteredRecords.Select(r => r.ID).ToList();
//var filteredRecordsID = listRecord;
//DocumentData documentData = new DocumentData();
//documentData.ResultWithSitcWTOCount = (from r in context.Records where recordIdFilteredYear.Contains(r.ID) && r.Sectors.Count > 0 select r).ToList().Count;
//documentData.ResultOfOtherRecordCount = (from r in context.Records where recordIdFilteredYear.Contains(r.ID) && r.Sectors.Count == 0 select r).ToList().Count;
//return documentData;
}
}
public static List <Record_Variable> GetRecordInYear(Guid languageID, List <Guid> listOfRecordId)
{
Guid yearVariableID = new Guid(AppSettingsUtility.GetString(AppSettingsKeys.YearVariableID));
Guid startImplementationYear = new Guid(AppSettingsUtility.GetString(AppSettingsKeys.StartYearVariableID));
Guid endImplementationYear = new Guid(AppSettingsUtility.GetString(AppSettingsKeys.EndYearVariableID));
//using (var context = new TSMContext())
//{
// // var filteredRecords = GetRecordList(languageID, selectedRegions, selectedCountries, selectedSectors, selectedDocumentTypes, documentTypeVariableID);
// //var filteredRecordsID = filteredRecords.Select(r => r.ID).ToList();
// var filteredRecordsID = listOfRecordId;
// var documentTypeVariableID=
// var result = from r1 in context.Record_Variables
// where filteredRecordsID.Contains(r1.RecordID) && r1.VariableID == yearVariableID && r1.LanguageID == languageID
// group r1 by r1.Value into grps
// select new
// {
// value = grps.Key,
// recordCount = grps.Count()
// };
// List<Record_Variable> recordVariables = new List<Record_Variable>();
// Record_Variable recordVaraible;
// foreach (var record in result)
// {
// recordVaraible = new Record_Variable();
// recordVaraible.Value = record.value;
// recordVaraible.RecordCount = record.recordCount;
// recordVariables.Add(recordVaraible);
// }
// return recordVariables;
//}
List <Record_Variable> recordVariables = new List <Record_Variable>();
List <Record_Variable> results = new List <Record_Variable>();
List <Guid> recordIds = new List <Guid>();
using (var context = new TSMContext())
{
var filteredRecordsID = listOfRecordId;
//recordVariables = context.Record_Variables.Where(rv => filteredRecordsID.Contains(rv.RecordID)).ToList();
recordVariables = context.Record_Variables.Where(rv => filteredRecordsID.Contains(rv.RecordID) && rv.LanguageID == languageID && (rv.VariableID == yearVariableID || rv.VariableID == startImplementationYear || rv.VariableID == endImplementationYear)).ToList();
recordIds = context.Record_Languages.Where(rl => rl.LanguageID == languageID).Select(rl => rl.ID).Distinct().ToList();
}
int currentYear = DateTime.Now.Year;
int startYear = 1980;
if (recordVariables.Count > 0)
{
startYear = Convert.ToInt32(recordVariables.Where(rv => rv.VariableID == startImplementationYear).OrderBy(rv => rv.Value).FirstOrDefault().Value);
}
var recordYears = new List <RecordYear>();
foreach (var recordId in recordIds)
{
if (recordVariables.Any(rv => rv.RecordID == recordId))
{
var recordYear = new RecordYear();
recordYear.RecordID = recordId;
//Start
var start = recordVariables.Where(rv => rv.RecordID == recordId && rv.VariableID == startImplementationYear && rv.Value != "").FirstOrDefault();
if (start != null)
{
recordYear.StartYear = Convert.ToInt32(start.Value);
}
//End
var end = recordVariables.Where(rv => rv.RecordID == recordId && rv.VariableID == endImplementationYear && rv.Value != "").FirstOrDefault();
if (end != null)
{
recordYear.EndYear = Convert.ToInt32(end.Value);
}
//Year
var year = recordVariables.Where(rv => rv.RecordID == recordId && rv.VariableID == yearVariableID && rv.Value != "").FirstOrDefault();
if (year != null && recordYear.StartYear == 0)
{
recordYear.StartYear = Convert.ToInt32(year.Value);
recordYear.EndYear = recordYear.StartYear + 5;
}
recordYears.Add(recordYear);
}
}
for (int count = startYear; count <= currentYear; count++)
{
var result = new Record_Variable();
result.Value = count.ToString();
result.RecordCount = recordYears.Where(ry => ry.StartYear <= count && ry.EndYear >= count).Count();
results.Add(result);
}
return(results);
}
