public void ExportToExcel(string GridModel)
{
ExcelExport exp = new ExcelExport();
var DataSource = (from job in db.JobsAssigneds
join emp in db.EmployeeJobs
on job.AssignID equals emp.AssignID
select new
{
AssignID = job.AssignID,
AssignJOBNUM = job.AssignJOBNUM,
AssignCLIENT = job.AssignCLIENT,
AssignWORK = job.AssignWORK,
AssignAREA = job.AssignAREA,
AssignINSTRUCTIONS = job.AssignINSTRUCTIONS,
AssignTRUCK = job.AssignTRUCK,
TextSENT = job.TextSENT,
AssignSTARTTIME = job.AssignSTARTTIME,
AssignENDTIME = job.AssignENDTIME,
Hours = EntityFunctions.DiffHours(job.AssignSTARTTIME, job.AssignENDTIME),
EmpNAME = emp.EmpNAME
}).ToList();
GridProperties obj = (GridProperties)Syncfusion.JavaScript.Utils.DeserializeToModel(typeof(GridProperties), GridModel);
exp.Export(obj, DataSource, "Export.xlsx", ExcelVersion.Excel2010, false, false, "flat-saffron");
}
public ActionResult SearchList(string searchCat, int?grpId)
{
oCurrentUser = (CCUSER)Session["User"];
var srchTaskList = new TaskList();
int enumDBType = 0;
enumDBType = Convert.ToInt32(Enumaretion.DBEnumType.PriorityType);
if (searchCat == "1")
{
srchTaskList.TASKTYPE = 1;
srchTaskList.TASKTYPENAME = "High Priority Request List";
long HightPriorityId = oDBContext.CCENUMERATIONs.FirstOrDefault(t => t.TYPE == enumDBType && t.NAME.ToLower().Contains("high")).ID;
srchTaskList.CCMEMOLIST = oDBContext.CCMEMOMASTERs.Where(t => t.PRIORITY == HightPriorityId && t.ISOPEN && t.ISACTIVE == true).OrderByDescending(x => x.CREATEDON).ToList();
}
else if (searchCat == "2")//when any case is referred to checker 6hours ago
{
srchTaskList.TASKTYPE = 2;
srchTaskList.TASKTYPENAME = "6 Hours Crossed Request";
srchTaskList.CCMEMOLIST = oDBContext.CCMEMOLOGs.Where(t => EntityFunctions.DiffHours(t.CREATEDON, DateTime.Now).Value > 6 && t.CCMEMOMASTER.CCGROUP.GROUPNAME.ToLower().Contains("check") && t.CCMEMOMASTER.CCENUMERATION.NAME.ToLower().Contains("refer") && t.CCMEMOMASTER.ISOPEN && t.CCMEMOMASTER.ISACTIVE).Select(x => x.CCMEMOMASTER).OrderByDescending(x => x.CREATEDON).Distinct().ToList();
}
else if (searchCat == "3")
{
srchTaskList.TASKTYPE = 3;
srchTaskList.TASKTYPENAME = "Total Refer Request List";
enumDBType = Convert.ToInt32(Enumaretion.DBEnumType.ActionType);
long openActionId = oDBContext.CCENUMERATIONs.FirstOrDefault(t => t.TYPE == enumDBType && t.NAME.ToLower().Contains("open")).ID;
long referActionId = oDBContext.CCENUMERATIONs.FirstOrDefault(t => t.TYPE == enumDBType && t.NAME.ToLower().Contains("refer")).ID;
srchTaskList.CCMEMOLIST = oDBContext.CCMEMOMASTERs.Where(t => (t.LASTACTIONTYPE == openActionId || t.LASTACTIONTYPE == referActionId) && t.ISOPEN && t.ISACTIVE).OrderByDescending(x => x.CREATEDON).ToList();
}
else if (searchCat == "4")
{
srchTaskList.TASKTYPE = 4;
srchTaskList.TASKTYPENAME = "Group Pending Request List";
enumDBType = Convert.ToInt32(Enumaretion.DBEnumType.ActionType);
long openActionId = oDBContext.CCENUMERATIONs.FirstOrDefault(t => t.TYPE == enumDBType && t.NAME.ToLower().Contains("open")).ID;
long referActionId = oDBContext.CCENUMERATIONs.FirstOrDefault(t => t.TYPE == enumDBType && t.NAME.ToLower().Contains("refer")).ID;
srchTaskList.CCMEMOLIST = oDBContext.CCMEMOLOGs.Where(t => !t.CCMEMOMASTER.CCGROUP.GROUPNAME.ToLower().Contains("make") && !t.CCMEMOMASTER.CCGROUP.GROUPNAME.ToLower().Contains("check") && (t.ACTIONTYPE == openActionId || t.ACTIONTYPE == referActionId) && t.CCMEMOMASTER.ISOPEN && t.CCMEMOMASTER.ISACTIVE).Select(x => x.CCMEMOMASTER).Distinct().OrderByDescending(x => x.CREATEDON).ToList();
if (grpId.HasValue)
{
srchTaskList.TASKTYPE = 4;
srchTaskList.CCMEMOLIST = srchTaskList.CCMEMOLIST.Where(t => t.ASSIGNGROUP == grpId).ToList();
srchTaskList.TASKTYPENAME = srchTaskList.CCMEMOLIST[0].CCGROUP.GROUPNAME + " Pending Request List";
}
}
return(PartialView("_requestListPartial", srchTaskList));
}
public static int CountDelayingOrders(this HtmlHelper htmlHelper, int companyId)
{
using (OrdersRepository ordersRep = new OrdersRepository(companyId))
{
DateTime CurrentTime = DateTime.Now;
return(ordersRep.GetList("Orders_Statuses", "Supplier", "User")
.Where(x =>
EntityFunctions.DiffHours(x.CreationDate, CurrentTime) >= 48 &&
x.StatusId < (int)StatusType.ApprovedPendingInvoice &&
x.StatusId != (int)StatusType.Declined &&
x.StatusId != (int)StatusType.OrderCancelled
)
.Count());
}
}
public List <Claim> TestRule(BusinessRule rule)
{
//List<BusinessRule> rules = null;
List <Claim> claims = null;
int lapsedTime = 0;
DateTime now = DateTime.Now;
//// get business rules for client/rule type id
//using (BusinessRuleManager repository = new BusinessRuleManager()) {
// rules = repository.GetBusinessRules(Globals.RuleType.ClaimAssingmentReview);
//}
XElement ruleXML = XElement.Parse(rule.RuleXML);
// get lapse time units: number of hours or days
int.TryParse(GetElementValue(ruleXML, "LapseTime"), out lapsedTime);
// get lapse time type: hours or days
string lapseTimeType = GetElementValue(ruleXML, "LapseTimeType");
Expression <Func <Claim, bool> > predicate = null;
// query filters
predicate = PredicateBuilder.True <CRM.Data.Entities.Claim>();
//predicate = predicate.And(x => x.LeadPolicy.Lead.ClientID == clientID); // claims for this client only
predicate = predicate.And(x => x.IsActive == true); // active claims
predicate = predicate.And(x => x.LastStatusUpdate != null);
predicate = predicate.And(x => x.ProgressStatusID == (int)Globals.ClaimProgressStatus.ClaimAssignedNotAcceptedYet);
// determine time lapsed
if (lapseTimeType == "1") // hours
{
predicate = predicate.And(x => EntityFunctions.DiffHours(x.LastProgressChanged, now) > lapsedTime);
}
else if (lapseTimeType == "2") // days
{
predicate = predicate.And(x => EntityFunctions.DiffDays(x.LastProgressChanged, now) > lapsedTime);
}
using (ClaimManager repository = new ClaimManager()) {
claims = repository.SearchClaim(predicate);
}
return(claims);
}
public void TimeDiffHours()
{
TimeSpan time = stored.Add(TimeSpan.FromHours(-1));
#if !EFOLD
var q = this.Entities
.Where(x =>
DbFunctions.DiffHours(time, x.Time) == 1);
#else
var q = this.Entities
.Where(x =>
EntityFunctions.DiffHours(time, x.Time) == 1);
#endif
q.Should().NotBeEmpty();
}
public void DateTimeDiffHours()
{
DateTime date = stored.AddHours(-1);
#if !EFOLD
var q = this.Entities
.Where(x =>
DbFunctions.DiffHours(date, x.DateTime) == 1);
#else
var q = this.Entities
.Where(x =>
EntityFunctions.DiffHours(date, x.DateTime) == 1);
#endif
q.Should().NotBeEmpty();
}
public void DateTimeOffsetDiffHours()
{
DateTimeOffset offset = stored.AddHours(-1);
#if !EFOLD
var q = this.Entities
.Where(x =>
DbFunctions.DiffHours(offset, x.Offset) == 1);
#else
var q = this.Entities
.Where(x =>
EntityFunctions.DiffHours(offset, x.Offset) == 1);
#endif
q.Should().NotBeEmpty();
}
public ActionResult Index()
{
var User = HttpContext.User;
var role = Roles.GetAllRoles();
int enumDBType = 0;
enumDBType = Convert.ToInt32(Enumaretion.DBEnumType.PriorityType);
long HightPriorityId = oDBContext.CCENUMERATIONs.FirstOrDefault(t => t.TYPE == enumDBType && t.NAME.ToLower().Contains("high")).ID;
enumDBType = Convert.ToInt32(Enumaretion.DBEnumType.ActionType);
long openActionId = oDBContext.CCENUMERATIONs.FirstOrDefault(t => t.TYPE == enumDBType && t.NAME.ToLower().Contains("open")).ID;
long referActionId = oDBContext.CCENUMERATIONs.FirstOrDefault(t => t.TYPE == enumDBType && t.NAME.ToLower().Contains("refer")).ID;
//ViewBag.Message = "Modify this template to jump-start your ASP.NET MVC application.";
enumDBType = Convert.ToInt32(Enumaretion.DBEnumType.TickerMsg);
CCUSER oCCUSER = (CCUSER)Session["User"];
Dashboard oDashboard = new Dashboard();
oDashboard.NewsTicker = oDBContext.CCENUMERATIONs.Where(t => t.TYPE == enumDBType).OrderByDescending(t => t.CREATEDON).ToList();
oDashboard.USERID = oCCUSER.USERID;
oDashboard.USERNAME = oCCUSER.EMPLOYEENAME;
oDashboard.GROUP = oCCUSER.CCGROUP.GROUPNAME;
oDashboard.ROLE = oCCUSER.CCROLE.ROLENAME;
oDashboard.UrgentMemo = oDBContext.CCMEMOMASTERs.Where(t => t.PRIORITY == HightPriorityId && t.ISOPEN && t.ISACTIVE).Count();
oDashboard.HourCrossedMemo = oDBContext.CCMEMOLOGs.Where(t => EntityFunctions.DiffHours(t.CREATEDON, DateTime.Now).Value > 6 && t.CCMEMOMASTER.CCGROUP.GROUPNAME.ToLower().Contains("check") && t.CCMEMOMASTER.CCENUMERATION.NAME.ToLower().Contains("refer") && t.CCMEMOMASTER.ISOPEN && t.ISACTIVE).Select(x => x.CCMEMOMASTER).OrderByDescending(x => x.CREATEDON).Distinct().Count();
oDashboard.ReferMemo = oDBContext.CCMEMOMASTERs.Where(t => (t.LASTACTIONTYPE == openActionId || t.LASTACTIONTYPE == referActionId) && t.ISOPEN && t.ISACTIVE).ToList().Count();
var OtherGroupMemo = oDBContext.CCMEMOLOGs.Where(t => !t.CCMEMOMASTER.CCGROUP.GROUPNAME.ToLower().Contains("make") && !t.CCMEMOMASTER.CCGROUP.GROUPNAME.ToLower().Contains("check") && (t.ACTIONTYPE == openActionId || t.ACTIONTYPE == referActionId) && t.CCMEMOMASTER.ISOPEN && t.CCMEMOMASTER.ISACTIVE).Select(x => x.CCMEMOMASTER).Distinct().ToList();
oDashboard.OtherGroupMemo = OtherGroupMemo.Count;
oDashboard.OtherGrpList = OtherGroupMemo.GroupBy(t => t.CCGROUP).Select(x => x).ToList().Select(x => new DashGroupTicket
{
GroupId = x.Key.ID.ToString(),
GroupName = x.Key.GROUPNAME,
SrchCategory = "4",
TotalTicketNo = x.Count().ToString()
}).ToList();
return(View(oDashboard));
}
/// <summary>
/// /获取报警信息
/// </summary>
/// <returns></returns>
public IQueryable <dynamic> GetWarningInfo(WarningQueryParam warningQueryParam)
{
var firstTime = Convert.ToDateTime(warningQueryParam.FirstTime);
var lastTime = Convert.ToDateTime(warningQueryParam.LastTime);
//获取报警信息表实体
var warningInfo = DbSession.WarningInfoDal.GetEntities(w => w.IsDeleted == false);
var query = from t1 in warningInfo
from t2 in warningInfo.GroupBy(w => w.DeviceId).Select(p => new
{
newestSubTime = p.Max(q => q.SubTime),
warningStartTime = p.Min(q => q.SubTime),
deviceId = p.Key
})
where t1.DeviceId == t2.deviceId && t1.SubTime == t2.newestSubTime
select new
{
t1.DeviceId,
t1.WarningMessage,
t2.warningStartTime,
day = EntityFunctions.DiffDays(t2.warningStartTime, t2.newestSubTime),
hour = EntityFunctions.DiffHours(t2.warningStartTime, t2.newestSubTime),
minute = EntityFunctions.DiffMinutes(t2.warningStartTime, t2.newestSubTime),
};
//按设备ID编号筛选
if (!string.IsNullOrEmpty(warningQueryParam.SchDeviceId))
{
query = query.Where(w => w.DeviceId.Contains(warningQueryParam.SchDeviceId)).AsQueryable();
}
//按报警信息筛选
if (!string.IsNullOrEmpty(warningQueryParam.SchMessage))
{
query = query.Where(w => w.WarningMessage.Contains(warningQueryParam.SchMessage)).AsQueryable();
}
//按报警时间查询
if (!string.IsNullOrEmpty(warningQueryParam.FirstTime) && !string.IsNullOrEmpty(warningQueryParam.LastTime))
{
query = query.Where(w => (w.warningStartTime > firstTime && w.warningStartTime < lastTime)).AsQueryable();
}
//总条数
warningQueryParam.Total = query.Count();
return(query);
}
//
public ActionResult GridView()
{
//return View(db.JobsAssigneds.ToList());
db.Configuration.AutoDetectChangesEnabled = false; //added line
db.Configuration.LazyLoadingEnabled = false; //added line
db.Configuration.ProxyCreationEnabled = false; //added line
//var DataSource = db.JobsAssigneds.ToList();
var DataSource = (from job in db.JobsAssigneds
join emp in db.EmployeeJobs
on job.AssignID equals emp.AssignID
select new
{
AssignID = job.AssignID,
AssignJOBNUM = job.AssignJOBNUM,
AssignCLIENT = job.AssignCLIENT,
AssignWORK = job.AssignWORK,
AssignAREA = job.AssignAREA,
AssignINSTRUCTIONS = job.AssignINSTRUCTIONS,
AssignTRUCK = job.AssignTRUCK,
TextSENT = job.TextSENT,
AssignSTARTTIME = job.AssignSTARTTIME,
AssignENDTIME = job.AssignENDTIME,
Hours = EntityFunctions.DiffHours(job.AssignSTARTTIME, job.AssignENDTIME),
EmpNAME = emp.EmpNAME
}).ToList();
ViewBag.datasource = DataSource;
var DataSource2 = (from d in db.EmployeeJobs
select new { AssignID = d.AssignID, Name = d.EmpNAME }).ToList();
ViewBag.datasource2 = DataSource2;
return(View());
}
public IQueryable <OrderToTrackDTO> GetUnDeliveredShippingInfoes(DateTime when,
bool excludeRecentlyProcessed,
IList <long> orderIds)
{
var now = when;
var preventCheckDate = when.AddHours(-24);
var toWhenExpDelDate = when.AddHours(-8);
var preventCheckDateForDelivered = when.AddDays(-7); //~once per week
//var minDate = when.AddMonths(-2);
var deliveryReCheckPeriod = when.AddDays(-3);
var minOrderDate = when.AddDays(-120); //Stop tracking old orders, when.AddYears(-1); //NOTE: Tracking number was resused by USPS
var query = from o in unitOfWork.GetSet <Order>()
join sh in unitOfWork.GetSet <OrderShippingInfo>() on o.Id equals sh.OrderId
join shM in unitOfWork.GetSet <ShippingMethod>() on sh.ShippingMethodId equals shM.Id
where sh.IsActive &&
!String.IsNullOrEmpty(sh.TrackingNumber)
orderby o.OrderDate descending
select new OrderToTrackDTO()
{
OrderId = o.Id,
ShipmentInfoId = sh.Id,
OrderNumber = o.AmazonIdentifier,
OrderDate = o.OrderDate,
OrderStatus = o.OrderStatus,
ShipmentProviderType = sh.ShipmentProviderType,
Carrier = shM.CarrierName,
ShippingName = shM.Name,
TrackingNumber = sh.TrackingNumber,
LabelCanceled = sh.LabelCanceled,
CancelLabelRequested = sh.CancelLabelRequested,
BuyDate = sh.LabelPurchaseDate,
ShippingMethodId = sh.ShippingMethodId,
EstDeliveryDate = sh.EstimatedDeliveryDate,
ActualDeliveryDate = sh.ActualDeliveryDate,
DeliveredStatus = sh.DeliveredStatus,
IsDelivered = sh.IsDelivered,
LastTrackingRequestDate = sh.LastTrackingRequestDate,
TrackingRequestAttempts = sh.TrackingRequestAttempts,
//TrackingStateType = sh.TrackingStateType,
TrackingStateDate = sh.TrackingStateDate,
TrackingStateEvent = sh.TrackingStateEvent,
TrackingLocation = sh.TrackingLocation,
ShippingAddress = new AddressDTO()
{
Address1 = o.ShippingAddress1,
Address2 = o.ShippingAddress2,
City = o.ShippingCity,
State = o.ShippingState,
Country = o.ShippingCountry,
Phone = o.ShippingPhone,
Zip = o.ShippingZip,
ZipAddon = o.ShippingZipAddon,
ManuallyAddress1 = o.ManuallyShippingAddress1,
ManuallyAddress2 = o.ManuallyShippingAddress2,
ManuallyCity = o.ManuallyShippingCity,
ManuallyState = o.ManuallyShippingState,
ManuallyCountry = o.ManuallyShippingCountry,
ManuallyPhone = o.ManuallyShippingPhone,
ManuallyZip = o.ManuallyShippingZip,
ManuallyZipAddon = o.ManuallyShippingZipAddon,
}
};
if (orderIds == null || !orderIds.Any())
{
query = query.Where(sh => sh.OrderStatus == OrderStatusEnumEx.Shipped &&
(!sh.ActualDeliveryDate.HasValue ||
sh.ActualDeliveryDate.Value > deliveryReCheckPeriod) &&
sh.OrderDate > minOrderDate);
if (excludeRecentlyProcessed)
{
query = query.Where(sh =>
(!sh.ActualDeliveryDate.HasValue &&
sh.TrackingRequestAttempts <= 250 &&
(!sh.LastTrackingRequestDate.HasValue || sh.LastTrackingRequestDate < preventCheckDate)) ||
(sh.ActualDeliveryDate.HasValue && sh.LastTrackingRequestDate < preventCheckDateForDelivered) ||
((!sh.ActualDeliveryDate.HasValue &&
sh.EstDeliveryDate.HasValue &&
(EntityFunctions.DiffHours(sh.EstDeliveryDate.Value, now) < 18 &&
EntityFunctions.DiffHours(sh.EstDeliveryDate.Value, now) > -8)) &&
(!sh.LastTrackingRequestDate.HasValue ||
sh.LastTrackingRequestDate < toWhenExpDelDate)));
}
}
else
{
query = query.Where(sh => orderIds.Contains(sh.OrderId));
}
return(query);
}
public IQueryable <OrderToTrackDTO> GetUnDeliveredMailInfoes(DateTime when,
bool excludeRecentlyProcessed,
IList <long> orderIds)
{
var now = when;
var toDate = when.AddHours(-24);
var toWhenExpDelDate = when.AddHours(-8);
var deliveredReCheckPeriod = when.AddDays(-3);
var oneYear = when.AddYears(-1); //NOTE: Tracking number was resused by USPS
var query = from o in unitOfWork.GetSet <Order>()
join mail in unitOfWork.GetSet <MailLabelInfo>() on o.Id equals mail.OrderId
join shM in unitOfWork.GetSet <ShippingMethod>() on mail.ShippingMethodId equals shM.Id
where !String.IsNullOrEmpty(mail.TrackingNumber)
select new OrderToTrackDTO()
{
OrderId = o.Id,
MailInfoId = mail.Id,
OrderNumber = o.AmazonIdentifier,
OrderDate = o.OrderDate,
OrderStatus = o.OrderStatus,
ShippingName = shM.Name,
Carrier = shM.CarrierName,
TrackingNumber = mail.TrackingNumber,
LabelCanceled = mail.LabelCanceled,
CancelLabelRequested = mail.CancelLabelRequested,
ShipmentProviderType = mail.ShipmentProviderType,
ShippingMethodId = mail.ShippingMethodId,
BuyDate = mail.BuyDate,
ReasonId = mail.ReasonId,
EstDeliveryDate = mail.EstimatedDeliveryDate,
ActualDeliveryDate = mail.ActualDeliveryDate,
DeliveredStatus = mail.DeliveredStatus,
IsDelivered = mail.IsDelivered,
LastTrackingRequestDate = mail.LastTrackingRequestDate,
TrackingRequestAttempts = mail.TrackingRequestAttempts,
TrackingStateDate = mail.TrackingStateDate,
TrackingStateEvent = mail.TrackingStateEvent,
TrackingLocation = mail.TrackingLocation,
ShippingAddress = new AddressDTO()
{
Address1 = o.ShippingAddress1,
Address2 = o.ShippingAddress2,
City = o.ShippingCity,
State = o.ShippingState,
Country = o.ShippingCountry,
Phone = o.ShippingPhone,
Zip = o.ShippingZip,
ZipAddon = o.ShippingZipAddon,
ManuallyAddress1 = o.ManuallyShippingAddress1,
ManuallyAddress2 = o.ManuallyShippingAddress2,
ManuallyCity = o.ManuallyShippingCity,
ManuallyState = o.ManuallyShippingState,
ManuallyCountry = o.ManuallyShippingCountry,
ManuallyPhone = o.ManuallyShippingPhone,
ManuallyZip = o.ManuallyShippingZip,
ManuallyZipAddon = o.ManuallyShippingZipAddon,
}
};
if (orderIds == null || !orderIds.Any())
{
query = query.Where(sh => sh.OrderStatus == OrderStatusEnumEx.Shipped &&
(!sh.ActualDeliveryDate.HasValue ||
sh.ActualDeliveryDate.Value > deliveredReCheckPeriod)
//&& (o.EstDeliveryDate <= when)
&& sh.OrderDate > oneYear);
if (excludeRecentlyProcessed)
{
query = query.Where(mail => ((mail.TrackingRequestAttempts <= 200) &&
(!mail.LastTrackingRequestDate.HasValue ||
mail.LastTrackingRequestDate < toDate)) ||
((mail.EstDeliveryDate.HasValue &&
(EntityFunctions.DiffHours(mail.EstDeliveryDate.Value, now) < 18 &&
EntityFunctions.DiffHours(mail.EstDeliveryDate.Value, now) > -8)) &&
(!mail.LastTrackingRequestDate.HasValue ||
mail.LastTrackingRequestDate < toWhenExpDelDate))
);
}
}
else
{
query = query.Where(sh => orderIds.Contains(sh.OrderId));
}
return(query);
}