/// <summary>
/// 检查所有项目
/// </summary>
/// <returns></returns>
public static void CheckExpire()
{
using (Entities db = new Entities())
{
List <int> nidList = new List <int>();
List <int> sidList = new List <int>();
nidList = db.ht_news.Where(p =>
(p.status == 0 || p.status == 1)
&&
((p.validity_unit == "月" && DbFunctions.AddMonths(p.add_time, p.validity_num.Value) < DateTime.Now) ||
(p.validity_unit == "天" && DbFunctions.AddDays(p.add_time, p.validity_num.Value) < DateTime.Now))
).Select(p => p.id).ToList();
db.ht_news.Where(p => nidList.Contains(p.id)).Update(p => new ht_news {
status = 3
});
//筛选出货源
nidList = db.ht_news.Where(p => nidList.Contains(p.id) && p.cateid == 1).Select(p => p.id).ToList();;
if (nidList.Count > 0)
{
sidList = db.ht_news_subscribe_relation.Where(p => nidList.Contains(p.news_id))
.Select(p => p.subscribe_id).ToList();
}
if (sidList.Count > 0)
{
db.ht_news_subscribe.Where(p => sidList.Contains(p.id)).Update(p => new ht_news_subscribe
{
ncount = p.ncount - 1
});
db.ht_news_subscribe_relation.Where(p => nidList.Contains(p.news_id)).Delete();
}
}
}
public ActionResult GetAllChungChiOfNV(string mnv = "")
{
using (QUANGHANHABCEntities db = new QUANGHANHABCEntities())
{
var temp = (from cc in db.ChungChis
join ccnv in db.ChungChi_NhanVien
on cc.MaChungChi equals ccnv.MaChungChi
where ccnv.MaNV.Equals(mnv)
select new
{
MaNV = ccnv.MaNV,
TenChungChi = cc.TenChungChi,
SoHieu = ccnv.SoHieu,
NgayCap = ccnv.NgayCap,
ConHan = (DbFunctions.DiffDays(DbFunctions.AddMonths(ccnv.NgayCap, cc.ThoiHan), DateTime.Now) < 0 ? "Hết Hạn" :
DbFunctions.DiffDays(DbFunctions.AddMonths(ccnv.NgayCap, cc.ThoiHan), DateTime.Now) >= 0 ? "Còn Hạn" : "Còn Hạn"
)
});
List <ChungChi_NhanVien_Model> arrChungChi = temp.ToList().Select(p => new ChungChi_NhanVien_Model
{
TenChungChi = p.TenChungChi,
SoHieu = p.SoHieu,
NgayCap = p.NgayCap,
isConHan = p.ConHan
}).ToList();
return(Json(new { success = true, data = arrChungChi }, JsonRequestBehavior.AllowGet));
}
}
//TODO: Make a NotificationService to centralize and encapsulate notification methods/logic
public void SendFriendRequestNotifications()
{
// get friend requests that haven't had a notification sent
var friendRequests = _customerFriendRepository.Table
.Where(x => x.Confirmed == false && x.NotificationCount == 0)
.GroupBy(x => x.ToCustomerId)
.Select(g => new { CustomerId = g.Key, FriendRequestCount = g.Count() })
.ToList();
foreach (var friendRequest in friendRequests)
{
var customer = _customerService.GetCustomerById(friendRequest.CustomerId);
if (customer == null)
{
continue;
}
_mobSocialMessageService.SendFriendRequestNotification(customer, friendRequest.FriendRequestCount,
_workContext.WorkingLanguage.Id, _storeContext.CurrentStore.Id);
UpdateFriendRequestNotificationCounts(friendRequest.CustomerId);
}
// Send a reminder a week later. Get friend requests that have had one notification since last week
var weekUnconfirmedFriendRequests = _customerFriendRepository.Table
.Where(x => x.Confirmed == false && x.NotificationCount == 1)
.Where(x => DbFunctions.AddDays(x.LastNotificationDate, 7) < DateTime.Now)
.GroupBy(x => x.ToCustomerId)
.Select(g => new { CustomerId = g.Key, FriendRequestCount = g.Count() })
.ToList();
foreach (var weekUnconfirmedFriendRequest in weekUnconfirmedFriendRequests)
{
var customer = _customerService.GetCustomerById(weekUnconfirmedFriendRequest.CustomerId);
_mobSocialMessageService.SendPendingFriendRequestNotification(customer,
weekUnconfirmedFriendRequest.FriendRequestCount, _workContext.WorkingLanguage.Id, _storeContext.CurrentStore.Id);
UpdateFriendRequestNotificationCounts(weekUnconfirmedFriendRequest.CustomerId);
}
// Send pending friend request reminder each month
var monthlyUnconfirmedFriendRequests = _customerFriendRepository.Table
.Where(x => x.Confirmed == false && x.NotificationCount > 1)
.Where(x => DbFunctions.AddMonths(x.LastNotificationDate, 1) < DateTime.Now)
.GroupBy(x => x.ToCustomerId)
.Select(g => new { CustomerId = g.Key, FriendRequestCount = g.Count() })
.ToList();
foreach (var monthlyUnconfirmedFriendRequest in monthlyUnconfirmedFriendRequests)
{
var customer = _customerService.GetCustomerById(monthlyUnconfirmedFriendRequest.CustomerId);
_mobSocialMessageService.SendPendingFriendRequestNotification(customer,
monthlyUnconfirmedFriendRequest.FriendRequestCount, _workContext.WorkingLanguage.Id, _storeContext.CurrentStore.Id);
UpdateFriendRequestNotificationCounts(monthlyUnconfirmedFriendRequest.CustomerId);
}
}
/// <summary>
/// Report No.2 for contracts that are no longer active
/// </summary>
/// <returns>sends inactiveOwners list to View</returns>
public ActionResult InactiveOwners()
{
PrinterTonerContext db = new PrinterTonerContext();
var inactiveOwners = from i in db.Contracts select i;
var Today = DateTime.Now.Date; //.Date is not suported by LINQ
inactiveOwners = inactiveOwners.Where(a => DbFunctions.AddMonths(a.ContractDate, a.ContactDuration) < Today);
return(View(inactiveOwners.ToList()));
}
public void DbFunctionsTests_AddMonths_DateTime_Test()
{
var result = this.GetOrderQuery().Select(x => (DateTime)DbFunctions.AddMonths(this.TestDateTime, 1)).First();
Assert.AreEqual(YEAR, result.Year);
Assert.AreEqual(MONTH + 1, result.Month);
Assert.AreEqual(DAY, result.Day);
Assert.AreEqual(HOUR, result.Hour);
Assert.AreEqual(MINUTE, result.Minute);
Assert.AreEqual(SECOND, result.Second);
}
/// <summary>
/// Report No.2 for contracts that are no longer active
/// </summary>
/// <returns>sends inactiveOwners list to View</returns>
public ActionResult InactiveOwners()
{
using (ApplicationDbContext db = new ApplicationDbContext())
{
var inactiveOwners = from i in db.Contracts select i;
var Today = DateTime.Now.Date; //.Date is not suported by LINQ
inactiveOwners = inactiveOwners.Include(o => o.Owner).Where(a => DbFunctions.AddMonths(a.ContractDate, a.ContactDuration - 2) < Today);
return(View(inactiveOwners.ToList()));
}
}
public void DateFunctions()
{
using (var context = new BloggingContext(ConnectionString))
{
IQueryable <int> oneRow = context.Posts.Where(p => false).Select(p => 1).Concat(new int[] { 1 });
var dateAdds = oneRow.Select(p => new List <DateTime?>
{
DbFunctions.AddDays(new DateTime(2014, 2, 28), 1),
DbFunctions.AddHours(new DateTime(2014, 2, 28, 23, 0, 0), 1),
DbFunctions.AddMinutes(new DateTime(2014, 2, 28, 23, 59, 0), 1),
DbFunctions.AddSeconds(new DateTime(2014, 2, 28, 23, 59, 59), 1),
DbFunctions.AddMilliseconds(new DateTime(2014, 2, 28, 23, 59, 59, 999), 2 - p),
DbFunctions.AddMicroseconds(DbFunctions.AddMicroseconds(new DateTime(2014, 2, 28, 23, 59, 59, 999), 500), 500),
DbFunctions.AddNanoseconds(new DateTime(2014, 2, 28, 23, 59, 59, 999), 999999 + p),
DbFunctions.AddMonths(new DateTime(2014, 2, 1), 1),
DbFunctions.AddYears(new DateTime(2013, 3, 1), 1)
}).First();
foreach (var result in dateAdds)
{
Assert.IsTrue(result.Value == new DateTime(2014, 3, 1, 0, 0, 0));
}
var dateDiffs = oneRow.Select(p => new {
a = DbFunctions.DiffDays(new DateTime(1999, 12, 31, 23, 59, 59, 999), new DateTime(2000, 1, 1, 0, 0, 0)),
b = DbFunctions.DiffHours(new DateTime(1999, 12, 31, 23, 59, 59, 999), new DateTime(2000, 1, 1, 0, 0, 0)),
c = DbFunctions.DiffMinutes(new DateTime(1999, 12, 31, 23, 59, 59, 999), new DateTime(2000, 1, 1, 0, 0, 0)),
d = DbFunctions.DiffSeconds(new DateTime(1999, 12, 31, 23, 59, 59, 999), new DateTime(2000, 1, 1, 0, 0, 0)),
e = DbFunctions.DiffMilliseconds(new DateTime(1999, 12, 31, 23, 59, 59, 999), new DateTime(2000, 1, 1, 0, 0, 0)),
f = DbFunctions.DiffMicroseconds(new DateTime(1999, 12, 31, 23, 59, 59, 999), new DateTime(2000, 1, 1, 0, 0, 0)),
g = DbFunctions.DiffNanoseconds(new DateTime(1999, 12, 31, 23, 59, 59, 999), new DateTime(2000, 1, 1, 0, 0, 0)),
h = DbFunctions.DiffMonths(new DateTime(1999, 12, 31, 23, 59, 59, 999), new DateTime(3000, 1, 1, 0, 0, 0)),
i = DbFunctions.DiffYears(new DateTime(1999, 12, 31, 23, 59, 59, 999), new DateTime(3000, 1, 1, 0, 0, 0)),
j = DbFunctions.DiffYears(null, new DateTime(2000, 1, 1)),
k = DbFunctions.DiffMinutes(new TimeSpan(1, 2, 3), new TimeSpan(4, 5, 6)),
l = DbFunctions.DiffMinutes(new TimeSpan(1, 2, 3), null)
}).First();
Assert.AreEqual(dateDiffs.a, 1);
Assert.AreEqual(dateDiffs.b, 1);
Assert.AreEqual(dateDiffs.c, 1);
Assert.AreEqual(dateDiffs.d, 1);
Assert.AreEqual(dateDiffs.e, 1);
Assert.AreEqual(dateDiffs.f, 1000);
Assert.AreEqual(dateDiffs.g, 1000000);
Assert.AreEqual(dateDiffs.h, 12001);
Assert.AreEqual(dateDiffs.i, 1001);
Assert.AreEqual(dateDiffs.j, null);
Assert.AreEqual(dateDiffs.k, 183);
Assert.AreEqual(dateDiffs.l, null);
}
}
private void tenDaysLate()
{
listBox2.Items.Clear();
var lateSub = (from a in music.Abonné
join em in music.Emprunter on a.Code_Abonné equals em.Code_Abonné
where DbFunctions.AddMonths(em.Date_Emprunt.Value, 1).Value.CompareTo(DateTime.Now) <= 0
where !em.Date_Retour.HasValue
select a).ToList().Distinct();
foreach (Abonné a in lateSub)
{
listBox2.Items.Add(a);
}
}
public void DateTimeAddMonths()
{
DateTime date = stored.AddMonths(-1);
#if !EFOLD
var q = this.Entities
.Where(x =>
DbFunctions.AddMonths(x.DateTime, -1) == date);
#else
var q = this.Entities
.Where(x =>
EntityFunctions.AddMonths(x.DateTime, -1) == date);
#endif
q.Should().NotBeEmpty();
}
public IEnumerable <InvoiceProduct> GetInvoiceProductsByCustomerPhoneNumber(string phoneNumber)
{
using (var ctx = new AppDbContext())
{
var query = ctx.InvoiceProducts
.Include(i => i.Invoice)
.Include(i => i.Invoice.Customer)
.Where(i => i.Invoice.Customer.PhoneNumber == phoneNumber)
.Include(i => i.Product)
.Include(i => i.Product.Manufacturer)
.Where(i => i.Product.WarrantyPeriod != null && DbFunctions.AddMonths(i.Invoice.CreationTime, (int)i.Product.WarrantyPeriod) >= DateTime.Now)
.OrderByDescending(i => i.Invoice.CreationTime);
return(query.ToList());
}
}
public void DateTimeOffsetAddMonths()
{
DateTimeOffset offset = stored.AddMonths(-1);
#if !EFOLD
var q = this.Entities
.Where(x =>
DbFunctions.AddMonths(x.Offset, -1) == offset);
#else
var q = this.Entities
.Where(x =>
EntityFunctions.AddMonths(x.Offset, -1) == offset);
#endif
q.Should().NotBeEmpty();
}
public void Load(IQueryable <Statistic> query)
{
query = query.Where(it => it.RequestDate > DbFunctions.AddYears(DateTime.Now, -1));
var days = query.GroupBy(it => DbFunctions.TruncateTime(it.RequestDate));
var month = query.GroupBy(it => it.RequestDate.Month);
var weeks = query.GroupBy(it => DbFunctions.DiffDays(DbFunctions.CreateDateTime(DateTime.Now.Year, 1, 1, 1, 1, 1), it.RequestDate) / 7);
var week = CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(DateTime.Now, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday) - 10;
_lastDays = days
.Where(it => it.Key.Value > DbFunctions.AddMonths(DateTime.Now, -1))
.ToDictionary(q => q.Key.Value, q => q.DistinctBy(it => it.RequestIP).Count());
_lastWeeks = weeks
.Where(it => it.Key > week)
.ToDictionary(q => q.Key.Value,
q => q.DistinctBy(it => it.RequestIP).Count());
_lastMonths = month
.ToDictionary(q => q.Key > DateTime.Now.Month - 1 ? DateTime.Now.Year + "-" + (q.Key > 9 ? q.Key.ToString() : "0" + q.Key) : DateTime.Now.Year - 1 + "-" + (q.Key > 9 ? q.Key.ToString() : "0" + q.Key),
q => q.DistinctBy(it => it.RequestIP).Count());
}
public JsonResult GetDuePaymentOrders()
{
var orderList = new List <OrderInfo>();
try
{
using (var context = new OrderListEntities())
{
var result = (from it in context.Items
join ur in context.Users on it.OrderId equals ur.OrderId
where DbFunctions.AddMonths(ur.OrderDate, it.Tenure - 1) < DateTime.Now &&
DbFunctions.DiffDays(DbFunctions.AddMonths(ur.OrderDate, it.Tenure), DateTime.Now) < 30
select new { it.Tenure, ur.Name, ur.RentAmt, ur.SecurityDeposit, ur.OrderDate, ur.ContactNo, ur.Address, ur.OrderId }
).ToList();
foreach (var order in result)
{
OrderInfo orderInfo = new OrderInfo();
orderInfo.Name = order.Name;
orderInfo.RentAmt = order.RentAmt;
orderInfo.SecurityDeposit = order.SecurityDeposit;
orderInfo.OrderDate = order.OrderDate;
orderInfo.ContactNo = order.ContactNo;
orderInfo.Address = order.Address;
orderInfo.OrderId = order.OrderId;
orderList.Add(orderInfo);
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(Json(JsonConvert.SerializeObject(orderList), JsonRequestBehavior.AllowGet));
}
public void Increasement(string id)
{
var before = db.InvoiceDetails.Where(x => x.Product.Store.User.Id == id && x.Invoice.Status == ProductStatus.Delivered &&
x.isDisabled == false && x.Invoice.createdDate >= DbFunctions.AddMonths(DateTime.Now, -1));
int lastMonth = 0;
foreach (var item in before)
{
lastMonth += item.Price * item.Quantity;
}
int thisMonth = 0;
var after = db.InvoiceDetails.Where(x => x.Product.Store.User.Id == id && x.Invoice.Status == ProductStatus.Delivered &&
x.isDisabled == false && x.Invoice.createdDate.Month == DateTime.Now.Month);
foreach (var item in after)
{
thisMonth += item.Price * item.Quantity;
}
int increased = thisMonth - lastMonth;
double percent = lastMonth / (lastMonth + increased) * 100;
ViewBag.Percent = percent;
}
/// <summary>
/// Primary method for processing database queries posted to DataTables. Intended to be universal for all ADAP Applicant and Dashboard Reports.
/// </summary>
/// <param name="query">The query initialized by the DataTable Web Service calling this method.</param>
/// <param name="sFName">String of the applicant's first name</param>
/// <param name="sLName">String of the applicant's last name</param>
/// <param name="sTeam">String of the Team associated with the application.</param>
/// <param name="sStat">String of the Status of the application. Some reports use a specialized key word to add additional parameters to the query.</param>
/// <param name="sDate">String of the Date of the application. Some reports use specialized key words to add additional parameters to the query.</param>
/// <returns>IQueryable<vFormResultUsers> with additional parameters added for the DataTable.</returns>
public IQueryable <vFormResultUser> SetVfruQueryParams(IQueryable <vFormResultUser> query, String sFName, String sLName, String sTeam, String sStat, String sDate, String sDob, String ssn, string adapId,
string siteNum, string enrollmentSite, String formType, String elgEndFrom, String elgEndTo, int statusMasterId = 1)
{
query = query.Where(q => q.StatusFlag.Equals("A"));
if (!String.IsNullOrEmpty(sFName))
{
query = query.Where(q => q.FirstName.Contains(sFName));
}
if (!String.IsNullOrEmpty(sLName))
{
query = query.Where(q => q.LastName.Contains(sLName));
}
if (!string.IsNullOrWhiteSpace(siteNum))
{
query = query.Where(q => q.GroupName.StartsWith(siteNum));
}
int formId = 15;
if (!string.IsNullOrWhiteSpace(formType))
{
var parts = formType.Split('|');
var formIdPart = parts[0];
if (int.TryParse(formIdPart, out formId))
{
query = query.Where(q => q.formId == formId);
}
if (formId == 15)
{
var formVariant = parts[1];
if (formVariant != "Initial Enrollment Application")
{
query = query.Where(q => q.formVariant == formVariant);
}
else
{
query = query.Where(q => q.formVariant == null || q.formVariant == formVariant);
}
}
}
if (!string.IsNullOrWhiteSpace(elgEndFrom))
{
DateTime elgEndFromDate;
if (DateTime.TryParse(elgEndFrom, out elgEndFromDate))
{
query = query.Where(q => q.EligibilityEndDate >= elgEndFromDate);
}
}
if (!string.IsNullOrWhiteSpace(elgEndTo))
{
DateTime elgEndToDate;
if (DateTime.TryParse(elgEndTo, out elgEndToDate))
{
query = query.Where(q => q.EligibilityEndDate <= elgEndToDate);
}
}
if (!String.IsNullOrWhiteSpace(enrollmentSite))
{
var context = DataContext.getUasDbContext();
var groupIds = (from g in context.uas_Group
where g.GroupDescription.Contains(enrollmentSite) &&
g.GroupTypeID == 193
select g.GroupID).ToList();
query = query.Where(q => groupIds.Contains(q.GroupID.Value));
}
if (!String.IsNullOrEmpty(sTeam) && !sTeam.Equals("All"))
{
// serach by unit
// get child groups of unit
var context = DataContext.getUasDbContext();
int groupId;
int.TryParse(sTeam, out groupId);
List <int> groupIds = (from g in context.uas_Group
where g.ParentGroupId == groupId ||
g.GroupID == groupId
select g.GroupID).ToList();
query = query.Where(q => groupIds.Contains(q.GroupID.Value));
}
if (!String.IsNullOrEmpty(sStat) && !sStat.Equals("All"))
{
if (sStat.Equals("Pending") || sStat.Equals("All Pending"))
{
// In Process = 0, Needs Review = 1, Needs Information = 2
int inProcess = Convert.ToInt32(formsRepo.GetStatusDetailByMasterIdentifier(1, "IN_PROCESS").sortOrder);
int needsReview = Convert.ToInt32(formsRepo.GetStatusDetailByMasterIdentifier(1, "NEEDS_REVIEW").sortOrder);
int needsInformation = Convert.ToInt32(formsRepo.GetStatusDetailByMasterIdentifier(1, "NEEDS_INFORMATION").sortOrder);
query = query.Where(q => q.formStatus == inProcess || q.formStatus == needsReview || q.formStatus == needsInformation);
}
else
{
if (sStat.Contains(" Pending"))
{
sStat = sStat.Substring(0, sStat.IndexOf(" Pending"));
}
// In Process = 0, Needs Review = 1, Needs Information = 2, Denied = 3, Approved = 4, Cancelled = 5
if (!string.IsNullOrWhiteSpace(formType))
{
def_StatusMaster statusMaster = formsRepo.GetStatusMasterByFormId(formId);
statusMasterId = statusMaster == null ? 1 : statusMaster.statusMasterId;
var statusDisplay = formsRepo.GetStatusDetailByDisplayText(statusMasterId, sStat);
int index = statusDisplay != null ? statusDisplay.sortOrder.Value : -1;
query = query.Where(q => q.formStatus == index);
}
else
{
// all form types
// form enrollment
int formIdStandard = 15;
int formIdMoop = 17;
int formIdSvf = 19;
def_StatusMaster statusMaster = formsRepo.GetStatusMasterByFormId(formIdStandard);
statusMasterId = statusMaster == null ? 1 : statusMaster.statusMasterId;
var statusDisplay = formsRepo.GetStatusDetailByDisplayText(statusMasterId, sStat);
int index = statusDisplay != null ? statusDisplay.sortOrder.Value : -1;
// form moop
def_StatusMaster statusMasterMoop = formsRepo.GetStatusMasterByFormId(formIdMoop);
var statusMasterIdMoop = statusMasterMoop == null ? 1 : statusMasterMoop.statusMasterId;
var statusDisplayMoop = formsRepo.GetStatusDetailByDisplayText(statusMasterIdMoop, sStat);
int indexMoop = statusDisplayMoop != null ? statusDisplayMoop.sortOrder.Value : -1;
// form svf
def_StatusMaster statusMasterSvf = formsRepo.GetStatusMasterByFormId(formIdSvf);
var statusMasterIdSvf = statusMasterSvf == null ? 1 : statusMasterSvf.statusMasterId;
var statusDisplaySvf = formsRepo.GetStatusDetailByDisplayText(statusMasterIdSvf, sStat);
int indexSvf = statusDisplaySvf != null ? statusDisplaySvf.sortOrder.Value : -1;
query = query.Where(q => (q.formStatus == index && q.formId == formIdStandard) ||
(q.formStatus == indexSvf && q.formId == formIdSvf) ||
(q.formStatus == indexMoop && q.formId == formIdMoop));
}
}
}
if (!String.IsNullOrWhiteSpace(sDob))
{
DateTime dob;
if (DateTime.TryParse(sDob, out dob))
{
query = query.Where(q => q.DOB == dob);
}
}
if (!string.IsNullOrWhiteSpace(ssn))
{
formsEntities context = new formsEntities();
var formResultIds = (from iv in context.def_ItemVariables
join rv in context.def_ResponseVariables on iv.itemVariableId equals rv.itemVariableId
join ir in context.def_ItemResults on rv.itemResultId equals ir.itemResultId
where iv.identifier == "C1_MemberSocSecNumber" && rv.rspValue == ssn
select ir.formResultId);
query = from q in query
join fr in formResultIds.ToList() on q.formResultId equals fr
select q;
}
if (!string.IsNullOrWhiteSpace(adapId))
{
query = query.Where(x => x.adap_id == adapId);
}
if (!String.IsNullOrEmpty(sDate) && !sDate.Equals("All"))
{
//int formId = formsRepo.GetFormByIdentifier("ADAP").formId;
// "Re-Certs Late" still needs to be added.
// Current calculation does not account for Late re-certifications.
if (sDate.Contains("Last Modified within"))
{
try {
int span = Convert.ToInt32(sDate.Substring(21, 1));
DateTime compare = DateTime.Now.AddDays(span * -30);
query = query.Where(q => q.dateUpdated >= compare);
}
catch (FormatException ex) {
Debug.WriteLine("Adap Applications sDate span: " + ex.Message);
}
}
if (sDate.Contains("Re-Certs"))
{
/// Base Rule: END of birth month, END of birth month +6.
///
/// Upon first approval, if base rule results in LESS THAN 3 months, advance to the next 6 month date. E.g. birth month March,
/// initially approved July. Instead of recert due Sept 30, due next March 31. Exception applies for initial approval.
/// After that, even if a recert is late, it doesn't delay the next recert due from the Base Rule.
int approved = Convert.ToInt32(formsRepo.GetStatusDetailByMasterIdentifier(1, "APPROVED").sortOrder);
IQueryable <vFormResultUser> subQuery = formsRepo.GetFormResultsWithSubjects(SessionHelper.LoginStatus.EnterpriseID, formId)
.Where(sq => sq.formStatus == approved && sq.StatusFlag.Equals("A")).OrderByDescending(sq => sq.statusChangeDate);
// Duplicated variable to prevent InvalidOperationException: "A cycle was detected in a LINQ expression."
IQueryable <vFormResultUser> accepted = formsRepo.GetFormResultsWithSubjects(SessionHelper.LoginStatus.EnterpriseID, formId)
.Where(sq => sq.formStatus == approved && sq.StatusFlag.Equals("A")).OrderByDescending(sq => sq.statusChangeDate);
// DbFunctions formula: AddDays(AddMonths(CreateDatetime()))
// CreateDateTime() to assemble the birthdate of the current year on the first of the month.
// AddMonths() to either advance the birthdate to 7 months in the future, or a single month in the future.
// AddDays() to subtract a single day, resulting in either the last day of the month 6 months after the birthdate, or the last day of the birth month.
// Logic used below should reflect the logic used in the getRecert method.
// nextRecert pseudocode: create new IQueryable<RecertObject>
// Test if the current time falls between the birth month and 6 months from the birth month ( DOB < Today < DOB + 6 Months )
// if true: use DbFunctions formula to find the last day of the month 6 months after the birthdate
// if false: Test if the current time is later than 6 months from teh birth month. ( DOB + 6 Months < Today )
// if true: use DbFunctions formula to find the last day of the birth month in the following year.
// if false: use DbFunctions formula to find the last day of the birth month
IQueryable <RecertObject> nextRecert = formsRepo.GetFormResultsWithSubjects(SessionHelper.LoginStatus.EnterpriseID, formId)
.Select(nr => new RecertObject {
formResultId = nr.formResultId,
subject = nr.subject,
recert = (nr.DOB.Value.Month < DateTime.Now.Month && DateTime.Now.Month <= nr.DOB.Value.Month + 6)
? DbFunctions.AddDays(DbFunctions.AddMonths(DbFunctions.CreateDateTime(DateTime.Now.Year, nr.DOB.Value.Month, 1, 0, 0, 0), 7), -1)
: (nr.DOB.Value.Month + 6 < DateTime.Now.Month)
? DbFunctions.AddDays(DbFunctions.AddMonths(DbFunctions.CreateDateTime(DateTime.Now.Year + 1, nr.DOB.Value.Month, 1, 0, 0, 0), 1), -1)
: DbFunctions.AddDays(DbFunctions.AddMonths(DbFunctions.CreateDateTime(DateTime.Now.Year, nr.DOB.Value.Month, 1, 0, 0, 0), 1), -1)
});
// applyExcep pseudocode: edit nextRecert object to apply the 3 month exception.
// Ensure there is only one accepted application, then
// Test if the absolute value of the most recent statusChangeDate for the subject minus the nextRecert.recert is LESS THAN 3 ( statusChangeDate - recert < 3 )
// if true: Find the last day of the month 6 months from the recert with recert.AddDays(+1) -> AddMonths(+6) -> AddDays(-1)
// if false: Use the established recert date.
IQueryable <RecertObject> applyExcep = nextRecert.Where(nr => accepted.Where(sq => sq.subject == nr.subject).Count() > 0)
.Select(nr => new RecertObject {
formResultId = nr.formResultId,
subject = nr.subject,
recert = (accepted.Where(sq => sq.subject == nr.subject).Count() == 1 &&
Math.Abs(accepted.Select(sq => sq.statusChangeDate).FirstOrDefault().Value.Month - nr.recert.Value.Month) < 3)
? DbFunctions.AddDays(DbFunctions.AddMonths(DbFunctions.AddDays(nr.recert, 1), 6), -1)
: nr.recert
});
//// Data check for validation.
//List<RecertObject> recertCheck = new List<RecertObject>();
//foreach (var v in applyExcep)
//{
// recertCheck.Add(v);
//}
DateTime deadline = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
deadline = deadline.AddMonths(1).AddDays(-1);
if (sDate.Contains("Re-Certs Late"))
{
// Adjust the deadline to 6 months prior to the current deadline.
deadline = deadline.AddDays(1).AddMonths(-6).AddDays(-1);
// Take the most recently accepted application and check if the statusChangeDate falls before the deadline.
subQuery = subQuery.Where(sq => accepted.Where(ac => ac.subject == sq.subject).Select(ac => ac.statusChangeDate).FirstOrDefault() < deadline);
}
else if (sDate.Contains("Re-Certs Due within 7 Days"))
{
// Adjust deadline to the current date so recerts due will only show when the due date is within 7 days.
deadline = DateTime.Now;
DateTime range = deadline.AddDays(7);
subQuery = subQuery.Where(sq => deadline <= applyExcep.Where(nr => nr.subject == sq.subject).Select(nr => nr.recert).FirstOrDefault() &&
applyExcep.Where(nr => nr.subject == sq.subject).Select(nr => nr.recert).FirstOrDefault() <= range);
//// Data check for validation
//List<string[]> dataCheck = new List<string[]>();
//foreach (var v in subQuery)
//{
// dataCheck.Add(new string[] { v.subject.ToString() });
//}
}
else if (sDate.Contains("Re-Certs Due within"))
{
try {
// This block begins by parsing the month multiplier from the selection string.
int span = Convert.ToInt32(sDate.Substring(20, 1));
DateTime range = deadline.AddDays(1).AddMonths(span).AddDays(-1);
subQuery = subQuery.Where(sq => deadline <= applyExcep.Where(nr => nr.subject == sq.subject).Select(nr => nr.recert).FirstOrDefault() &&
applyExcep.Where(nr => nr.subject == sq.subject).Select(nr => nr.recert).FirstOrDefault() <= range);
}
catch (FormatException ex) {
Debug.WriteLine("Adap Reports Controller sDate span: " + ex.Message);
}
}
query = query.Where(q => subQuery.Where(sq => sq.subject == q.subject).Select(sq => sq.subject).FirstOrDefault() == q.subject);
query = query.OrderByDescending(q => q.dateUpdated);
}
else if (sDate.Contains("&"))
{
string[] dates = sDate.Split('&');
DateTime from;
bool fromBool = true;
DateTime to;
bool toBool = true;
if (!String.IsNullOrEmpty(dates[0]))
{
try {
from = Convert.ToDateTime(dates[0]);
}
catch (FormatException ex) {
Debug.WriteLine("Adap Reports Controller sDate from date conversation: " + ex.Message);
from = new DateTime(2000, 1, 1);
}
}
else
{
from = new DateTime(2000, 1, 1);
fromBool = false;
}
if (!String.IsNullOrEmpty(dates[1]))
{
try {
to = Convert.ToDateTime(dates[1]);
}
catch (FormatException ex) {
Debug.WriteLine("Adap Reports Controller sDate to date conversation: " + ex.Message);
to = DateTime.Now.AddDays(1);
}
}
else
{
to = DateTime.Now.AddDays(1);
toBool = false;
}
IQueryable <vFormResultUser> subQuery = formsRepo.GetFormResultsWithSubjects(SessionHelper.LoginStatus.EnterpriseID, formId);
int needsReview = Convert.ToInt32(formsRepo.GetStatusDetailByMasterIdentifier(1, "NEEDS_REVIEW").sortOrder);
subQuery = subQuery.Where(sq => /*sq.formStatus == needsReview &&*/ sq.StatusFlag.Equals("A"));
if (dates[2].Contains("Overview"))
{
subQuery = subQuery.Where(sq => (sq.formStatus == needsReview) &&
((fromBool) ? sq.statusChangeDate >= from : true) && ((toBool) ? sq.statusChangeDate <= to : true)).OrderByDescending(sq => sq.statusChangeDate);
}
else if (dates[2].Contains("Summary"))
{
subQuery = subQuery.Where(sq => ((fromBool) ? sq.statusChangeDate >= from : true) && ((toBool) ? sq.statusChangeDate <= to : true))
.OrderByDescending(sq => sq.statusChangeDate);
}
//List<string[]> dataCheck = new List<string[]>();
//foreach (var v in subQuery)
//{
// dataCheck.Add(new string[] { v.subject.ToString(), v.formStatus.ToString(), v.GroupName, v.statusChangeDate.ToString() });
//}
if (dates[2].Contains("Overview"))
{
query = query.Where(q => q.subject == subQuery.Where(sq => sq.subject == q.subject).Select(sq => sq.subject).FirstOrDefault());
}
else if (dates[2].Contains("Summary"))
{
query = query.Where(q => q.formResultId == subQuery.Where(sq => sq.subject == q.subject)
.OrderByDescending(sq => sq.statusChangeDate).Select(sq => sq.formResultId).FirstOrDefault());
}
}
}
return(query);
}
public List <TC_PART_HS_AUDIT> GetAuditLog(string partNo)
{
List <TC_PART_HS_AUDIT> list = db.TC_PART_HS_AUDIT.Where(a => a.PART_NO == partNo && DbFunctions.AddMonths(a.ACTIVITY_DATE, 12) > DateTime.Now).ToList();
return(list);
}
public void DbFunctionsTests_AddMonths_DateTimeOffset_Test()
{
this.AssertException <NotSupportedException>(() => {
this.GetOrderQuery().Select(x => DbFunctions.AddMonths(this.TestDateTimeOffset, 1)).First();
});
}
/// <summary>
/// 构造查询IQueryable
/// </summary>
/// <param name="db">查询条件</param>
/// <param name="searchKey">查询条件</param>
/// <param name="searchKey">查询条件</param>
/// <param name="searchKey">查询条件</param>
/// <returns></returns>
private static IQueryable <ht_news> GetNewsData(Entities db, ht_news searchKey, bool isOrder = false)
{
db.Configuration.ProxyCreationEnabled = false;
var data = db.ht_news.Where(p => true);
data = data.Where(p => p.is_delete == 0);
if (searchKey.cateid != 0)
{
data = data.Where(p => p.cateid == searchKey.cateid);
}
if (!string.IsNullOrWhiteSpace(searchKey.start_province))
{
data = data.Where(p => p.start_province == searchKey.start_province);
}
if (!string.IsNullOrWhiteSpace(searchKey.start_city))
{
data = data.Where(p => p.start_city == searchKey.start_city);
}
if (!string.IsNullOrWhiteSpace(searchKey.start_district))
{
data = data.Where(p => p.start_district == searchKey.start_district);
}
if (!string.IsNullOrWhiteSpace(searchKey.stop_province))
{
data = data.Where(p => p.stop_province == searchKey.stop_province);
}
if (!string.IsNullOrWhiteSpace(searchKey.stop_city))
{
data = data.Where(p => p.stop_city == searchKey.stop_city);
}
if (!string.IsNullOrWhiteSpace(searchKey.stop_district))
{
data = data.Where(p => p.stop_district == searchKey.stop_district);
}
if (!string.IsNullOrWhiteSpace(searchKey.use_type))
{
data = data.Where(p => p.use_type == searchKey.use_type);
}
if (!string.IsNullOrWhiteSpace(searchKey.car_length))
{
data = data.Where(p => p.car_length == searchKey.car_length);
}
if (!string.IsNullOrWhiteSpace(searchKey.car_style))
{
data = data.Where(p => p.car_style == searchKey.car_style);
}
if (!string.IsNullOrWhiteSpace(searchKey.goods_type))
{
data = data.Where(p => p.goods_type == searchKey.goods_type);
}
if (searchKey.expire.HasValue && searchKey.expire == 1)
{
data = data.Where(p => (p.validity_unit == "月" && DbFunctions.AddMonths(p.add_time, p.validity_num.Value) < DateTime.Now) || (p.validity_unit == "天" && DbFunctions.AddDays(p.add_time, p.validity_num.Value) < DateTime.Now));
}
else if (searchKey.expire.HasValue && searchKey.expire == 0)
{
data = data.Where(p => (p.validity_unit == "月" && DbFunctions.AddMonths(p.add_time, p.validity_num.Value) > DateTime.Now) || (p.validity_unit == "天" && DbFunctions.AddDays(p.add_time, p.validity_num.Value) > DateTime.Now));
}
if (searchKey.status.HasValue)
{
data = data.Where(p => p.status == searchKey.status);
}
if (searchKey.pay_status.HasValue)
{
data = data.Where(p => p.pay_status == searchKey.pay_status);
}
if (searchKey.add_userid != 0)
{
data = data.Where(p => p.add_userid == searchKey.add_userid);
}
if (isOrder == false)
{
return(data);
}
var orderData = searchKey.add_userid != 0? data.OrderByDescending(p => p.id) : data.OrderByDescending(p => p.set_top);
if (searchKey.recommend.HasValue && searchKey.recommend.Value)
{
orderData = orderData.ThenByDescending(p => p.praise_num);
}
orderData = orderData.ThenByDescending(p => p.update_time);
return(orderData);
}
// GET: Prisoners
public ActionResult GetPrisoners(string searchString, string sortOrder)
{
ViewBag.IDSortParm = string.IsNullOrEmpty(sortOrder) ? "id_desc" : "";
ViewBag.NazwiskoSortParm = sortOrder == "nazwisko" ? "nazwisko_desc" : "nazwisko";
ViewBag.ImieSortParm = sortOrder == "imie" ? "imie_desc" : "imie";
ViewBag.PeselSortParm = sortOrder == "pesel" ? "pesel_desc" : "pesel";
ViewBag.PlecSortParm = sortOrder == "plec" ? "plec_desc" : "plec";
var list = from w in _db.Prisoners
join judgement in _db.Judgements on w.Prisoner_Id equals judgement.FK_Judgements_Prisoners_Id
join allocation in _db.Allocation on w.Prisoner_Id equals allocation.FK_Allocation_Prisoners_Id
where allocation.FK_Allocation_Cells_Id != ReleasePrisonerClass.Placeholder
select new ShowPrisonersViewModel
{
Prisoner = w,
Judgement = judgement,
Allocation = allocation,
Date = DbFunctions.AddMonths(judgement.StartDate, judgement.TimeOfJudgement).Value
};
if (TempData["alertMessage"] != null)
{
ViewBag.alert = TempData["alertMessage"].ToString();
}
if (!string.IsNullOrEmpty(searchString))
{
list = list.Where(s => s.Prisoner.Pesel.Contains(searchString) ||
s.Prisoner.PrisonerSurname.Contains(searchString) ||
s.Prisoner.PrisonerName.Contains(searchString));
}
switch (sortOrder)
{
case "id_desc":
list = list.OrderByDescending(s => s.Prisoner.Prisoner_Id);
break;
case "surname":
list = list.OrderBy(s => s.Prisoner.PrisonerSurname);
break;
case "surname_desc":
list = list.OrderByDescending(s => s.Prisoner.PrisonerSurname);
break;
case "name":
list = list.OrderBy(s => s.Prisoner.PrisonerName);
break;
case "name_desc":
list = list.OrderByDescending(s => s.Prisoner.PrisonerName);
break;
case "sex":
list = list.OrderBy(s => s.Prisoner.Sex);
break;
case "sex_desc":
list = list.OrderByDescending(s => s.Prisoner.Sex);
break;
case "pesel":
list = list.OrderBy(s => s.Prisoner.Pesel);
break;
case "pesel_desc":
list = list.OrderByDescending(s => s.Prisoner.Pesel);
break;
default:
list = list.OrderBy(s => s.Prisoner.Prisoner_Id);
break;
}
return(View(list));
}
public ActionResult ReportJob(string maPhongBan = "", string tenPhongBan = "")
{
using (QUANGHANHABCEntities db = new QUANGHANHABCEntities())
{
var temp = (from a in db.Departments select new { maPhongBan = a.department_id, TenPhanXuong = a.department_name }).ToList();
IEnumerable <Department> arrPhanXuong = temp.Distinct().ToList().Select(p => new Department {
department_id = p.maPhongBan, department_name = p.TenPhanXuong
});
ViewBag.PhanXuongs = arrPhanXuong;
}
ViewBag.nameDepartment = "vld-antoan";
if (maPhongBan.Equals(""))
{
using (QUANGHANHABCEntities db = new QUANGHANHABCEntities())
{
var temp = (from ct in db.ChiTiet_NhiemVu_NhanVien
join n in db.NhanViens
on ct.MaNV equals n.MaNV
join nv in db.NhiemVus
on ct.MaNhiemVu equals nv.MaNhiemVu
join cc in db.ChungChis
on nv.MaChungChi equals cc.MaChungChi
join ccnv in db.ChungChi_NhanVien
on new { n.MaNV, cc.MaChungChi } equals new { ccnv.MaNV, ccnv.MaChungChi }
into tb
from mtb in tb.DefaultIfEmpty()
where n.MaPhongBan.Equals("DL1")
orderby nv.Loai
select new
{
SoHieu = mtb.SoHieu,
Loai = nv.Loai,
TenNhanVien = n.Ten,
TenNhiemVu = nv.TenNhiemVu,
TenChungChi = cc.TenChungChi,
MaChungChi = cc.MaChungChi,
MaNhanVien = n.MaNV,
IsConHan = (DbFunctions.DiffDays(DbFunctions.AddMonths(mtb.NgayCap, cc.ThoiHan), DateTime.Now) > 0 ? "Hết hạn" :
DbFunctions.DiffDays(DbFunctions.AddMonths(mtb.NgayCap, cc.ThoiHan), DateTime.Now) <= 0 ? "Còn hạn" : "Chưa có")
,
SoNgay = (DbFunctions.DiffDays(DbFunctions.AddMonths(mtb.NgayCap, cc.ThoiHan), DateTime.Now) < 0 ? -DbFunctions.DiffDays(DbFunctions.AddMonths(mtb.NgayCap, cc.ThoiHan), DateTime.Now) :
DbFunctions.DiffDays(DbFunctions.AddMonths(mtb.NgayCap, cc.ThoiHan), DateTime.Now) >= 0 ? DbFunctions.DiffDays(DbFunctions.AddMonths(mtb.NgayCap, cc.ThoiHan), DateTime.Now) : 0),
}
).ToList();
List <ReportModel> arrReportModels = new List <ReportModel>();
bool isContainLoai = false;
bool isContainNV = false;
foreach (var myTemp in temp)
{
foreach (ReportModel reportModel in arrReportModels)
{
if (reportModel.Loai.Equals(myTemp.Loai))
{
foreach (TaskModel taskModel in reportModel.arrTask)
{
if (taskModel.TenNhiemVu.Equals(myTemp.TenNhiemVu))
{
ChungChi_NhanVien_Model ccnv = new ChungChi_NhanVien_Model {
TenNV = myTemp.TenNhanVien, SoHieu = myTemp.SoHieu, MaNV = myTemp.MaNhanVien, MaChungChi = myTemp.MaChungChi, TenChungChi = myTemp.TenChungChi, SoNgay = myTemp.SoNgay, isConHan = myTemp.IsConHan
};
taskModel.arrNhanVien.Add(ccnv);
reportModel.Rowspan++;
taskModel.Rowspan++;
isContainNV = true;
}
}
if (!isContainNV)
{
TaskModel taskModel = new TaskModel
{
TenNhiemVu = myTemp.TenNhiemVu
};
ChungChi_NhanVien_Model ccnv = new ChungChi_NhanVien_Model {
TenNV = myTemp.TenNhanVien, SoHieu = myTemp.SoHieu, MaNV = myTemp.MaNhanVien, MaChungChi = myTemp.MaChungChi, TenChungChi = myTemp.TenChungChi, SoNgay = myTemp.SoNgay, isConHan = myTemp.IsConHan
};
taskModel.arrNhanVien.Add(ccnv);
reportModel.arrTask.Add(taskModel);
reportModel.Rowspan++;
taskModel.Rowspan++;
isContainNV = false;
}
isContainLoai = true;
}
}
if (!isContainLoai)
{
ReportModel reportModel = new ReportModel
{
Loai = myTemp.Loai
};
TaskModel taskModel = new TaskModel
{
TenNhiemVu = myTemp.TenNhiemVu
};
ChungChi_NhanVien_Model ccnv = new ChungChi_NhanVien_Model {
TenNV = myTemp.TenNhanVien, SoHieu = myTemp.SoHieu, MaNV = myTemp.MaNhanVien, MaChungChi = myTemp.MaChungChi, TenChungChi = myTemp.TenChungChi, SoNgay = myTemp.SoNgay, isConHan = myTemp.IsConHan
};
reportModel.Rowspan++;
taskModel.Rowspan++;
taskModel.arrNhanVien.Add(ccnv);
reportModel.arrTask.Add(taskModel);
arrReportModels.Add(reportModel);
}
isContainLoai = false;
}
ViewBag.arrReportModels = arrReportModels;
}
}
else
{
using (QUANGHANHABCEntities db = new QUANGHANHABCEntities())
{
var temp = (from ct in db.ChiTiet_NhiemVu_NhanVien
join n in db.NhanViens
on ct.MaNV equals n.MaNV
join nv in db.NhiemVus
on ct.MaNhiemVu equals nv.MaNhiemVu
join cc in db.ChungChis
on nv.MaChungChi equals cc.MaChungChi
join ccnv in db.ChungChi_NhanVien
on new { n.MaNV, cc.MaChungChi } equals new { ccnv.MaNV, ccnv.MaChungChi }
into tb
from mtb in tb.DefaultIfEmpty()
where n.MaPhongBan.Equals(maPhongBan)
orderby nv.Loai
select new
{
SoHieu = mtb.SoHieu,
Loai = nv.Loai,
TenNhanVien = n.Ten,
TenNhiemVu = nv.TenNhiemVu,
TenChungChi = cc.TenChungChi,
MaChungChi = cc.MaChungChi,
MaNhanVien = n.MaNV,
IsConHan = (DbFunctions.DiffDays(DbFunctions.AddMonths(mtb.NgayCap, cc.ThoiHan), DateTime.Now) > 0 ? "Hết hạn" :
DbFunctions.DiffDays(DbFunctions.AddMonths(mtb.NgayCap, cc.ThoiHan), DateTime.Now) <= 0 ? "Còn hạn" : "Chưa có")
,
SoNgay = (DbFunctions.DiffDays(DbFunctions.AddMonths(mtb.NgayCap, cc.ThoiHan), DateTime.Now) < 0 ? -DbFunctions.DiffDays(DbFunctions.AddMonths(mtb.NgayCap, cc.ThoiHan), DateTime.Now) :
DbFunctions.DiffDays(DbFunctions.AddMonths(mtb.NgayCap, cc.ThoiHan), DateTime.Now) >= 0 ? DbFunctions.DiffDays(DbFunctions.AddMonths(mtb.NgayCap, cc.ThoiHan), DateTime.Now) : 0),
}
).ToList();
List <ReportModel> arrReportModels = new List <ReportModel>();
bool isContainLoai = false;
bool isContainNV = false;
foreach (var myTemp in temp)
{
foreach (ReportModel reportModel in arrReportModels)
{
if (reportModel.Loai.Equals(myTemp.Loai))
{
foreach (TaskModel taskModel in reportModel.arrTask)
{
if (taskModel.TenNhiemVu.Equals(myTemp.TenNhiemVu))
{
ChungChi_NhanVien_Model ccnv = new ChungChi_NhanVien_Model {
TenNV = myTemp.TenNhanVien, SoHieu = myTemp.SoHieu, MaNV = myTemp.MaNhanVien, MaChungChi = myTemp.MaChungChi, TenChungChi = myTemp.TenChungChi, SoNgay = myTemp.SoNgay, isConHan = myTemp.IsConHan
};
taskModel.arrNhanVien.Add(ccnv);
reportModel.Rowspan++;
taskModel.Rowspan++;
isContainNV = true;
}
}
if (!isContainNV)
{
TaskModel taskModel = new TaskModel
{
TenNhiemVu = myTemp.TenNhiemVu
};
ChungChi_NhanVien_Model ccnv = new ChungChi_NhanVien_Model {
TenNV = myTemp.TenNhanVien, SoHieu = myTemp.SoHieu, MaNV = myTemp.MaNhanVien, MaChungChi = myTemp.MaChungChi, TenChungChi = myTemp.TenChungChi, SoNgay = myTemp.SoNgay, isConHan = myTemp.IsConHan
};
taskModel.arrNhanVien.Add(ccnv);
reportModel.arrTask.Add(taskModel);
reportModel.Rowspan++;
taskModel.Rowspan++;
isContainNV = false;
}
isContainLoai = true;
}
}
if (!isContainLoai)
{
ReportModel reportModel = new ReportModel
{
Loai = myTemp.Loai
};
TaskModel taskModel = new TaskModel
{
TenNhiemVu = myTemp.TenNhiemVu
};
ChungChi_NhanVien_Model ccnv = new ChungChi_NhanVien_Model {
TenNV = myTemp.TenNhanVien, SoHieu = myTemp.SoHieu, MaNV = myTemp.MaNhanVien, MaChungChi = myTemp.MaChungChi, TenChungChi = myTemp.TenChungChi, SoNgay = myTemp.SoNgay, isConHan = myTemp.IsConHan
};
reportModel.Rowspan++;
taskModel.Rowspan++;
taskModel.arrNhanVien.Add(ccnv);
reportModel.arrTask.Add(taskModel);
arrReportModels.Add(reportModel);
}
isContainLoai = false;
}
ViewBag.arrReportModels = arrReportModels;
ViewBag.tenPhongBan = tenPhongBan;
}
}
return(View("/Views/TCLD/Task/ReportJob.cshtml"));
}
/// <summary>
/// Primary method for processing database queries posted to DataTables. Intended to be universal for all ADAP Applicant and Dashboard Reports.
/// </summary>
/// <param name="query">The query initialized by the DataTable Web Service calling this method.</param>
/// <param name="sFName">String of the applicant's first name</param>
/// <param name="sLName">String of the applicant's last name</param>
/// <param name="sTeam">String of the Team associated with the application.</param>
/// <param name="sStat">String of the Status of the application. Some reports use a specialized key word to add additional parameters to the query.</param>
/// <param name="sDate">String of the Date of the application. Some reports use specialized key words to add additional parameters to the query.</param>
/// <returns>IQueryable<vFormResultUsers> with additional parameters added for the DataTable.</returns>
public IQueryable <vFormResultUser> SetVfruQueryParams(IQueryable <vFormResultUser> query, String sFName, String sLName, String sTeam, String sStat, String sDate, String sType, int statusMasterId = 1)
{
query = query.Where(q => q.StatusFlag.Equals("A"));
if (!String.IsNullOrEmpty(sFName))
{
query = query.Where(q => q.FirstName.Contains(sFName));
}
if (!String.IsNullOrEmpty(sLName))
{
query = query.Where(q => q.LastName.Contains(sLName));
}
if (!String.IsNullOrEmpty(sTeam) && !sTeam.Equals("All"))
{
query = query.Where(q => q.GroupName.Contains(sTeam));
}
if (!String.IsNullOrEmpty(sType) && !sType.Equals("All"))
{
int formId = formsRepo.GetFormByIdentifier(sType).formId;
query = query.Where(q => q.formId == formId);
}
if (!String.IsNullOrEmpty(sStat) && !sStat.Equals("All"))
{
if (sStat.Equals("Pending") || sStat.Equals("All Pending"))
{
// In Process = 0, Needs Review = 1, Needs Information = 2
int inProcess = Convert.ToInt32(formsRepo.GetStatusDetailByMasterIdentifier(1, "IN_PROCESS").sortOrder);
int needsReview = Convert.ToInt32(formsRepo.GetStatusDetailByMasterIdentifier(1, "NEEDS_REVIEW").sortOrder);
int needsInformation = Convert.ToInt32(formsRepo.GetStatusDetailByMasterIdentifier(1, "NEEDS_INFORMATION").sortOrder);
query = query.Where(q => q.formStatus == inProcess || q.formStatus == needsReview || q.formStatus == needsInformation);
}
else
{
if (sStat.Contains(" Pending"))
{
sStat = sStat.Substring(0, sStat.IndexOf(" Pending"));
}
// In Process = 0, Needs Review = 1, Needs Information = 2, Denied = 3, Approved = 4, Cancelled = 5
int index = Convert.ToInt32(formsRepo.GetStatusDetailByDisplayText(statusMasterId, sStat).sortOrder);
query = query.Where(q => q.formStatus == index);
}
}
if (!String.IsNullOrEmpty(sDate) && !sDate.Equals("All"))
{
int formId = formsRepo.GetFormByIdentifier("ADAP").formId;
// "Re-Certs Late" still needs to be added.
// Current calculation does not account for Late re-certifications.
if (sDate.Contains("Last Modified within"))
{
try {
int span = Convert.ToInt32(sDate.Substring(21, 1));
DateTime compare = DateTime.Now.AddDays(span * -30);
query = query.Where(q => q.dateUpdated >= compare);
}
catch (FormatException ex) {
Debug.WriteLine("Adap Applications sDate span: " + ex.Message);
}
}
if (sDate.Contains("Re-Certs"))
{
/// Base Rule: END of birth month, END of birth month +6.
///
/// Upon first approval, if base rule results in LESS THAN 3 months, advance to the next 6 month date. E.g. birth month March,
/// initially approved July. Instead of recert due Sept 30, due next March 31. Exception applies for initial approval.
/// After that, even if a recert is late, it doesn't delay the next recert due from the Base Rule.
int approved = Convert.ToInt32(formsRepo.GetStatusDetailByMasterIdentifier(1, "APPROVED").sortOrder);
IQueryable <vFormResultUser> subQuery = formsRepo.GetFormResultsWithSubjects(SessionHelper.LoginStatus.EnterpriseID, formId)
.Where(sq => sq.formStatus == approved && sq.StatusFlag.Equals("A")).OrderByDescending(sq => sq.statusChangeDate);
// Duplicated variable to prevent InvalidOperationException: "A cycle was detected in a LINQ expression."
IQueryable <vFormResultUser> accepted = formsRepo.GetFormResultsWithSubjects(SessionHelper.LoginStatus.EnterpriseID, formId)
.Where(sq => sq.formStatus == approved && sq.StatusFlag.Equals("A")).OrderByDescending(sq => sq.statusChangeDate);
// DbFunctions formula: AddDays(AddMonths(CreateDatetime()))
// CreateDateTime() to assemble the birthdate of the current year on the first of the month.
// AddMonths() to either advance the birthdate to 7 months in the future, or a single month in the future.
// AddDays() to subtract a single day, resulting in either the last day of the month 6 months after the birthdate, or the last day of the birth month.
// Logic used below should reflect the logic used in the getRecert method.
// nextRecert pseudocode: create new IQueryable<RecertObject>
// Test if the current time falls between the birth month and 6 months from the birth month ( DOB < Today < DOB + 6 Months )
// if true: use DbFunctions formula to find the last day of the month 6 months after the birthdate
// if false: Test if the current time is later than 6 months from teh birth month. ( DOB + 6 Months < Today )
// if true: use DbFunctions formula to find the last day of the birth month in the following year.
// if false: use DbFunctions formula to find the last day of the birth month
IQueryable <RecertObject> nextRecert = formsRepo.GetFormResultsWithSubjects(SessionHelper.LoginStatus.EnterpriseID, formId)
.Select(nr => new RecertObject {
formResultId = nr.formResultId,
subject = nr.subject,
recert = (nr.DOB.Value.Month < DateTime.Now.Month && DateTime.Now.Month <= nr.DOB.Value.Month + 6)
? DbFunctions.AddDays(DbFunctions.AddMonths(DbFunctions.CreateDateTime(DateTime.Now.Year, nr.DOB.Value.Month, 1, 0, 0, 0), 7), -1)
: (nr.DOB.Value.Month + 6 < DateTime.Now.Month)
? DbFunctions.AddDays(DbFunctions.AddMonths(DbFunctions.CreateDateTime(DateTime.Now.Year + 1, nr.DOB.Value.Month, 1, 0, 0, 0), 1), -1)
: DbFunctions.AddDays(DbFunctions.AddMonths(DbFunctions.CreateDateTime(DateTime.Now.Year, nr.DOB.Value.Month, 1, 0, 0, 0), 1), -1)
});
// applyExcep pseudocode: edit nextRecert object to apply the 3 month exception.
// Ensure there is only one accepted application, then
// Test if the absolute value of the most recent statusChangeDate for the subject minus the nextRecert.recert is LESS THAN 3 ( statusChangeDate - recert < 3 )
// if true: Find the last day of the month 6 months from the recert with recert.AddDays(+1) -> AddMonths(+6) -> AddDays(-1)
// if false: Use the established recert date.
IQueryable <RecertObject> applyExcep = nextRecert.Where(nr => accepted.Where(sq => sq.subject == nr.subject).Count() > 0)
.Select(nr => new RecertObject {
formResultId = nr.formResultId,
subject = nr.subject,
recert = (accepted.Where(sq => sq.subject == nr.subject).Count() == 1 &&
Math.Abs(accepted.Select(sq => sq.statusChangeDate).FirstOrDefault().Value.Month - nr.recert.Value.Month) < 3)
? DbFunctions.AddDays(DbFunctions.AddMonths(DbFunctions.AddDays(nr.recert, 1), 6), -1)
: nr.recert
});
//// Data check for validation.
//List<RecertObject> recertCheck = new List<RecertObject>();
//foreach (var v in applyExcep)
//{
// recertCheck.Add(v);
//}
DateTime deadline = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
deadline = deadline.AddMonths(1).AddDays(-1);
if (sDate.Contains("Re-Certs Late"))
{
// Adjust the deadline to 6 months prior to the current deadline.
deadline = deadline.AddDays(1).AddMonths(-6).AddDays(-1);
// Take the most recently accepted application and check if the statusChangeDate falls before the deadline.
subQuery = subQuery.Where(sq => accepted.Where(ac => ac.subject == sq.subject).Select(ac => ac.statusChangeDate).FirstOrDefault() < deadline);
}
else if (sDate.Contains("Re-Certs Due within 7 Days"))
{
// Adjust deadline to the current date so recerts due will only show when the due date is within 7 days.
deadline = DateTime.Now;
DateTime range = deadline.AddDays(7);
subQuery = subQuery.Where(sq => deadline <= applyExcep.Where(nr => nr.subject == sq.subject).Select(nr => nr.recert).FirstOrDefault() &&
applyExcep.Where(nr => nr.subject == sq.subject).Select(nr => nr.recert).FirstOrDefault() <= range);
//// Data check for validation
//List<string[]> dataCheck = new List<string[]>();
//foreach (var v in subQuery)
//{
// dataCheck.Add(new string[] { v.subject.ToString() });
//}
}
else if (sDate.Contains("Re-Certs Due within"))
{
try {
// This block begins by parsing the month multiplier from the selection string.
int span = Convert.ToInt32(sDate.Substring(20, 1));
DateTime range = deadline.AddDays(1).AddMonths(span).AddDays(-1);
subQuery = subQuery.Where(sq => deadline <= applyExcep.Where(nr => nr.subject == sq.subject).Select(nr => nr.recert).FirstOrDefault() &&
applyExcep.Where(nr => nr.subject == sq.subject).Select(nr => nr.recert).FirstOrDefault() <= range);
}
catch (FormatException ex) {
Debug.WriteLine("Adap Reports Controller sDate span: " + ex.Message);
}
}
query = query.Where(q => subQuery.Where(sq => sq.subject == q.subject).Select(sq => sq.subject).FirstOrDefault() == q.subject);
query = query.OrderByDescending(q => q.dateUpdated);
}
else if (sDate.Contains("&"))
{
string[] dates = sDate.Split('&');
DateTime from;
bool fromBool = true;
DateTime to;
bool toBool = true;
if (!String.IsNullOrEmpty(dates[0]))
{
try {
from = Convert.ToDateTime(dates[0]);
}
catch (FormatException ex) {
Debug.WriteLine("Adap Reports Controller sDate from date conversation: " + ex.Message);
from = new DateTime(2000, 1, 1);
}
}
else
{
from = new DateTime(2000, 1, 1);
fromBool = false;
}
if (!String.IsNullOrEmpty(dates[1]))
{
try {
to = Convert.ToDateTime(dates[1]);
}
catch (FormatException ex) {
Debug.WriteLine("Adap Reports Controller sDate to date conversation: " + ex.Message);
to = DateTime.Now.AddDays(1);
}
}
else
{
to = DateTime.Now.AddDays(1);
toBool = false;
}
IQueryable <vFormResultUser> subQuery = formsRepo.GetFormResultsWithSubjects(SessionHelper.LoginStatus.EnterpriseID, formId);
int needsReview = Convert.ToInt32(formsRepo.GetStatusDetailByMasterIdentifier(1, "NEEDS_REVIEW").sortOrder);
subQuery = subQuery.Where(sq => /*sq.formStatus == needsReview &&*/ sq.StatusFlag.Equals("A"));
if (dates[2].Contains("Overview"))
{
subQuery = subQuery.Where(sq => (sq.formStatus == needsReview) &&
((fromBool) ? sq.statusChangeDate >= from : true) && ((toBool) ? sq.statusChangeDate <= to : true)).OrderByDescending(sq => sq.statusChangeDate);
}
else if (dates[2].Contains("Summary"))
{
subQuery = subQuery.Where(sq => ((fromBool) ? sq.statusChangeDate >= from : true) && ((toBool) ? sq.statusChangeDate <= to : true))
.OrderByDescending(sq => sq.statusChangeDate);
}
//List<string[]> dataCheck = new List<string[]>();
//foreach (var v in subQuery)
//{
// dataCheck.Add(new string[] { v.subject.ToString(), v.formStatus.ToString(), v.GroupName, v.statusChangeDate.ToString() });
//}
if (dates[2].Contains("Overview"))
{
query = query.Where(q => q.subject == subQuery.Where(sq => sq.subject == q.subject).Select(sq => sq.subject).FirstOrDefault());
}
else if (dates[2].Contains("Summary"))
{
query = query.Where(q => q.formResultId == subQuery.Where(sq => sq.subject == q.subject)
.OrderByDescending(sq => sq.statusChangeDate).Select(sq => sq.formResultId).FirstOrDefault());
}
}
}
return(query);
}
public DateTime?AddMonths(DateTime?date, int?monthsToAdd)
{
return(DbFunctions.AddMonths(date, monthsToAdd));
}
public IEnumerable <PayingItem> GetPayingItemsForLastYear(WebUser user)
{
var dateFrom = DateTime.Parse(DateTime.Today.ToString("Y", CultureInfo.CurrentCulture));
try
{
return(_payingItemService.GetList(x => x.UserId == user.Id && x.Date <= DbFunctions.AddMonths(dateFrom, 1) && x.Date >= DbFunctions.AddYears(dateFrom, -1))
.ToList());
}
catch (ServiceException e)
{
throw new WebUiHelperException(
$"Ошибка в типе {nameof(ReportControllerHelper)} в методе {nameof(GetPayingItemsForLastYear)}", e);
}
}
