public static List <UserSmallInfo> GetMyUsers(string preparerId, bool getActive, int year)
{
using (var db = new WorktripEntities())
{
var links =
db.UserToPreparers
.Include(up => up.Status)
.Where(up => up.Year == year && up.PreparerId == preparerId && (getActive ? up.Status.Name != "Finished" : up.Status.Name == "Finished"))
.ToDictionary(up => up.UserId, up => up);
var customerIds = links.Select(up => up.Key).ToArray();
var customers = db.Users.Where(u => customerIds.Contains(u.Id)).ToList().Select(u => new UserSmallInfo
{
Id = u.Id,
PhoneNumber = u.PhoneNumber,
FirstName = u.FirstName,
MiddleName = u.MiddleName,
LastName = u.LastName,
Email = u.Email,
DOB = u.DOB.HasValue ? u.DOB.Value.ToString("yyyy-MM-dd") : null,
Status = links[u.Id].Status.Name,
Price = links[u.Id].Fee,
Color = links[u.Id].ColorCode,
FirstModifiedString = links[u.Id].FirstModified.HasValue ? DateTime.SpecifyKind(links[u.Id].FirstModified.Value, DateTimeKind.Utc).ToString("s") + "Z" : ""
});
var customersSorted = customers.OrderByDescending(c => c.Color).ThenByDescending(b => b.FirstModified);
var finalCustomersSorted = SortUserColorCodes(customersSorted.ToList());
return(finalCustomersSorted.ToList());
}
}
public static List <Preparers> GetPreparers()
{
using (var db = new WorktripEntities())
{
//var results = db.Users.Include(u => u.Roles).Where(u => u.Roles.Any(r => r.Name == "Preparer")).
// Select();
var results =
from u in db.Users.Include(u => u.Roles)
join up in db.UserToPreparers on u.Id equals up.PreparerId into leftJoin
from link in leftJoin.DefaultIfEmpty()
//join s in db.Status on link.StatusId equals s.Id
where u.Roles.Any(r => r.Name == "Preparer")
group new { u, link } by u.Id into upGroup
select new Preparers
{
Id = upGroup.FirstOrDefault().u.Id,
FirstName = upGroup.FirstOrDefault().u.FirstName,
LastName = upGroup.FirstOrDefault().u.LastName,
CustomersAssigned = upGroup.Count(g => g.link == null ? false : (g.link.Status.Name != "Finished" && g.link.Status.Name != "WaitingCustomerPayment"))
};
return(results.ToList());
}
}
public static bool UpdateUsersToPreparers(Dictionary <string, string> userToPreparer, int year)
{
using (var db = new WorktripEntities())
{
foreach (var kvPair in userToPreparer)
{
var link = db.UserToPreparers.FirstOrDefault(up => up.UserId == kvPair.Key && up.Year == year);
if (link == null)
{
var checkUserFields = UserInfoViewModel.CheckUserFields(kvPair.Key);
link = new UserToPreparer
{
UserId = kvPair.Key,
Year = year,
StatusId = checkUserFields ? 3 : 2
};
db.UserToPreparers.Add(link);
}
link.PreparerId = kvPair.Value;
db.SaveChanges();
}
return(true);
}
}
public static List <AirlineSelectModel> GetAirlines()
{
using (var db = new WorktripEntities())
{
return(db.Airlines.Select(a => new AirlineSelectModel
{
Id = a.Id,
Name = a.Name
}).ToList());
}
}
public static List <UserAction> GetUserActions(int hours)
{
using (var db = new WorktripEntities())
{
var utcNow = DateTime.UtcNow;
var actions = db.UserActionsLogs.Where(a => DbFunctions.DiffHours(a.Timestamp.Value, utcNow) <= hours).ToList();
var userList = actions.Select(a => a.UserId).ToList();
var userInfoDict =
(from u in db.Users
where userList.Contains(u.Id)
join up in db.UserToPreparers on u.Id equals up.UserId into upJoined
from upj in upJoined.DefaultIfEmpty()
join s in db.Status on upj.StatusId equals s.Id into sJoined
from sj in sJoined.DefaultIfEmpty()
group new { u, upj, sj } by upj.UserId into g
select new UserSmallInfo
{
Id = g.FirstOrDefault().u.Id,
PhoneNumber = g.FirstOrDefault().u.PhoneNumber,
FirstName = g.FirstOrDefault().u.FirstName,
MiddleName = g.FirstOrDefault().u.MiddleName,
LastName = g.FirstOrDefault().u.LastName,
Email = g.FirstOrDefault().u.Email,
Status = g.FirstOrDefault().sj.Name == null ? "Waiting for Assignment to Preparer" : g.FirstOrDefault().sj.Name,
Price = g.FirstOrDefault().upj.Fee,
PreparerId = g.FirstOrDefault().upj.PreparerId,
Color = g.FirstOrDefault().upj.ColorCode
}).ToDictionary(u => u.Id, u => u);
var result =
from a in actions
select new UserAction
{
Id = userInfoDict[a.UserId].Id,
PhoneNumber = userInfoDict[a.UserId].PhoneNumber,
FirstName = userInfoDict[a.UserId].FirstName,
MiddleName = userInfoDict[a.UserId].MiddleName,
LastName = userInfoDict[a.UserId].LastName,
Email = userInfoDict[a.UserId].Email,
Status = userInfoDict[a.UserId].Status,
Price = userInfoDict[a.UserId].Price,
PreparerId = userInfoDict[a.UserId].PreparerId,
Color = userInfoDict[a.UserId].Color,
Action = a.Action,
TimeStr = DateTime.SpecifyKind(a.Timestamp.Value, DateTimeKind.Utc).ToString("s") + "Z"
};
return(result.ToList());
}
}
public static List <AirportSelectModel> SearchAirports(string searchTerm)
{
using (var db = new WorktripEntities())
{
var query = db.Airports.Where(a => a.Code.StartsWith(searchTerm) || a.Name.StartsWith(searchTerm)).ToList();
return(query.Select(q => new AirportSelectModel
{
Id = q.Code,
Text = q.Name
}).ToList());
}
}
public static bool ChargePayment(string userId, string paymentNonce, int year)
{
using (var db = new WorktripEntities())
{
var user = db.Users.FirstOrDefault(u => u.Id == userId);
var link = db.UserToPreparers.FirstOrDefault(up => up.Year == year && up.UserId == userId);
if (user == null || link == null || !link.Fee.HasValue)
{
return(false);
}
var request = new TransactionRequest
{
Amount = link.Fee.Value * 1.07M,
PaymentMethodNonce = paymentNonce,
Customer = new CustomerRequest
{
FirstName = user.FirstName,
LastName = user.LastName
},
BillingAddress = new AddressRequest
{
FirstName = user.FirstName,
LastName = user.LastName
},
Options = new TransactionOptionsRequest
{
SubmitForSettlement = true
}
};
Result <Transaction> result = gateway.Transaction.Sale(request);
if (result.IsSuccess())
{
link.StatusId = db.Status.First(s => s.Name == "Finished").Id;
db.SaveChanges();
UserInfoViewModel.UpdateUserActionsLog(userId, "submitted payment");
}
return(result.IsSuccess());
}
}
public static bool UpdateFirstTimeLogin(string userId, bool value)
{
using (var db = new WorktripEntities())
{
var userInfo = db.Users.FirstOrDefault(u => u.Id == userId);
if (userInfo == null)
{
return(false);
}
userInfo.FirstTimeLogin = value;
db.SaveChanges();
}
return(true);
}
public bool SaveTaxInfo(string userId)
{
using (var db = new WorktripEntities())
{
var userInfo = db.Users.FirstOrDefault(u => u.Id == userId);
if (userInfo == null)
{
return(false);
}
if (this.TaxInfos != null)
{
foreach (var kvPair in this.TaxInfos)
{
var taxInfo = UserInfoViewModel.GetOrCreateUserInfo(db, userId, kvPair.Value.Year);
var u = kvPair.Value;
taxInfo.CellphoneBill = u.CellphoneBill;
taxInfo.ClothingFees = u.ClothingFees;
taxInfo.DaysInTrainingOrAway = u.DaysInTrainingOrAway;
taxInfo.DriverLayoverTips = u.DriverLayoverTips;
taxInfo.FlightGearLuggageFees = u.FlightGearLuggageFees;
taxInfo.IdentityFees = u.IdentityFees;
taxInfo.InternetBill = u.InternetBill;
taxInfo.LaundryFees = u.LaundryFees;
taxInfo.TechPurchasesFees = u.TechPurchasesFees;
taxInfo.TotalSpentLayoverTransportation = u.TotalSpentLayoverTransportation;
taxInfo.TransactionalFees = u.TransactionalFees;
taxInfo.UnreimbursedExpenses = u.UnreimbursedExpenses;
taxInfo.DLState = u.DLState;
taxInfo.InternationalLayovers = u.InternationalLayovers;
}
}
db.SaveChanges();
UpdateUserActionsLog(userId, "answered " + this.TaxInfos.First().Value.Year + " tax questions");
}
return(true);
}
public bool SavePersonalInfo(string userId)
{
using (var db = new WorktripEntities())
{
var userInfo = db.Users.FirstOrDefault(u => u.Id == userId);
if (userInfo == null)
{
return(false);
}
userInfo.FirstName = this.FirstName;
userInfo.MiddleName = this.MiddleName;
userInfo.LastName = this.LastName;
userInfo.Email = this.Email;
userInfo.PhoneNumber = this.PhoneNumber;
userInfo.DOB = String.IsNullOrEmpty(this.DOB) ? (DateTime?)null : DateTime.Parse(this.DOB);
userInfo.Address = this.Address;
userInfo.City = this.City;
userInfo.State = this.State;
userInfo.Zip = this.Zip;
userInfo.BankName = this.BankName;
userInfo.BankAccountNumber = this.BankAccountNumber;
userInfo.RoutingNumber = this.RoutingNumber;
if (this.TaxInfos != null)
{
foreach (var kvPair in this.TaxInfos)
{
var taxInfo = UserInfoViewModel.GetOrCreateUserInfo(db, userId, kvPair.Value.Year);
taxInfo.BaseAirportCode = kvPair.Value.BaseAirportCode?.ToUpper();
taxInfo.Airline = kvPair.Value?.Airline;
}
}
db.SaveChanges();
UpdateUserActionsLog(userId, "updated personal info");
}
return(true);
}
public static bool UpdateUserActionsLog(string userId, string action)
{
using (var db = new WorktripEntities())
{
var latestAction = db.UserActionsLogs.Where(a => a.UserId == userId && a.Action == action).OrderByDescending(a => a.Id).FirstOrDefault();
if (latestAction == null || (latestAction != null && (DateTime.UtcNow - latestAction.Timestamp.Value).Minutes > 4))
{
UserActionsLog userAction = new UserActionsLog
{
UserId = userId,
Action = action,
Timestamp = DateTime.UtcNow
};
db.UserActionsLogs.Add(userAction);
db.SaveChanges();
}
}
return(true);
}
public static bool CheckUserFields(string userId)
{
using (var db = new WorktripEntities())
{
var userInfo = db.Users.FirstOrDefault(u => u.Id == userId);
if (userInfo == null)
{
return(false);
}
return(!String.IsNullOrEmpty(userInfo.Address) &&
!String.IsNullOrEmpty(userInfo.State) &&
!String.IsNullOrEmpty(userInfo.City) &&
!String.IsNullOrEmpty(userInfo.Zip) &&
!String.IsNullOrEmpty(userInfo.Email) &&
!String.IsNullOrEmpty(userInfo.FirstName) &&
!String.IsNullOrEmpty(userInfo.LastName) &&
!String.IsNullOrEmpty(userInfo.PhoneNumber) &&
userInfo.DOB.HasValue);
}
}
public static List <UnboundUsers> GetUnboundUsers(int year)
{
using (var db = new WorktripEntities())
{
// Only care about users who had previous infoes filled out, and has current year info filled out, but not assigned yet
var results =
from u in db.Users
join up in db.UserToPreparers on u.Id equals up.UserId into upGroup
from g in upGroup.DefaultIfEmpty()
where u.Roles.Any(r => r.Name == "Customer") && (g.Year == year && (g == null || g.PreparerId == null))
orderby u.SignUpTime
select u;
return(results.ToList().Select(u => new UnboundUsers
{
Id = u.Id,
PhoneNumber = u.PhoneNumber,
FirstName = u.FirstName,
LastName = u.LastName,
SignUpTimeStr = u.SignUpTime.HasValue ? DateTime.SpecifyKind(u.SignUpTime.Value, DateTimeKind.Utc).ToString("s") + "Z" : ""
}).ToList());
}
}
public bool SaveYesNoQuestions(string userId)
{
using (var db = new WorktripEntities())
{
var userInfo = db.Users.FirstOrDefault(u => u.Id == userId);
if (userInfo == null)
{
return(false);
}
if (this.TaxInfos != null)
{
foreach (var kvPair in this.TaxInfos)
{
var taxInfo = UserInfoViewModel.GetOrCreateUserInfo(db, userId, kvPair.Value.Year);
var u = kvPair.Value;
taxInfo.Married = u.Married;
taxInfo.Dependent = u.Dependent;
taxInfo.StudentLoans = u.StudentLoans;
taxInfo.Stocks = u.Stocks;
taxInfo.House = u.House;
taxInfo.HSA = u.HSA;
taxInfo.C1098T = u.C1098T;
taxInfo.C1099R = u.C1099R;
}
}
db.SaveChanges();
UpdateUserActionsLog(userId, "updated yes/no questions");
}
return(true);
}
public static UserMiscTaxInfo GetOrCreateUserInfo(WorktripEntities dbContext, string userId, int taxYear, string promoCode = null, bool notifyAdminsIfExistingCustomer = true)
{
var allTaxInfos = dbContext.UserMiscTaxInfoes.Where(i => i.UserId == userId).ToList();
var taxInfo = allTaxInfos.FirstOrDefault(i => i.TaxYear == taxYear);
bool createdNewTaxInfo = false;
if (taxInfo == null)//New Signup
{
taxInfo = new UserMiscTaxInfo()
{
UserId = userId,
TaxYear = taxYear
};
dbContext.UserMiscTaxInfoes.Add(taxInfo);
dbContext.SaveChanges();
createdNewTaxInfo = true;
}
var upLink = dbContext.UserToPreparers.FirstOrDefault(up => up.Year == taxYear && up.UserId == userId);
var newUser = dbContext.UserToPreparers.FirstOrDefault(up => up.UserId == userId);
if (upLink == null)
{
upLink = new UserToPreparer()
{
UserId = userId,
StatusId = 1,
Year = taxYear,
PromoCode = promoCode
};
if (newUser == null)
{
upLink.ColorCode = "#fc58a1"; //New Signup
}
else
{
upLink.ColorCode = "#58c4fc"; //Existing Signup
}
dbContext.UserToPreparers.Add(upLink);
dbContext.SaveChanges();
}
// Notify admins if this is an existing user (from previous year), and they have not been assigned yet for this year
if (notifyAdminsIfExistingCustomer && allTaxInfos.Count > 0 && createdNewTaxInfo && upLink.PreparerId == null)
{
var smsService = new SmsService();
var preparers = dbContext.Users.Include(u => u.Roles).Where(u => u.Roles.Any(r => r.Name == "Preparer")).ToList();
var user = dbContext.Users.FirstOrDefault(u => u.Id == userId);
foreach (var p in preparers)
{
smsService.SendAsync(new Microsoft.AspNet.Identity.IdentityMessage
{
Destination = p.PhoneNumber,
Body = String.Format("Existing customer {0} ({1}) has began filling out their {2} tax info", user.FirstName + " " + user.LastName, user.PhoneNumber, taxYear)
});
}
}
return(taxInfo);
}
public static List <UserSmallInfo> GetAllUsers(string query, int year)
{
using (var db = new WorktripEntities())
{
var results =
from u in db.Users
join up in db.UserToPreparers on u.Id equals up.UserId into upJoined
from upj in upJoined.DefaultIfEmpty()
join s in db.Status on upj.StatusId equals s.Id into sJoined
from sj in sJoined.DefaultIfEmpty()
where upj.Year == year && u.Roles.Any(r => r.Name == "Customer")
orderby upj.ColorCode descending
select new UserSmallInfo
{
Id = u.Id,
PhoneNumber = u.PhoneNumber,
FirstName = u.FirstName,
MiddleName = u.MiddleName,
LastName = u.LastName,
Email = u.Email,
Status = sj.Name == null ? "Waiting for Assignment to Preparer" : sj.Name,
Price = upj.Fee,
PreparerId = upj.PreparerId,
Color = upj.ColorCode,
FirstModified = upj.FirstModified.HasValue ? upj.FirstModified : null,
FirstModifiedString = ""
};
var resultsList = results.ToList();
foreach (var r in resultsList)
{
if (r.FirstModified != null)
{
r.FirstModifiedString = DateTime.SpecifyKind(r.FirstModified.Value, DateTimeKind.Utc).ToString("s") + "Z";
}
}
var resultsSorted = resultsList.OrderByDescending(c => c.Color).ThenByDescending(b => b.FirstModified.HasValue).ThenByDescending(a => a.FirstModified);
if (!String.IsNullOrEmpty(query))
{
resultsSorted = resultsSorted.Where(u =>
((u.FirstName != null && u.LastName != null && (u.FirstName.ToLower() + " " + u.LastName.ToLower()).StartsWith(query.ToLower())) ||
u.LastName != null && (u.LastName.ToLower().StartsWith(query.ToLower())) ||
(u.FirstName != null && u.LastName != null && u.MiddleName != null && (u.FirstName.ToLower() + " " + u.MiddleName.ToLower() + " " + u.LastName.ToLower()).StartsWith(query.ToLower())) ||
(u.Email != null && u.Email.ToLower().StartsWith(query.ToLower()))
)).OrderBy(l => l.LastName);
}
var preparers = Preparers.GetPreparers().ToDictionary(p => p.Id, p => p);
var retrieved = SortUserColorCodes(resultsSorted.ToList());
foreach (var r in retrieved)
{
if (!String.IsNullOrEmpty(r.PreparerId) && preparers.ContainsKey(r.PreparerId))
{
r.Preparer = preparers[r.PreparerId].FirstName + " " + preparers[r.PreparerId].LastName;
}
}
return(retrieved);
}
}
public static bool SubmitQuestion(string question, int taxYear, string userId)
{
using (var db = new WorktripEntities())
{
var userInfo = db.Users.FirstOrDefault(u => u.Id == userId);
if (userInfo == null)
{
return(false);
}
var newQuestion = new UserQuestion()
{
AskedBy = userId,
Question = question,
TaxYear = taxYear,
Date = DateTime.UtcNow
};
db.UserQuestions.Add(newQuestion);
//try to text the tax preparer this question
var userToPreparer = db.UserToPreparers.Where(u => u.UserId == userId && u.Year == taxYear).FirstOrDefault();
var smsService = new SmsService();
var textMessage =
string.Format("{0} asked a question for their {1} taxes: {2}",
userInfo.FirstName + " " + userInfo.LastName + ", " + userInfo.PhoneNumber,
taxYear,
question);
if (userToPreparer != null)
{
var preparerInfo = db.Users.Where(u => u.Id == userToPreparer.PreparerId).FirstOrDefault();
if (preparerInfo != null)
{
smsService.SendAsync(new Microsoft.AspNet.Identity.IdentityMessage
{
Destination = preparerInfo.PhoneNumber,
Body = textMessage
});
}
}
else
{
//if this user hasn't been assigned to any preparers yet, text all preparers
var preparers = db.Users.Include(u => u.Roles).Where(u => u.Roles.Any(r => r.Name == "Preparer")).ToList();
foreach (var p in preparers)
{
smsService.Send(new IdentityMessage
{
Destination = p.PhoneNumber,
Body = textMessage
});
}
}
db.SaveChanges();
}
return(true);
}
public static UserInfoViewModel GetUserInfo(string userId)
{
using (var db = new WorktripEntities())
{
var userInfo = db.Users.FirstOrDefault(u => u.Id == userId);
if (userInfo == null)
{
return(null);
}
return(new UserInfoViewModel()
{
Id = userInfo.Id,
FirstName = userInfo.FirstName,
MiddleName = userInfo.MiddleName,
LastName = userInfo.LastName,
Email = userInfo.Email,
PhoneNumber = userInfo.PhoneNumber,
DOB = userInfo.DOB.HasValue ? userInfo.DOB.Value.ToString("yyyy-MM-dd") : null,
Address = userInfo.Address,
City = userInfo.City,
State = userInfo.State,
Zip = userInfo.Zip,
FirstTimeLogin = userInfo.FirstTimeLogin,
BankName = userInfo.BankName,
BankAccountNumber = userInfo.BankAccountNumber,
RoutingNumber = userInfo.RoutingNumber,
TaxInfos = db.UserMiscTaxInfoes.Where(d => d.UserId == userId).Select(d => new TaxInfo
{
Year = d.TaxYear,
BaseAirportCode = d.BaseAirportCode,
DaysInTrainingOrAway = d.DaysInTrainingOrAway,
DriverLayoverTips = d.DriverLayoverTips,
TotalSpentLayoverTransportation = d.TotalSpentLayoverTransportation,
TransactionalFees = d.TransactionalFees,
ClothingFees = d.ClothingFees,
LaundryFees = d.LaundryFees,
FlightGearLuggageFees = d.FlightGearLuggageFees,
TechPurchasesFees = d.TechPurchasesFees,
CellphoneBill = d.CellphoneBill,
InternetBill = d.InternetBill,
IdentityFees = d.IdentityFees,
UnreimbursedExpenses = d.UnreimbursedExpenses,
Airline = d.Airline,
PreparerNotes = d.PreparerNotes,
TaxReturn = d.TaxReturn,
PerDiemsTotal = d.LayoversPerDiem,
DLState = d.DLState,
InternationalLayovers = d.InternationalLayovers,
Married = d.Married,
Dependent = d.Dependent,
StudentLoans = d.StudentLoans,
Stocks = d.Stocks,
House = d.House,
HSA = d.HSA,
C1098T = d.C1098T,
C1099R = d.C1099R
}).ToDictionary(t => t.Year.ToString(), t => t),
Questions = db.UserQuestions.Include(q => q.User1).Where(q => q.AskedBy == userId).Select(q => new Question
{
TaxYear = q.TaxYear,
AnswerText = q.Answer,
AnsweredBy = q.User1.FirstName,
Date = q.Date,
QuestionText = q.Question
}).GroupBy(q => q.TaxYear).ToDictionary(q => q.First().TaxYear.ToString(), q => q.ToList()),
Statuses = db.UserToPreparers.Include(up => up.Status).Where(up => up.UserId == userId).Select(up => new TaxStatus
{
Year = up.Year,
PreparerId = up.PreparerId,
Status = up.Status.Name,
Fee = up.Fee,
PromoCode = up.PromoCode
}).ToDictionary(up => up.Year.ToString(), up => up)
});
}
}
