private static void PersistRows(List <Models.ColoradoBusiness> rows, int batchSize, ref int recordsAdded)
{
using (var context = new BusinessIncentivesContext())
{
try
{
context.ColoradoBusinesses.AddRange(rows);
context.SaveChanges();
}
catch
{
if (rows.Count == 1)
{
recordsAdded--;
return;
}
for (int i = 0; i < rows.Count; i++)
{
var temp = new List <Models.ColoradoBusiness>();
temp.Add(rows[i]);
PersistRows(temp, 1, ref recordsAdded);
}
}
}
}
public List <ProgramView> GetAllPrograms()
{
using (var context = new BusinessIncentivesContext())
{
return(context.ProgramViews.Take(300).ToList());
}
}
public List <ColoradoBusiness> GetAllBusinesses()
{
using (var context = new BusinessIncentivesContext())
{
return(context.ColoradoBusinesses.ToList());
}
}
private static void PersistPrograms(List <Models.Program> programs, int batchSize)
{
using (var context = new BusinessIncentivesContext())
{
context.Programs.AddRange(programs);
context.SaveChanges();
}
}
public User GetUser(int Id)
{
using (var context = new BusinessIncentivesContext())
{
var result = context.Users.Where(o => o.ID == Id).Single();
return(result);
}
}
public UserCreationResult CreateUser(UserModel userModel, byte[] hash, byte[] salt)
{
var result = new UserCreationResult();
if (userModel == null)
{
throw new ArgumentNullException("userModel");
}
if (hash.Length == 0)
{
throw new ArgumentException("Hash can't be empty");
}
try
{
using (var context = new BusinessIncentivesContext())
{
IQueryable <User> existingUser = context.Users.Where(o => o.Username == userModel.Username || o.Email == userModel.Email);
if (existingUser.Count() > 0)
{
result.IsSuccess = false;
result.ErrorMessage = "User already exists!";
}
else
{
var user = new User()
{
Username = userModel.Username,
Password = hash,
Email = userModel.Email,
FirstName = userModel.FirstName,
LastName = userModel.LastName,
Salt = salt,
PermissionLevelID = (short)PermissionLevels.OpportunityCreator
};
user = context.Users.Add(user);
context.SaveChanges();
result.UserCreated = user;
result.IsSuccess = true;
}
}
}
catch (Exception ex)
{
result.IsSuccess = false;
result.ErrorMessage = ex.ToString();
}
return(result);
}
public short?GetUserPermission(string username)
{
using (var context = new BusinessIncentivesContext())
{
if (username == null || Session.GetCurrent().GetUserName() != username)
{
return(short.MaxValue);
}
var result = context.Users.Where(o => o.Username == username).Single();
return(result.PermissionLevelID);
}
}
public string GetUserFirstName(string username)
{
using (var context = new BusinessIncentivesContext())
{
if (username == null || Session.GetCurrent().GetUserName() != username)
{
return(null);
}
var result = context.Users.Where(o => o.Username == username).Single();
return(result.FirstName);
}
}
public ColoradoBusiness GetBusinessByName(string businessName)
{
using (var context = new BusinessIncentivesContext())
{
IQueryable <ColoradoBusiness> query;
// These are constructing the query...
query = context.ColoradoBusinesses.Where(o => o.EntityName.Equals(businessName));
// This triggers the conversion to SQL + roundtrip to the DB, using the constructed query
var result = query.Single();
return(result);
}
}
public List <ColoradoBusiness> GetBusinesses(string EntityId, string EntityName, string PrincipalAddress1, string PrincipalAddress2, string PrincipalCity, string PrincipalZipCode)
{
if (string.IsNullOrWhiteSpace(EntityName) && string.IsNullOrWhiteSpace(PrincipalAddress1))
{
return(GetAllBusinesses());
}
else
{
using (var context = new BusinessIncentivesContext())
{
IQueryable <ColoradoBusiness> query;
// These are constructing the query...
query = context.ColoradoBusinesses.Where(o => string.IsNullOrEmpty(EntityName) || o.EntityName.ToLower().Contains(EntityName.ToLower()));
if (!string.IsNullOrEmpty(PrincipalAddress1))
{
query = query.Where(o => string.IsNullOrEmpty(PrincipalAddress1) || o.PrincipalAddress1.ToLower().Contains(PrincipalAddress1.ToLower()));
}
if (!string.IsNullOrEmpty(PrincipalAddress2))
{
query = query.Where(o => string.IsNullOrEmpty(PrincipalAddress2) || o.PrincipalAddress2.ToLower().Contains(PrincipalAddress2.ToLower()));
}
if (!string.IsNullOrEmpty(PrincipalCity))
{
query = query.Where(o => string.IsNullOrEmpty(PrincipalCity) || o.PrincipalCity.ToLower().Contains(PrincipalCity.ToLower()));
}
if (!string.IsNullOrEmpty(PrincipalZipCode))
{
query = query.Where(o => string.IsNullOrEmpty(PrincipalZipCode) || o.PrincipalZipCode.ToLower().Contains(PrincipalZipCode.ToLower()));
}
if (query.Count() < 1)
{
return(null);
}
// This triggers the conversion to SQL + roundtrip to the DB, using the constructed query
var result = query.ToList();
return(result);
}
}
}
public User GetUser(string username, byte[] hash)
{
if (string.IsNullOrWhiteSpace(username) || hash.Length == 0)
{
return(null);
}
else
{
using (var context = new BusinessIncentivesContext())
{
IQueryable <User> query = context.Users.Where(o => o.Username == username && o.Password == hash);
// Returns either the single user object or null (which is a default value)
var result = query.SingleOrDefault();
return(result);
}
}
}
/// <summary>
/// Searches by keywords within the following fields: ProgramTitle, Objectives
/// </summary>
/// <param name="keywords"></param>
/// <returns></returns>
public List <ProgramView> GetProgramsByKeywords(List <string> keywords)
{
if (keywords == null || keywords.Count == 0)
{
return(GetAllPrograms());
}
else
{
using (var context = new BusinessIncentivesContext())
{
IQueryable <ProgramView> query;
query = context.ProgramViews.Take(300).Where(p => keywords.Any(kw => p.ProgramTitle.Contains(kw) || p.Objectives_050.Contains(kw) || p.BeneficiaryEligibility_082.Contains(kw)));
var result = query.ToList();
return(result);
}
}
}
public List <string> GetBusinessNames(string searchTerm)
{
if (string.IsNullOrWhiteSpace(searchTerm))
{
return(GetAllBusinesses().Select(b => b.EntityName).ToList());;
}
else
{
using (var context = new BusinessIncentivesContext())
{
IQueryable <ColoradoBusiness> query;
// These are constructing the query...
query = context.ColoradoBusinesses.Where(o => string.IsNullOrEmpty(searchTerm) || o.EntityName.ToLower().Contains(searchTerm.ToLower()));
// This triggers the conversion to SQL + roundtrip to the DB, using the constructed query
var result = query.Select(b => b.EntityName).Take(200).ToList();
return(result);
}
}
}
public byte[] GetSalt(string username)
{
if (string.IsNullOrWhiteSpace(username))
{
return(null);
}
else
{
using (var context = new BusinessIncentivesContext())
{
IQueryable <User> query = context.Users.Where(o => o.Username == username);
if (query.Count() == 0)
{
return(null);
}
var result = query.Select(b => b.Salt).Single();
return(result);
}
}
}
public long?AddCustomOpportunity(CustomOpportunity customOpportunity)
{
using (var context = new BusinessIncentivesContext())
{
IQueryable <CustomOpportunity> query = context.CustomOpportunities.Where(o => o.Name == customOpportunity.Name);
var result = query?.ToList();
if (result?.Count > 0)
{
return(null);
}
context.CustomOpportunities.Add(customOpportunity);
context.SaveChanges();
query = context.CustomOpportunities.Where(o => o.Name == customOpportunity.Name);
result = query?.ToList();
return(result[0]?.ID);
}
}
public FieldCodedValueRepository()
{
_context = new BusinessIncentivesContext();
}
public OpportunityRepository()
{
_context = new BusinessIncentivesContext();
}
public DataLinkResult LinkData()
{
Console.CursorVisible = false;
Console.WriteLine($"Creating cross reference data between Opportunities and Programs...");
// Get the list of Opportunity record ID's to process
var opportunityIDs = new List <string>();
using (var context = new BusinessIncentivesContext())
{
// We get the IDs from OpportunityView instead of Opportunity,
// which filters the opportunities still available only...
opportunityIDs = context.OpportunityViews.Select(o => o.OpportunityID).ToList();
}
// Loop through 100 Opportunity records, and for
// each get and attach the related programs
var batchSize = 100;
var counter = 0;
var cursorTop = Console.CursorTop + 1;
using (var context = new BusinessIncentivesContext())
{
foreach (var opportunityID in opportunityIDs)
{
var opportunity = context.Opportunities.Single(o => o.OpportunityID == opportunityID);
var relatedPrograms = context.Programs.Where(p => opportunity.CFDANumbers.Contains(p.ProgramNumber));
string message = string.Empty;
var areProgramsAlreadyAttached = opportunity.Programs != null && opportunity.Programs.Count() > 0;
if (areProgramsAlreadyAttached)
{
message = $"Opportunity ID {opportunity.OpportunityID} has its related Programs already attached";
}
else
{
opportunity.Programs = relatedPrograms.ToList();
var programCount = relatedPrograms.Count();
message = $"Opportunity ID {opportunity.OpportunityID} has {programCount} related Program{(programCount > 1 ? "(s)" : "")}";
}
Console.WriteLine(string.Join(" ", Enumerable.Range(0, 50).Select(i => " ")));
Console.SetCursorPosition(0, cursorTop + 1);
Console.WriteLine(message);
counter++;
if (counter % batchSize == 0 || counter == opportunityIDs.Count)
{
// Persist batch
context.SaveChanges();
Console.SetCursorPosition(0, cursorTop);
Console.WriteLine($"Count of Opportunity records processed: {counter} / {opportunityIDs.Count}");
}
}
Console.SetCursorPosition(0, cursorTop + 1);
}
return(new DataLinkResult
{
CountOfRecordsLinked = counter,
IsSuccess = true
});
}
