/// <summary>
/// Get Country by code
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
public static Country GetCountryByCode(string code)
{
using (var context = new TSMContext())
{
return(context.Countries.Where(c => c.ISOCode == code).FirstOrDefault());
}
}
/// <summary>
/// Check if email exists or not
/// </summary>
/// <param name="email"></param>
/// <returns></returns>
public static bool IsEmailExists(string email)
{
using (var context = new TSMContext())
{
return(context.Users.Where(u => u.Email == email && u.IsDeleted == false).Count() > 0);
}
}
/// <summary>
/// Get Parent ID by Nomenclature Code and Sector Type
/// </summary>
/// <param name="nomenclatureCode"></param>
/// <param name="SectorTypes"></param>
/// <returns></returns>
public static List <TSMSector> GetParentID(string nomenclatureCode, string SectorTypes)
{
List <TSMSector> lstsmSector = new List <TSMSector>();
string nomenclatureType = string.Empty;
string sectortype = string.Empty;
if (nomenclatureCode == "1")
{
nomenclatureType = NomenclatureType.ITC.ToString();
}
else if (nomenclatureCode == "2")
{
nomenclatureType = NomenclatureType.International.ToString();
}
if (SectorTypes == "1")
{
sectortype = SectorType.Product.ToString();
}
else if (SectorTypes == "2")
{
sectortype = SectorType.Service.ToString();
}
using (var context = new TSMContext())
{
lstsmSector = context.TSMSector.Where(s => s.NomenclatureType.ToString() == nomenclatureType && s.Type.ToString() == sectortype && s.ParentID == null && s.IsActive == true && s.IsDeleted == false).ToList();
}
return(lstsmSector);
}
public static List <Record_Variable> GetRecordInYear1(List <Guid> listRecord, Guid languageID)
{
Guid yearVariableID = new Guid(AppSettingsUtility.GetString(AppSettingsKeys.YearVariableID));
using (var context = new TSMContext())
{
//var filteredRecords = GetRecordList(languageID, selectedRegions, selectedCountries, selectedSectors, selectedDocumentTypes, documentTypeVariableID);
var filteredRecordsID = listRecord;
// 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);
}
}
/// <summary>
/// Get User by ID
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static User GetUser(Guid id)
{
using (var context = new TSMContext())
{
return(context.Users.Where(u => u.ID == id).FirstOrDefault());
}
}
/// <summary>
/// Get content list
/// </summary>
/// <returns></returns>
public static List <Content> GetContents()
{
using (var context = new TSMContext())
{
return(context.Contents.Include("Content_Languages").Where(c => c.IsActive).OrderBy(c => c.Type.ToString()).ToList());
}
}
public static List <Variable_Language> GetVariableLanguage()
{
using (var context = new TSMContext())
{
return(context.Variable_Languages.ToList());
}
}
/// <summary>
/// Get Record Language By Search Param
/// </summary>
/// <param name="searchParam"></param>
/// <returns></returns>
public static List <Record_Language> GetRecordLanguages(SearchAttributes searchParam, Guid langID)
{
//IEnumerable<Record> records;
//List<Record> lstRecord = new List<Record>();
using (var context = new TSMContext())
{
var lstrecordLanguage = context.Record_Languages.Include("Record").Where(r => r.Record.IsDeleted == false && r.Record.IsActive == true && r.LanguageID == langID);
if (!string.IsNullOrEmpty(searchParam.SearchText))
{
string searchText = searchParam.SearchText.ToLower();
lstrecordLanguage = lstrecordLanguage.Where(rl => rl.Name.ToLower().Contains(searchText));
}
switch (searchParam.SortColumnIndex)
{
case 1:
lstrecordLanguage = searchParam.SortDirection == SortDirection.Ascending ? lstrecordLanguage.OrderBy(r => r.Name) : lstrecordLanguage.OrderByDescending(r => r.Name);
break;
}
searchParam.TotalRecordCount = lstrecordLanguage.Count();
searchParam.TotalPages = (int)Math.Ceiling((double)searchParam.TotalRecordCount / searchParam.RecordsPerPage);
return(lstrecordLanguage.Skip((searchParam.CurrentPageNumber - 1) * searchParam.RecordsPerPage)
.Take(searchParam.RecordsPerPage).ToList());
}
}
/// <summary>
/// Save XML Data for record download
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public static bool SaveXMLToData(ExportRecordXML xml)
{
try
{
using (var context = new TSMContext())
{
//var xmlRecordID = context.ExportRecordXML.Select(a => a.ID).FirstOrDefault();
//context.ExportRecordXML.D.Delete(w => w.ID == xmlRecordID);
var all = from c in context.ExportRecordXML select c;
context.ExportRecordXML.RemoveRange(all);
//context.Entry(xml).State = EntityState.Deleted;
context.ExportRecordXML.Add(xml);
//Save to DB
context.SaveChanges();
return(true);
}
}
catch (Exception)
{
return(false);
}
}
/// <summary>
/// Get Language
/// </summary>
/// <param name="ID"></param>
/// <returns></returns>
public static Language GetLanguage(Guid id)
{
using (var context = new TSMContext())
{
return(context.Languages.Where(l => l.ID == id).FirstOrDefault());
}
}
/// <summary>
/// Get Languages
/// </summary>
/// <returns></returns>
public static List <Language> GetLanguages()
{
using (var context = new TSMContext())
{
return(context.Languages.Where(l => l.IsActive).OrderBy(l => l.Name).ToList());
}
}
public static string GetRegionNamesByRegionName(Guid languageId, List <Region> regions, Country country)
{
using (var context = new TSMContext())
{
var tmpCountry = context.Countries.Include("Regions").Where(c => c.ID == country.ID).FirstOrDefault();
var selectedRegion = new List <Region>();
if (tmpCountry != null)
{
foreach (var region in tmpCountry.Regions)
{
selectedRegion.Add(region);
}
}
List <Region_Language> regionLanguage = context.Region_Languages.ToList();
var regionLanguageList = (from rl in regionLanguage join r in selectedRegion on rl.ID equals r.ID where r.Type == TSM.Entity.RegionType.Geographical && rl.LanguageID == languageId select rl.Name).ToList();
var fullRegionLanguageList = (from rl in regionLanguage join r in selectedRegion on rl.ID equals r.ID where rl.LanguageID == languageId select rl.Name).ToList();
string regionNames = "";
if (regionLanguageList.Count > 0)
{
regionNames = regionLanguageList[0].ToString();
}
return(regionNames);
}
}
/// <summary>
/// Get Countries with record
/// </summary>
/// <param name="languageID"></param>
/// <returns></returns>
public static List <Country_Language> GetCountriesWithRecord(Guid languageID, List <Guid> listRegion, List <Guid> listCountry)
{
List <Country> countries = new List <Country>();
//List<Country> countries2 = new List<Country>();
using (var context = new TSMContext())
{
countries = context.Countries.Include("Country_Languages").Include("Records").Include("Regions").Where(c => c.IsActive == true && c.Records.Count > 0).ToList();
}
if (listRegion.Count > 0 && listRegion != null)
{
try
{
//countries2 = countries.Where(c => c.Regions.Any(r => listRegion.Any(sr => sr == r.ID))).ToList();
countries = (from c in countries
where c.Regions.Any(region => listRegion.Any(sr => sr == region.ID))
select c).ToList();
}
catch
{ }
}
if (listCountry.Count > 0 && listCountry != null)
{
try
{
countries = countries.Where(c => listCountry.Any(l => c.ID == l)).ToList();
}
catch { }
}
List <Country_Language> countryLanguages = GetCountryLanguages(languageID, countries);
return(countryLanguages);
}
/// <summary>
/// Get Countries by region
/// </summary>
/// <param name="regionID"></param>
/// <param name="languageID"></param>
/// <returns></returns>
public static List <Country_Language> GetCountriesByRegion(Guid regionID, Guid languageID)
{
List <Country> countries = new List <Country>();
List <Country_Language> countryLanguages = new List <Country_Language>();
var cacheProvider = CacheFactory.Get();
if (!cacheProvider.Exists(CacheKeys.CACHE_COUNTRIES))
{
using (var context = new TSMContext())
{
countries = context.Countries.Include("Country_Languages").Include("Regions").Where(c => c.IsActive && !c.IsDeleted.Value).ToList();
}
cacheProvider.Add <List <Country> >(countries, CacheKeys.CACHE_COUNTRIES, SiteConstants.CacheDuration);
}
else
{
countries = cacheProvider.Get <List <Country> >(CacheKeys.CACHE_COUNTRIES);
}
if (regionID == null)
{
//countries = countries.Where(c => c.Regions.Any(r => r.ID == regionID)).ToList();
countryLanguages = GetCountryLanguages(languageID, countries);
}
else
{
//Filter by region
countries = countries.Where(c => c.Regions.Any(r => r.ID == regionID)).ToList();
countryLanguages = GetCountryLanguages(languageID, countries);
}
return(countryLanguages);
}
/// <summary>
/// Get content list
/// </summary>
/// <returns></returns>
public static List <Content> GetContentsbyType(ContentType Type)
{
using (var context = new TSMContext())
{
return(context.Contents.Include("Content_Languages").Where(c => c.IsActive).Where(c => c.Type == Type).ToList());
}
}
/// <summary>
/// Get Records By Search Param
/// </summary>
/// <param name="searchParam"></param>
/// <returns></returns>
public static List <Record> GetRecords(SearchAttributes searchParam, Guid langID)
{
IEnumerable <Record> records;
List <Record> lstRecord = new List <Record>();
using (var context = new TSMContext())
{
records = context.Records.Include("Record_Languages").Where(r => r.IsDeleted == false);
if (!string.IsNullOrEmpty(searchParam.SearchText))
{
string searchText = searchParam.SearchText.ToLower();
records = records.Where(rl => rl.Record_Languages[0].Name.ToLower().Contains(searchText));
}
switch (searchParam.SortColumnIndex)
{
case 1:
records = searchParam.SortDirection == SortDirection.Ascending ? records.OrderBy(r => r.Record_Languages[0].Name) : records.OrderByDescending(r => r.Record_Languages[0].Name);
break;
}
searchParam.TotalRecordCount = records.Count();
searchParam.TotalPages = (int)Math.Ceiling((double)searchParam.TotalRecordCount / searchParam.RecordsPerPage);
return(records.Skip((searchParam.CurrentPageNumber - 1) * searchParam.RecordsPerPage)
.Take(searchParam.RecordsPerPage).ToList());
}
}
/// <summary>
/// Get Content detail by id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static Content GetContent(Guid id)
{
using (var context = new TSMContext())
{
return(context.Contents.Include("Content_Languages").Where(c => c.ID == id).FirstOrDefault());
}
}
/// <summary>
/// Get Variable By Search Param and Language
/// </summary>
/// <param name="searchParam"></param>
/// <param name="LanguageId"></param>
/// <returns></returns>
public static List <Variable> GetVariables(SearchAttributes searchParam, Guid LanguageId)
{
List <Variable> lstVariables = new List <Variable>();
using (var context = new TSMContext())
{
lstVariables = context.Variables.Include("Variable_Languages").Where(c => c.IsDeleted == false).ToList();
if (!string.IsNullOrEmpty(searchParam.SearchText))
{
string searchText = searchParam.SearchText.ToLower();
lstVariables = lstVariables.Where(u => (u.Variable_Languages[0].Name.ToLower().Contains(searchText)) || (u.Variable_Languages[0].DisplayName.ToLower().Contains(searchText))).ToList();
}
switch (searchParam.SortColumnIndex)
{
case 1:
lstVariables = searchParam.SortDirection == SortDirection.Ascending ? lstVariables.OrderBy(u => u.Variable_Languages[0].Name).ToList() : lstVariables.OrderByDescending(u => u.Variable_Languages[0].Name).ToList();
break;
case 2:
lstVariables = searchParam.SortDirection == SortDirection.Ascending ? lstVariables.OrderBy(u => u.Variable_Languages[0].DisplayName).ToList() : lstVariables.OrderByDescending(u => u.Variable_Languages[0].DisplayName).ToList();
break;
}
searchParam.TotalRecordCount = lstVariables.Count();
searchParam.TotalPages = (int)Math.Ceiling((double)searchParam.TotalRecordCount / searchParam.RecordsPerPage);
return(lstVariables.Skip((searchParam.CurrentPageNumber - 1) * searchParam.RecordsPerPage)
.Take(searchParam.RecordsPerPage).ToList());
}
}
/// <summary>
/// Get Content detail by url
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static Content GetContentbyURL(string url)
{
using (var context = new TSMContext())
{
return(context.Contents.Where(c => c.URL == url).FirstOrDefault());
}
}
/// <summary>
/// Get Variables by language
/// </summary>
/// <param name="languageID"></param>
/// <returns></returns>
public static List <Variable_Language> GetVariables(Guid languageID)
{
List <Variable> variables = new List <Variable>();
List <Variable_Language> variableLanguages = new List <Variable_Language>();
var cacheProvider = CacheFactory.Get();
if (!cacheProvider.Exists(CacheKeys.CACHE_VARIABLES))
{
using (var context = new TSMContext())
{
var query = context.Variables.Include("Variable_Languages").Include("Choices").Where(c => c.IsActive && !c.IsDeleted.Value).OrderBy(v => v.DisplayOrder);
variables = query.ToList();
}
//Populate Choices
foreach (var variable in variables)
{
variable.Choices = new List <Choice>();
if (variable.Type == VariableType.MultipleChoice || variable.Type == VariableType.SingleChoice)
{
variable.Choices = GetChoices(variable.ID, languageID);
}
}
cacheProvider.Add <List <Variable> >(variables, CacheKeys.CACHE_VARIABLES, SiteConstants.CacheDuration);
}
else
{
variables = cacheProvider.Get <List <Variable> >(CacheKeys.CACHE_VARIABLES);
}
variableLanguages = GetVariableLanguages(languageID, variables);
return(variableLanguages);
}
private static Guid GetYearID()
{
using (var context = new TSMContext())
{
return(context.Variable_Languages.Where(vl => vl.Name == "Year").FirstOrDefault().ID);
}
}
/// <summary>
/// Get Choice options
/// </summary>
/// <param name="languageID"></param>
/// <param name="variableID"></param>
/// <returns></returns>
public static List <Choice_Language> GetChoiceOptions(Guid languageID, Guid variableID)
{
using (var context = new TSMContext())
{
return(context.Choice_Languages.Include("Choice").Where(cl => cl.Choice.VariableID == variableID && cl.LanguageID == languageID).OrderBy(cl => cl.Name).ToList());
}
}
public static List <Data> RecordSortedListGroupWise()
{
using (var context = new TSMContext())
{
List <Data> listRecName = new List <Data>();
var result = from rv in context.Record_Variables
join rl in context.Record_Languages
on rv.RecordID equals rl.ID
select new
{
Name = rl.Name,
Year = rv.Value
};
var result1 = from rv in result
group rv by new
{
rv.Year,
rv.Name
} into gcs
select new Data
{
Name = "",
Year = gcs.Key.Year,
Count = gcs.ToList().Count
};
var returnResultList = result1.ToList();
return(returnResultList);
}
}
/// <summary>
/// Get Sector by ID
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static Sector GetSector(Guid id)
{
using (var context = new TSMContext())
{
return(context.Sectors.Include("Sector_Languages").Where(u => u.ID == id && u.IsDeleted == false && u.IsActive == true).FirstOrDefault());
}
}
/// <summary>
/// Get User By Email
/// </summary>
/// <param name="email"></param>
/// <returns></returns>
public static User GetUser(string email)
{
using (var context = new TSMContext())
{
return(context.Users.Where(u => u.Email == email).FirstOrDefault());
}
}
public static Sector Save(Sector sector)
{
using (var context = new TSMContext())
{
if (context.Sectors.Where(v => v.ID == sector.ID).Count() > 0)
{
var sector_language = context.Sector_Languages.Where(sl => sl.ID == sector.ID).FirstOrDefault();
if (sector_language != null)
{
context.Sector_Languages.Remove(sector_language);
}
context.Sector_Languages.AddRange(sector.Sector_Languages);
context.Sectors.Attach(sector);
context.Entry(sector).State = EntityState.Modified;
}
else
{
context.Sectors.Add(sector);
}
//Save to DB
context.SaveChanges();
return(sector);
}
}
/// <summary>
/// Get Users
/// </summary>
/// <param name="searchParam"></param>
/// <returns></returns>
public static List <User> GetUsers(SearchAttributes searchParam)
{
IEnumerable <User> users;
using (var context = new TSMContext())
{
users = context.Users.Where(u => !u.IsDeleted.Value);
//users = context.Users;
if (!string.IsNullOrEmpty(searchParam.SearchText))
{
string searchText = searchParam.SearchText.ToLower();
users = users.Where(u => u.Email.ToLower().Contains(searchText) || u.FirstName.ToLower().Contains(searchText) || u.LastName.ToLower().Contains(searchText));
}
switch (searchParam.SortColumnIndex)
{
case 2:
users = searchParam.SortDirection == SortDirection.Ascending ? users.OrderBy(u => u.Email) : users.OrderByDescending(u => u.Email);
break;
case 1:
users = searchParam.SortDirection == SortDirection.Ascending ? users.OrderBy(u => u.FirstName).ThenBy(u => u.LastName) : users.OrderByDescending(u => u.FirstName).ThenByDescending(u => u.LastName);
break;
}
searchParam.TotalRecordCount = users.Count();
searchParam.TotalPages = (int)Math.Ceiling((double)searchParam.TotalRecordCount / searchParam.RecordsPerPage);
//return users.ToList();
return(users.Skip((searchParam.CurrentPageNumber - 1) * searchParam.RecordsPerPage)
.Take(searchParam.RecordsPerPage).ToList());
}
}
/// <summary>
/// Get ContentLanguage detail by id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static Content_Language GetContentLanguage(Guid id)
{
using (var context = new TSMContext())
{
return(context.Content_Languages.Where(c => c.ID == id).FirstOrDefault());
}
}
/// <summary>
/// Get Sector by ID
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static TSMSector GetSector(Guid id)
{
using (var context = new TSMContext())
{
return(context.TSMSector.Where(u => u.ID == id && u.IsDeleted == false).FirstOrDefault());
}
}
/// <summary>
/// Get Countries Record
/// </summary>
/// <param name="countryID"></param>
/// <returns></returns>
public static Country GetCountriesRecord(Guid countryID)
{
List <Country> countries = new List <Country>();
using (var context = new TSMContext())
{
countries = context.Countries.Include("Country_Languages").Include("Records").Where(x => x.IsActive == true).ToList();
FilterCountryRecords(countries, false);
var records = context.Record_Languages.ToList();
foreach (var item in countries)
{
foreach (var rec in item.Records)
{
if (rec.Record_Languages == null)
{
rec.Record_Languages = new List <Record_Language>();
}
if (records.Where(x => x.ID == rec.ID).Count() == 0)
{
continue;
}
rec.Record_Languages = records.Where(x => x.ID == rec.ID).ToList();
}
}
return(countries.Where(x => x.ID == countryID).FirstOrDefault());
}
}