public LoginInfo GetLoginInfo(Guid token)
{
return(CacheHelper.Get <LoginInfo>(string.Format(_LoginInfoKeyFormat, token), () =>
{
using (var dbContext = new AccountDbContext())
{
//如果有超时的，启动超时处理
var timeoutList = dbContext.FindAll <LoginInfo>(p => EntityFunctions.DiffMinutes(DateTime.Now, p.LastAccessTime) > _UserLoginTimeoutMinutes);
if (timeoutList.Count > 0)
{
foreach (var li in timeoutList)
{
dbContext.LoginInfos.Remove(li);
}
}
dbContext.SaveChanges();
var loginInfo = dbContext.FindAll <LoginInfo>(l => l.LoginToken == token).FirstOrDefault();
if (loginInfo != null)
{
loginInfo.LastAccessTime = DateTime.Now;
dbContext.Update <LoginInfo>(loginInfo);
}
return loginInfo;
}
}));
}
/// <summary>
/// 获取设备的实时数据
/// </summary>
/// <param name="deviceId">设备ID</param>
/// <returns>设备的具体参数</returns>
public IQueryable <dynamic> ApiGetDeviceParameter()
{
var statusFlag = StatusFlagEnum.运行中.ToString();
var deviceParameterInfo = DbSession.DeviceParameterInfoDal.GetEntities(t => true);
var deviceInfo = DbSession.DeviceInfoDal.GetEntities(d => d.IsDeleted == false);
//查询每台机器的最新一条数据（分组查询）
var query = from t1 in deviceParameterInfo
join t3 in deviceInfo on t1.DeviceInfoId equals t3.Id
from t2 in deviceParameterInfo.GroupBy(m => m.DeviceInfoId).Select(p => new
{
newestSubTime = p.Max(q => q.SubTime),
latestStartTime = p.Max(q => q.StartTime),
deviceInfoId = p.Key
})
from t4 in deviceParameterInfo
.Where(d => d.StatusFlag == statusFlag)
.GroupBy(m => m.DeviceInfoId).Select(p => new
{
newestNormalSubTime = p.Max(q => q.SubTime),
deviceInfoId = p.Key
})
where t1.DeviceInfoId == t2.deviceInfoId && t1.SubTime == t2.newestSubTime && t1.DeviceInfoId == t4.deviceInfoId
select new
{
runtime = EntityFunctions.DiffMinutes(t2.latestStartTime, t4.newestNormalSubTime),
t3.DeviceId,
t1.StatusFlag,
};
return(query);
}
public void TimeDiffMinutes()
{
TimeSpan time = stored.Add(TimeSpan.FromMinutes(-1));
#if !EFOLD
var q = this.Entities
.Where(x =>
DbFunctions.DiffMinutes(time, x.Time) == 1);
#else
var q = this.Entities
.Where(x =>
EntityFunctions.DiffMinutes(time, x.Time) == 1);
#endif
q.Should().NotBeEmpty();
}
public void DateTimeDiffMinutes()
{
DateTime date = stored.AddMinutes(-1);
#if !EFOLD
var q = this.Entities
.Where(x =>
DbFunctions.DiffMinutes(date, x.DateTime) == 1);
#else
var q = this.Entities
.Where(x =>
EntityFunctions.DiffMinutes(date, x.DateTime) == 1);
#endif
q.Should().NotBeEmpty();
}
public void DateTimeOffsetDiffMinutes()
{
DateTimeOffset offset = stored.AddMinutes(-1);
#if !EFOLD
var q = this.Entities
.Where(x =>
DbFunctions.DiffMinutes(offset, x.Offset) == 1);
#else
var q = this.Entities
.Where(x =>
EntityFunctions.DiffMinutes(offset, x.Offset) == 1);
#endif
q.Should().NotBeEmpty();
}
/// <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);
}
private static Expression <Func <ChileLotProduction, MillAndWetdownReturn> > SelectBase()
{
var lotKey = LotProjectors.SelectLotKey <ChileLotProduction>();
var productKey = ProductProjectors.SelectProductKey();
var productionLocationKey = LocationProjectors.SelectLocationKey();
return(m => new MillAndWetdownReturn
{
OutputChileLotKeyReturn = lotKey.Invoke(m),
ChileProductKeyReturn = productKey.Invoke(m.ResultingChileLot.ChileProduct.Product),
ProductionLineLocationKeyReturn = productionLocationKey.Invoke(m.Results.ProductionLineLocation),
ShiftKey = m.Results.ShiftKey,
ProductionLineDescription = m.Results.ProductionLineLocation.Description,
ChileProductName = m.ResultingChileLot.ChileProduct.Product.Name,
ProductionBegin = m.Results.ProductionBegin,
ProductionEnd = m.Results.ProductionEnd,
TotalProductionTimeMinutes = EntityFunctions.DiffMinutes(m.Results.ProductionBegin, m.Results.ProductionEnd) ?? 0,
TotalWeightProduced = m.Results.ResultItems.Any() ? (int)m.Results.ResultItems.Sum(i => i.PackagingProduct.Weight * i.Quantity) : 0,
TotalWeightPicked = m.PickedInventory.Items.Any() ? (int)m.PickedInventory.Items.Sum(i => i.PackagingProduct.Weight * i.Quantity) : 0
});
}
public ActionResult Index()
{
logger.Debug("User " + WebSecurity.GetUserId(User.Identity.Name) +
" \"" + User.Identity.Name + "\" visited AccountsManagement/Home/Index");
HomeViewModel viewModel = new HomeViewModel();
IQueryable <UserProfile> users = repository.Users;
if (!Roles.IsUserInRole("Administrator"))
{
users = users.Where(u => u.CreatedByUserID == WebSecurity.CurrentUserId);
}
//membershipService.GetAllUsers(0, 20, out totalRecords);
viewModel.TotalUserCount = users.Count(u => u.UserId != 1).ToString();
viewModel.TotalOnlineUsersCount = repository
.Users
.Where(u => u.UserId != 1)
.Count(u => EntityFunctions.DiffMinutes(u.LastAccessTime, DateTime.Now) < 10).ToString();
viewModel.TotalRolesCount = Roles.GetAllRoles().Length.ToString();
return(View(viewModel));
}
static void Main()
{
var ctx = new StudentContext();
// Task 3
//step 1 - Lists all students and their homework submissions. Select only their
//names and for each homework - content and content-type.
Console.WriteLine("Task 3\n");
var studentswithHomeworks = ctx.Homeworks.Select(h =>
new
{
StudentName = h.Student.Name,
Content = h.Content,
ContType = h.ContentType
}).GroupBy(n => n.StudentName);
foreach (var student in studentswithHomeworks)
{
Console.WriteLine("Student's name: {0}", student.Key);
foreach (var homework in student)
{
Console.WriteLine(" Homework Content: " + homework.Content + " Type: " + homework.ContType);
}
}
Console.WriteLine("\n\n");
//step 2 - List all courses with their corresponding resources. Select the
//course name and description and everything for each resource. Order the courses
//by start date (ascending), then by end date (descending).
var allCoursesWithResourses = ctx.Courses.OrderBy(c => c.StartDate)
.ThenByDescending(c => c.EndDate)
.Select(c => new
{
CourseName = c.Name,
CourseDescription = c.Description,
Resourcess = c.Resources
});
foreach (var course in allCoursesWithResourses)
{
Console.WriteLine("Course name: " + course.CourseName);
var courseDescription = course.CourseDescription == null ? "nope" : course.CourseDescription;
Console.WriteLine("Course description: " + courseDescription);
if (course.Resourcess.Count() != 0)
{
foreach (var resource in course.Resourcess)
{
Console.WriteLine("Resource name: {0}, Type: {1}, URL: {2}",
resource.Name, resource.Type, resource.URL);
}
}
else
{
Console.WriteLine("The course has no resources :) use Bat Google.");
}
Console.WriteLine();
}
Console.WriteLine("\n\n");
//step 3 - I am using more than 2 instead of more than 5, beckause my data is not enough
//List all courses with more than 5 resources. Order them by resources count (descending),
//then by start date (descending). Select only the course name and the resource count.
var coursesWithMoreThan2Resources = ctx.Courses.Where(c => c.Resources.Count >= 2)
.OrderByDescending(c => c.Resources.Count())
.ThenByDescending(c => c.StartDate).Select(c => new
{
c.Name,
ResourcesCount = c.Resources.Count()
});
foreach (var course in coursesWithMoreThan2Resources)
{
Console.WriteLine(course.Name + ": " + course.ResourcesCount);
}
Console.WriteLine("\n\n");
//step 4 - List all courses which were active on a given date (choose the date depending on the data
//seeded to ensure there are results), and for each course count the number of students enrolled.
//Select the course name, start and end date, course duration (difference between end and start date)
//and number of students enrolled. Order the results by the number of students enrolled (in descending order), then by duration (descending).
var givenDate = new DateTime(2001, 3, 5);
var allCoursesThatAreActive = ctx.Courses.Where(c => c.StartDate <= givenDate && c.EndDate >= givenDate)
.Select(c => new
{
CourseName = c.Name,
c.StartDate,
c.EndDate,
CourseDuration = EntityFunctions.DiffMinutes(c.StartDate, c.EndDate),
NumOfStudentsEnroll = c.Students.Count()
}).OrderByDescending(c => c.NumOfStudentsEnroll).ThenByDescending(c => c.CourseDuration);
foreach (var course in allCoursesThatAreActive)
{
Console.WriteLine("Course Name: {0}, Start Date: {1}, End Date {2}, Students Enrolled: {3}, Duration: {4} minutes :)\n",
course.CourseName, String.Format("{0:d/M/yyyy HH:mm:ss}", course.StartDate), String.Format("{0:d/M/yyyy HH:mm:ss}", course.EndDate), course.NumOfStudentsEnroll, course.CourseDuration);
}
Console.WriteLine("\n\n");
//step 5 - For each student, calculate the number of courses he/she has enrolled in, the total
//price of these courses and the average price per course for the student.
//Select the student name, number of courses, total price and average price. Order the results by total price
//(descending), then by number of courses (descending) and then by the student's name (ascending).
var infoAboutStudents = ctx.Students.Select(s => new
{
s.Name,
NumOFCourses = s.Courses.Count(),
TotalPricePaid = s.Courses.Sum(c => c.Price),
AvgPrice = s.Courses.Average(c => c.Price)
}).OrderByDescending(s => s.TotalPricePaid).ThenByDescending(s => s.AvgPrice);
foreach (var student in infoAboutStudents)
{
Console.WriteLine("Student Name: {0}, Num Courses: {1}, Total Price {2} lv, Avg Price: {3} lv",
student.Name, student.NumOFCourses, student.TotalPricePaid, student.AvgPrice);
}
}
public List <int>[] FindBestCustomers() // returns each customer in his/her cluster
{
try
{
List <int>[] results = new List <int> [3];
// or 4 cluster because 4th cluster has some poor customers to recommend to purchase goods
for (int i = 0; i < results.Length; i++)
{
results[i] = new List <int>();
}
ECDBEntities entity = new ECDBEntities();
int numberOfBests = (int)(SAMPLE_PERCENT * entity.CustomerTbls.Count());
// ------------------------------------- FORMULAS -------------------------------------
// Formula 1
// (1) / (3)
var idsFormula1 =
entity.CustomerTbls.OrderByDescending(
customer => (double)customer.NumberOfPurchases / (double)customer.NumberOfLogins).Select(
customer => customer.Cid).Take(numberOfBests);
// Formula 2
// 1 / (4) + (5) => max or (4) + (5) => min
var idsFormula2 =
entity.CustomerTbls.OrderBy(
customer =>
(Math.Abs((int)EntityFunctions.DiffMinutes(DateTime.Now, customer.LastLoginDate.Value)) +
Math.Abs((int)EntityFunctions.DiffMinutes(DateTime.Now, customer.LastPurchaseDate.Value)))).
Select(
customer => customer.Cid).
Take(numberOfBests);
// Formula 3
// (4) / (5) + (1) / (2)
var idsFormula3 = entity.CustomerTbls.OrderByDescending(
customer =>
Math.Abs((double)EntityFunctions.DiffMinutes(DateTime.Now, customer.LastLoginDate.Value)) /
Math.Abs((double)EntityFunctions.DiffMinutes(DateTime.Now, customer.LastPurchaseDate.Value)) +
(double)customer.NumberOfPurchases / (double)customer.LoggedInDuration).Select(
customer => customer.Cid)
.Take(
numberOfBests);
results[0].AddRange(idsFormula1.Intersect(idsFormula2).Intersect(idsFormula3));
results[1].AddRange(
idsFormula1.Intersect(idsFormula2).Union(idsFormula1.Intersect(idsFormula3)).Union(
idsFormula2.Intersect(idsFormula3)).Except(results[0]));
results[2].AddRange(
idsFormula1.Union(idsFormula2).Union(idsFormula3).Except(results[0]).Except(results[1]));
// ------------------- Working With Database's Best Customers Table -------------------
// Removing Last Data From Best Customers Table
entity.ExecuteStoreCommand("DELETE BestCustomersTbls");
// Adding Better Customers To Best Customers Table))
for (int i = 0; i < results.Length; i++)
{
foreach (var cid in results[i])
{
BestCustomersTbl bc = new BestCustomersTbl();
bc.ClusterId = i + 1;
bc.Cid = cid;
entity.AddToBestCustomersTbls(bc);
}
}
entity.SaveChanges();
return(results);
}
catch (Exception ex)
{
MySoapFault fault = new MySoapFault();
fault.Operation = "FindBestCustomers";
fault.Reason = "Error in finding best customers occurred .";
fault.Details = ex.Message;
fault.MoreDetails = ex.StackTrace;
throw new FaultException <MySoapFault>(fault);
}
}
public ActionResult ReportAbsensiKaryawan(DateTime startDate, DateTime endDate, int?Id = null, int ChartType = 0)
{
DateTime endDay = endDate.Date.AddDays(1);
var company = _companyInfoService.GetQueryable().FirstOrDefault();
var q = _employeeAttendanceService.GetQueryable().Include("Employee").Where(x => (Id == null || Id.Value == 0 || x.EmployeeId == Id.Value) && EntityFunctions.TruncateTime(x.AttendanceDate) >= startDate.Date && EntityFunctions.TruncateTime(x.AttendanceDate) < endDay);
string user = AuthenticationModel.GetUserName();
CultureInfo ci = new CultureInfo("id-ID");
var query = (from model in q
select new
{
StartDate = EntityFunctions.TruncateTime(startDate).Value,
EndDate = EntityFunctions.TruncateTime(endDate).Value,
AttDate = model.AttendanceDate,
model.Employee.NIK,
model.Employee.Name,
title = model.Employee.TitleInfo.Name,
division = model.Employee.Division.Name,
department = model.Employee.Division.Department.Name,
branch = model.Employee.Division.Department.BranchOffice.Name,
companyname = company.Name,
mode = ChartType,
work = (model.CheckOut == null) ? 0 : EntityFunctions.DiffMinutes(model.CheckIn, model.CheckOut.Value).Value - model.BreakMinutes, // model.WorkTimeMinutes
early = model.CheckEarlyMinutes,
late = model.CheckLateMinutes,
//User = user,
}).OrderBy(x => x.NIK).ThenByDescending(x => x.AttDate).ToList();
if (!query.Any())
{
return(Content(Core.Constants.Constant.ErrorPage.RecordNotFound));
}
var rd = new ReportDocument();
//Loading Report
rd.Load(Server.MapPath("~/") + "Reports/General/rpt_absensi_karyawan.rpt");
// Setting report data source
rd.SetDataSource(query);
// Set parameters, need to be done after all data sources are set (to prevent reseting parameters)
//rd.SetParameterValue(0, "Periode " + monthYear.ToString("MMMM yyyy", ci).ToUpper());
// Set printer paper size
//System.Drawing.Printing.PrintDocument doctoprint = new System.Drawing.Printing.PrintDocument();
//doctoprint.PrinterSettings.PrinterName = "Microsoft XPS Document Writer";
//int i = 0;
//for (i = 0; i < doctoprint.PrinterSettings.PaperSizes.Count; i++)
//{
// int rawKind = 0;
// if (doctoprint.PrinterSettings.PaperSizes[i].PaperName.ToLower() == "Letter")
// {
// rawKind = Convert.ToInt32(doctoprint.PrinterSettings.PaperSizes[i].GetType().GetField("kind", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic).GetValue(doctoprint.PrinterSettings.PaperSizes[i]));
// if (Enum.IsDefined(typeof(CrystalDecisions.Shared.PaperSize), rawKind))
// {
// rd.PrintOptions.PaperSize = (CrystalDecisions.Shared.PaperSize)rawKind;
// //rd.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
// }
// break;
// }
//}
var stream = rd.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
return(File(stream, "application/pdf"));
}
public List <WorkingPerformance> WorkingPerformanceData()
{
try
{
List <WorkingPerformance> lstWorkingPerformance = new List <WorkingPerformance>();
using (var db = new DBContext())
{
DateTime startDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
DateTime endDate = startDate.AddMonths(1).AddSeconds(-1);
int nMachineDetailId = (Session["sMachineDetailId"] == null || Session["sMachineDetailId"].ToString() == "") ? 0 : Convert.ToInt32(Session["sMachineDetailId"]);
if (nMachineDetailId > 0)
{
ViewBag.macName = (db.MachineDetails.Where(x => x.MachineDetailId == nMachineDetailId).Select(x => x.MachineName)).ToList().FirstOrDefault();
ViewBag.UserName = (db.MachineDetails.Where(x => x.MachineDetailId == nMachineDetailId).Select(x => x.UserName)).ToList().FirstOrDefault();
}
else
{
ViewBag.macName = "";
}
int customerId = Utility.GetCustomerId();
var dates = new List <DateTime>();
dates = Enumerable.Range(1, DateTime.DaysInMonth(startDate.Year, startDate.Month)) // Days: 1, 2 ... 31 etc.
.Select(day => new DateTime(startDate.Year, startDate.Month, day)) // Map each day to a date
.ToList(); // Load dates into a list
TimeSpan lunchStartTime = TimeSpan.Zero;
TimeSpan lunchEndTime = TimeSpan.Zero;
TimeSpan shiftStartTime = TimeSpan.Zero;
TimeSpan shiftEndTime = TimeSpan.Zero;
var groupTime = (from grp in db.Groups
join mg in db.MachineGroupings on grp.GroupId equals mg.GroupId
where mg.MachineDetailId == nMachineDetailId
select new
{
grp.ShiftStartTime,
grp.ShiftEndTime,
grp.LunchStartTime,
grp.LunchEndTime
}).ToList();
if (groupTime != null)
{
foreach (var grptime in groupTime)
{
lunchStartTime = DateTime.ParseExact(grptime.LunchStartTime, "HH:mm", CultureInfo.InvariantCulture).TimeOfDay;
lunchEndTime = DateTime.ParseExact(grptime.LunchEndTime, "HH:mm", CultureInfo.InvariantCulture).TimeOfDay;
shiftStartTime = DateTime.ParseExact(grptime.ShiftStartTime, "HH:mm", CultureInfo.InvariantCulture).TimeOfDay;
shiftEndTime = DateTime.ParseExact(grptime.ShiftEndTime, "HH:mm", CultureInfo.InvariantCulture).TimeOfDay;
}
}
TimeSpan t = (shiftEndTime - shiftStartTime) - (lunchEndTime - lunchStartTime);
var data = (from d in dates
select new
{
dateOfMonth = d.Day.ToString(),
workingTime = ((from ms in db.MachineSessions
where ms.MachineDetailId == nMachineDetailId && EntityFunctions.TruncateTime(ms.SessionStart) == EntityFunctions.TruncateTime(d.Date)
select EntityFunctions.DiffMinutes(ms.SessionStart, ms.SessionEnd)).ToList().Sum()
- (db.MachineIdles.Where(x => EntityFunctions.TruncateTime(x.CreatedDate) == EntityFunctions.TruncateTime(d.Date) && x.MachineDetailId == nMachineDetailId).Select(k => k.IdleTime).ToList().FirstOrDefault()) / 60) < 0 ? 0 :
((from ms in db.MachineSessions
where ms.MachineDetailId == nMachineDetailId && EntityFunctions.TruncateTime(ms.SessionStart) == EntityFunctions.TruncateTime(d.Date)
select EntityFunctions.DiffMinutes(ms.SessionStart, ms.SessionEnd)).ToList().Sum()
- (db.MachineIdles.Where(x => EntityFunctions.TruncateTime(x.CreatedDate) == EntityFunctions.TruncateTime(d.Date) && x.MachineDetailId == nMachineDetailId).Select(k => k.IdleTime).ToList().Sum()) / 60),
idleTime = (db.MachineIdles.Where(x => EntityFunctions.TruncateTime(x.CreatedDate) == EntityFunctions.TruncateTime(d.Date) && x.MachineDetailId == nMachineDetailId).Select(
k => k.IdleTime).ToList().Sum()) / 60,
groupTime = t.TotalMinutes,
}).ToList();
lstWorkingPerformance = data.ToList().Select(r => new WorkingPerformance
{
dateOfMonth = r.dateOfMonth,
workingTime = Convert.ToString(r.workingTime),
idleTime = Convert.ToString(r.idleTime),
groupTime = Convert.ToString(r.groupTime),
}).ToList();
}
return(lstWorkingPerformance);
}
catch (Exception e)
{
ExceptionHandler handler = new ExceptionHandler();
handler.HandleException(e);
}
return(null);
}