static SOM CreateSomModel_v3(string tagname, DayOfWeek day)
{
int samples = 24 * 10;
var context = new TagDbContext();
double[] data = context.TagValues
.Where(t => t.Tag.TagName == tagname && (SqlFunctions.DatePart("weekday", t.DateTime) - 1) == (int)day)
.OrderByDescending(v => v.DateTime)
.Select(v => v.Value)
.Take(samples + retrospective + 1)
.AsEnumerable()
.Reverse()
.ToArray();
var inputs = new Vector[samples];
for (int i = 0; i < samples; i++)
{
inputs[i] = new Vector();
inputs[i].Input = data.Skip(i).Take(retrospective).ToArray();
// set output
inputs[i].Output = new double[] { data[retrospective + i + 1] };
}
SOM model = new SOM(5, 1, 12, 12);
model.Train(inputs, 500);
return(model);
}
public void SetSoldGoods()
{
kitchenOrders = (from ordr in db.KitchenOrders
where
SqlFunctions.DatePart("day", ordr.Order.Date) == SqlFunctions.DatePart("day", dt) &&
SqlFunctions.DatePart("month", ordr.Order.Date) == SqlFunctions.DatePart("month", dt) &&
SqlFunctions.DatePart("year", ordr.Order.Date) == SqlFunctions.DatePart("year", dt)
group ordr by new
{
ordr.Dish.Name,
ordr.Dish.Price
} into ord
select new ProductModel()
{
Name = ord.Key.Name,
Number = ord.Count(),
Cost = ord.Key.Price
}).ToList();
barOrders = (from ordr in db.BarOrders
where
SqlFunctions.DatePart("day", ordr.Order.Date) == SqlFunctions.DatePart("day", dt) &&
SqlFunctions.DatePart("month", ordr.Order.Date) == SqlFunctions.DatePart("month", dt) &&
SqlFunctions.DatePart("year", ordr.Order.Date) == SqlFunctions.DatePart("year", dt)
group ordr by new
{
ordr.Drink.Name,
ordr.Drink.Price
} into ord
select new ProductModel()
{
Name = ord.Key.Name,
Number = ord.Count(),
Cost = ord.Key.Price
}).ToList();
}
public List <ThoiDiemSV_ThamGiaDTO> getSV_TG()
{
List <ThoiDiemSV_ThamGiaDTO> thoiDiemSV_ThamGiaDTOs = new List <ThoiDiemSV_ThamGiaDTO>();
thoiDiemSV_ThamGiaDTOs = (from tdtt in db.THOIDIEM_SV_THAMGIA
from tg in db.THOIGIAN_XET
from dv in db.DON_VI
from sv in db.SINH_VIEN
where tdtt.Mssv == sv.Mssv && sv.DonVi == dv.MaDonVi && tdtt.MaThoiGian == tg.MaThoiGian
select new ThoiDiemSV_ThamGiaDTO
{
Mssv = tdtt.Mssv,
TenSinhVien = sv.HoTen,
Lop = sv.Lop,
DonVi = dv.MaDonVi,
ThoiDiemDK = tdtt.ThoiGian_DK,
ThoiGian = string.Concat(
SqlFunctions.DatePart("day", tg.TuNgay).ToString().Trim() + "/" +
SqlFunctions.DatePart("month", tg.TuNgay).ToString().Trim() + "/" +
SqlFunctions.DatePart("year", tg.TuNgay).ToString().Trim(), "_",
SqlFunctions.DatePart("day", tg.DenNgay).ToString().Trim() + "/" +
SqlFunctions.DatePart("month", tg.DenNgay).ToString().Trim() + "/" +
SqlFunctions.DatePart("year", tg.DenNgay).ToString().Trim())
}).ToList();
return(thoiDiemSV_ThamGiaDTOs);
}
public object GetAvailableMonth(int?permitteeId, int?year)
{
var model = _unitOfWork.ProductionsRepo.Fetch();
if (permitteeId > 0)
{
model = model.Where(x => x.PermiteeId == permitteeId);
}
if (year > 0)
{
model = model.Where(x => SqlFunctions.DatePart("year", x.ProductionDate) == year);
}
var month = model
.GroupBy(x => SqlFunctions.DatePart("month", x.DateCreated) ?? 0)
.Where(x => x.Key != 0)
.Select(x => x.Key).ToList();
return(month.Select(x => new
{
MonthName = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(x),
Month = x
}).ToList());
}
/// <summary>
/// This method fires when the data source for the employee DevExpress GridView is selecting
/// and it handles the select
/// </summary>
/// <param name="sender">The efEmployeeDataSource control</param>
/// <param name="e">The LinqServerModeDataSourceSelectEventArgs event</param>
protected void efEmployeeDataSource_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e)
{
//Set the key expression
e.KeyExpression = "ProgramEmployeePK";
//Set the source to a LINQ query
PyramidContext context = new PyramidContext();
e.QueryableSource = (from pe in context.ProgramEmployee.AsNoTracking().Include(pe => pe.CodeTermReason).Include(pe => pe.Program)
join jf in context.JobFunction.AsNoTracking().Include(jf => jf.CodeJobType) on pe.ProgramEmployeePK equals jf.ProgramEmployeeFK into jobFunctions
join t in context.Training.AsNoTracking().Include(t => t.CodeTraining) on pe.ProgramEmployeePK equals t.ProgramEmployeeFK into trainings
where currentProgramRole.ProgramFKs.Contains(pe.ProgramFK)
select new
{
pe.ProgramEmployeePK,
Name = pe.FirstName + " " + pe.LastName,
pe.EmailAddress,
JobFunctions = from jf in jobFunctions where jf.EndDate.HasValue == false select jf.CodeJobType.Description,
Trainings = from t in trainings where t.TrainingCodeFK == (int)Utilities.TrainingFKs.INTRODUCTION_TO_COACHING ||
t.TrainingCodeFK == (int)Utilities.TrainingFKs.PRACTICE_BASED_COACHING ||
t.TrainingCodeFK == (int)Utilities.TrainingFKs.TPITOS_OBSERVER ||
t.TrainingCodeFK == (int)Utilities.TrainingFKs.TPOT_OBSERVER
orderby t.TrainingDate ascending
select(t.CodeTraining.Abbreviation + ": " + SqlFunctions.DatePart("month", t.TrainingDate) + "/" + SqlFunctions.DatePart("day", t.TrainingDate) + "/" + SqlFunctions.DatePart("year", t.TrainingDate)),
pe.HireDate,
pe.TermDate,
TermReason = pe.CodeTermReason.Description + " " + (pe.TermReasonSpecify == null ? "" : "(" + pe.TermReasonSpecify + ")"),
pe.ProgramFK,
pe.Program.ProgramName
});
}
public async Task <List <int> > GetYearsForSiteMap()
{
return(await
_context.Articles.GroupBy(g => SqlFunctions.DatePart("Year", g.DatePublish))
.Select(s => s.Key.Value)
.ToListAsync());
}
public static IQueryable <WeeklyQuantity> GetWeeklyQuantities(IQueryable <SubReport> subreports, int weekStart)
{
var q1 = from sr in subreports
from cr in sr.ClientReports
from ar in cr.ClientAmountReports
group ar by new { cr.ClientId, ar.ReportDate } into g
select new
{
g.Key.ClientId,
g.Key.ReportDate,
Quantity = g.Sum(f => f.Quantity),
}
into ar
let wd = SqlFunctions.DatePart("weekday", ar.ReportDate)
let ws = weekStart
let weekStartDate = wd < ws?
SqlFunctions.DateAdd("day", ws - wd - 7, ar.ReportDate) :
SqlFunctions.DateAdd("day", ws - wd, ar.ReportDate)
let weekEndDate = SqlFunctions.DateAdd("week", 1, weekStartDate)
select new WeeklyQuantity
{
ClientId = ar.ClientId,
ReportDate = ar.ReportDate,
WeekStart = weekStartDate,
WeekEnd = weekEndDate,
Quantity = ar.Quantity,
};
return(q1);
}
private void UpdateDataGridView()
{
var result = _context.Income.Where(income => income.UserId == userId);
if (_HideTransactionsOlderThanWeek)
{
result = result.Where(income => (SqlFunctions.DatePart("dayofyear", DateTime.Now) - SqlFunctions.DatePart("dayofyear", income.IncomeDate)) >= 0);
}
if (_HideTransactionsOlderThanMonth)
{
result = result.Where(income => (SqlFunctions.DatePart("dayofyear", DateTime.Now) - SqlFunctions.DatePart("dayofyear", income.IncomeDate)) >= 0);
}
if (_HideTransactionsOlderThan3Months)
{
result = result.Where(income => (SqlFunctions.DatePart("dayofyear", DateTime.Now) - SqlFunctions.DatePart("dayofyear", income.IncomeDate)) >= 0);
}
if (_HideTransactionsOlderThan12Months)
{
result = result.Where(income => (SqlFunctions.DatePart("dayofyear", DateTime.Now) - SqlFunctions.DatePart("dayofyear", income.IncomeDate)) >= 0);
}
if (_ShowEntirePeriod)
{
result = result.Where(income => (SqlFunctions.DatePart("dayofyear", DateTime.Now) - SqlFunctions.DatePart("dayofyear", income.IncomeDate)) >= 0);
}
IncomesDataGridView.DataSource = result.ToList();
}
public IEnumerable <ReleaseView> GetReleaseJoinAccount()
{
var Query = DbSet.AsNoTracking()
.Include(r => r.Account)
.Include(r => r.ReleaseStakeholders)
.Include(r => r.ReleaseStakeholders.Select(rsh => rsh.Stakeholder))
.Include(r => r.ReleaseStakeholders.Select(rsh => rsh.Employee1))
.Include(r => r.ReleaseMilestones).OrderBy(r => r.ReleaseID)
.Select(r => new ReleaseView()
{
ReleaseName = r.Name,
AccountName = r.Account != null ? r.Account.Name : String.Empty,
ReleaseID = r.ReleaseID,
PrepFPName = r.Account != null ? r.Account.PrepFPName : String.Empty,
PrepReviewMode = "Full",
// PREP.DAL.Models.Employee SPNameEmployeeTmp = r.ReleaseStakeholders.Any(rsh => rsh.StakeholderID == 6) ? r.ReleaseStakeholders.Where(rsh => rsh.StakeholderID == 6).FirstOrDefault().Employee1 : null;
SPNameEmployee =
r.ReleaseStakeholders.Any(rsh => rsh.StakeholderID == 6) &&
r.ReleaseStakeholders.Where(rsh => rsh.StakeholderID == 6).FirstOrDefault().Employee1 != null ?
r.ReleaseStakeholders.Where(rsh => rsh.StakeholderID == 6).FirstOrDefault().Employee1.FirstName + " " + r.ReleaseStakeholders.Where(rsh => rsh.StakeholderID == 6).FirstOrDefault().Employee1.LastName : String.Empty,
ProgramMeEmployee =
r.ReleaseStakeholders.Any(rsh => rsh.StakeholderID == 7) &&
r.ReleaseStakeholders.Where(rsh => rsh.StakeholderID == 7).FirstOrDefault().Employee1 != null ?
r.ReleaseStakeholders.Where(rsh => rsh.StakeholderID == 7).FirstOrDefault().Employee1.FirstName + " " + r.ReleaseStakeholders.Where(rsh => rsh.StakeholderID == 6).FirstOrDefault().Employee1.LastName : String.Empty,
ProductionStartDate = r.ReleaseMilestones.Any(m => m.MilestoneID == 12) ?
(SqlFunctions.StringConvert((double)SqlFunctions.DatePart("MM", r.ReleaseMilestones.Where(m => m.MilestoneID == 12).FirstOrDefault().MilestoneDate)).Trim() + "-" + SqlFunctions.DateName("dd", r.ReleaseMilestones.Where(m => m.MilestoneID == 12).FirstOrDefault().MilestoneDate).Trim() + "-" + SqlFunctions.DateName("yyyy", r.ReleaseMilestones.Where(m => m.MilestoneID == 12).FirstOrDefault().MilestoneDate).Trim())
: String.Empty
}
);
return(Query);
}
public List <DiscountAndMiscReportModels> GetReceiptDiscountAndMisc(DateTime fromDate, DateTime toDate, List <string> StoreIds, int mode)
{
using (var cxt = new NuWebContext())
{
var lstData = new List <DiscountAndMiscReportModels>();
var query = (from m in cxt.R_DiscountAndMiscReport
where StoreIds.Contains(m.StoreId) && m.CreatedDate >= fromDate && m.CreatedDate <= toDate &&
m.Mode == mode
group m by new
{
Hour = (int?)SqlFunctions.DatePart("HH", m.CreatedDate),
StoreId = m.StoreId,
//Date = DbFunctions.TruncateTime(m.CreatedDate)
} into g
select g).ToList();
if (query != null && query.Any())
{
lstData = query.Select(g => new DiscountAndMiscReportModels
{
StoreId = g.Key.StoreId,
Hour = g.Key.Hour.Value,
TimeSpanHour = new TimeSpan(g.Key.Hour.Value, 0, 0),
//CreatedDate = g.Key.Date.Value,
DiscountValue = g.Sum(ss => ss.DiscountValue),
MiscValue = g.Sum(ss => ss.MiscValue)
}).ToList();
}
return(lstData);
}
}
public List <DiscountAndMiscReportModels> GetReceiptDiscountAndMiscByDateTime(DateTime fromDate, DateTime toDate, List <string> StoreIds, int mode)
{
using (var cxt = new NuWebContext())
{
var lstData = new List <DiscountAndMiscReportModels>();
var query = (from ps in cxt.R_PosSale
from psd in cxt.R_PosSaleDetail.Where(ww => ww.StoreId == ps.StoreId && ww.OrderId == ps.OrderId)
where StoreIds.Contains(ps.StoreId) && ps.ReceiptCreatedDate >= fromDate && ps.ReceiptCreatedDate <= toDate &&
psd.Mode == mode
group psd by new
{
Date = DbFunctions.TruncateTime(ps.ReceiptCreatedDate),
Hour = (int?)SqlFunctions.DatePart("HH", ps.ReceiptCreatedDate),
StoreId = ps.StoreId,
} into g
select g).ToList();
if (query != null && query.Any())
{
lstData = query.Select(g => new DiscountAndMiscReportModels
{
StoreId = g.Key.StoreId,
Hour = g.Key.Hour.Value,
TimeSpanHour = new TimeSpan(g.Key.Hour.Value, 0, 0),
CreatedDate = g.Key.Date.Value,
DiscountValue = g.Where(ww => ww.IsDiscountTotal != true).Sum(ss => ss.Discount),
MiscValue = g.Where(ww => ww.ItemTypeId == (byte)Commons.EProductType.Misc).Sum(ss => ss.Price)
}).ToList();
}
return(lstData);
}
}
public ActionResult TotalJson()
{
User user = HttpContext.CurrentUser();
Func <int, int?, IQueryable <Payroll> > query = (y, m) => db.Payrolls.Where(val => SqlFunctions.DatePart("YEAR", val.Date) == y && (!m.HasValue || SqlFunctions.DatePart("MONTH", val.Date) == m));
string[] months = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.MonthNames;
List <int> years = db.Payrolls.Select(val => SqlFunctions.DatePart("YEAR", val.Date)).Distinct().OrderByDescending(val => val).Select(val => val.Value).ToList();
for (int i = years.Any() ? years.First() : DateTime.Today.Year; i < DateTime.Today.Year + 2; i++)
{
if (!years.Contains(i))
{
years.Add(i);
}
}
years = years.OrderByDescending(val => val).ToList();
object data = new
{
Years = years.Select(val => new
{
Year = val,
Months = Enumerable.Range(1, 12).Select(val2 => new
{
Year = val,
Name = months[val2 - 1],
Month = val2,
Total = (query(val, val2).Any() ? query(val, val2).Sum(val3 => val3.Amount) : 0)
})
}),
};
return(Json(data));
}
private async Task ValidateSingleJoinAsync(EventJoinValidationDto validationDto, int orgId, string userId)
{
if (validationDto.SelectedOptions.All(x => x.Rule == OptionRules.IgnoreSingleJoin) &&
validationDto.SelectedOptions.Count != 0 ||
!validationDto.IsSingleJoin)
{
return;
}
var query = _eventsDbSet
.Include(e => e.EventParticipants.Select(x => x.EventOptions))
.Include(e => e.EventType)
.Where(e =>
e.OrganizationId == orgId &&
e.Id != validationDto.Id &&
SqlFunctions.DatePart(WeekOfYear, e.StartDate) == SqlFunctions.DatePart(WeekOfYear, validationDto.StartDate) &&
e.StartDate.Year == validationDto.StartDate.Year &&
e.EventParticipants.Any(p => p.ApplicationUserId == userId &&
p.AttendStatus == (int)AttendingStatus.Attending));
query = string.IsNullOrEmpty(validationDto.SingleJoinGroupName)
? query.Where(x => x.EventType.Id == validationDto.EventTypeId)
: query.Where(x => x.EventType.SingleJoinGroupName == validationDto.SingleJoinGroupName);
var anyEventsAlreadyJoined = await query.AnyAsync(x => !x.EventParticipants.Any(y =>
y.ApplicationUserId == userId &&
y.EventOptions.All(z => z.Rule == OptionRules.IgnoreSingleJoin) &&
y.EventOptions.Count > 0));
_eventValidationService.CheckIfUserExistsInOtherSingleJoinEvent(anyEventsAlreadyJoined);
}
public List <rptProductionRuntime> GetProductionRunTimeReport(ReportParameters para)
{
IQueryable <TransactionLog> result = GetFilteredTransactionLog(para);
var data = result
.Where(p => p.ClientId == para.ClientId)
.GroupBy(p => new {
p.MachineName,
TransDate = (DateTime)DbFunctions.TruncateTime(p.TransactionDate),
TransHour = (int)SqlFunctions.DatePart("hour", p.TransactionDate)
}, (key, group) => new
{
MachineName = key.MachineName,
TransDate = key.TransDate,
TransHour = key.TransHour,
Result = group.ToList()
})
.OrderBy(p => p.MachineName).ThenBy(p => p.TransDate).ThenBy(p => p.TransHour)
.Select(s => new rptProductionRuntime
{
MachineName = s.MachineName,
TransDate = s.TransDate,
TransHour = s.TransHour,
TotalUnits = s.Result.Count()
}).ToList();
return(data.ToList());
}
public void SetWaiters()
{
Waiters = new List <Waiter>();
Waiters.AddRange((from ordr in db.Orders
where
SqlFunctions.DatePart("day", ordr.Date) == SqlFunctions.DatePart("day", dt) &&
SqlFunctions.DatePart("month", ordr.Date) == SqlFunctions.DatePart("month", dt) &&
SqlFunctions.DatePart("year", ordr.Date) == SqlFunctions.DatePart("year", dt)
group ordr by new
{
ordr.Person.FirstName,
ordr.Person.LastName,
ordr.Person.Phone,
ordr.Person.Salary.Size
} into ord
select new Waiter()
{
FirstName = ord.Key.FirstName,
LastName = ord.Key.LastName,
Phone = ord.Key.Phone,
Salary = ord.Key.Size,
OrdersCount = ord.Count()
}).ToList());
Waiters.ForEach(w => OrdersCount += w.OrdersCount);
}
public static void ForecastAndExport_v3(string forecast_tag, string data_tag, int forecastPerspective = 2)
{
string tag_name = forecast_tag;
double[] vMax, vMin;
DateTime fdate = DateTime.Today.AddDays(forecastPerspective); // дата прогноза
SOM model = CreateSomModel(data_tag, out vMax, out vMin);
Dictionary <DateTime, double> solution = new Dictionary <DateTime, double>(); // прогноз
TagDbContext context = new TagDbContext();
var data = context.TagValues
.Where(t => t.Tag.TagName.Trim() == data_tag)
.OrderByDescending(v => v.DateTime)
.Select(v => new { v.DateTime.Hour, Day = SqlFunctions.DatePart("weekday", v.DateTime) + 1, Value = v.Value })
.Take(24 + 3 + 1)
.AsEnumerable()
.Reverse()
.ToArray();
for (int index = 0; index < 24; index++)
{
var vector = new double[] {
(double)(vMax[0] - (double)data[index].Hour) / (vMax[0] - vMin[0]),
(double)(vMax[1] - (double)data[index].Day) / (vMax[1] - vMin[1]),
(double)(vMax[2] - data[index + 1].Value) / (vMax[2] - vMin[2]),
(double)(vMax[3] - data[index + 2].Value) / (vMax[3] - vMin[3]),
(double)(vMax[4] - data[index + 3].Value) / (vMax[4] - vMin[4])
};
var value = model.Compute(vector)[0];
solution[fdate.AddHours(data[index].Hour)] = value;
}
ExportToDB(tag_name, solution, context);
}
static void RunExample()
{
using (var context = new EFRecipesEntities())
{
var app1 = new Appointment {
StartsAt = DateTime.Parse("4/7/2010 14:00"), GoesTo = DateTime.Parse("4/7/2010 15:00")
};
var app2 = new Appointment {
StartsAt = DateTime.Parse("4/8/2010 9:00"), GoesTo = DateTime.Parse("4/8/2010 11:00")
};
var app3 = new Appointment {
StartsAt = DateTime.Parse("4/8/2010 13:00"), GoesTo = DateTime.Parse("4/7/2010 15:00")
};
context.Appointments.AddObject(app1);
context.Appointments.AddObject(app2);
context.Appointments.AddObject(app3);
context.SaveChanges();
}
using (var context = new EFRecipesEntities())
{
var apps = from a in context.Appointments
where SqlFunctions.DatePart("WEEKDAY", a.StartsAt) == 5
select a;
Console.WriteLine("Appointments for Thursday");
Console.WriteLine("=========================");
foreach (var appointment in apps)
{
Console.WriteLine("Appointment from {0} to {1}", appointment.StartsAt.ToShortTimeString(), appointment.GoesTo.ToShortTimeString());
}
}
Console.WriteLine("Press <enter> to continue...");
Console.ReadLine();
}
public GetScheduleByUserResponse GetScheduleByUser(GetScheduleByUserRequest request)
{
GetScheduleByUserResponse response = new GetScheduleByUserResponse();
var scheduleList = new List <ScheduleDto>();
if (request.RoleName.Equals("student"))
{
scheduleList = _context.Schedules.Where(s => s.Student.StudentId == request.UserId && s.TermId == request.TermId && s.CourseId == request.CourseId)
.Select(s => new ScheduleDto()
{
ScheduleId = s.ScheduleId,
Date = SqlFunctions.DateName("day", s.Date) + "/" + SqlFunctions.DateName("month", s.Date) + "/" + SqlFunctions.DateName("year", s.Date),
SlotNo = s.SlotId,
TeacherId = s.TeacherId,
ClassId = s.ClassId,
AttendanceStatus = s.AttendanceStatus,
ReportStatus = s.ReportStatus
}).ToList();
}
else if (request.RoleName.Equals("teacher"))
{
scheduleList = _context.Schedules.Where(s => s.TeacherId == request.UserId && s.TermId == request.TermId && s.CourseId == request.CourseId)
.GroupBy(g => new { g.Date, g.SlotId, g.TeacherId, g.ClassId })
.Select(s => new ScheduleDto()
{
ScheduleId = s.Select(c => c.ScheduleId).FirstOrDefault(),
Date = SqlFunctions.DateName("day", s.Key.Date) + "/" + SqlFunctions.DatePart("mm", s.Key.Date) + "/" + SqlFunctions.DateName("year", s.Key.Date),
SlotNo = s.Key.SlotId,
TeacherId = s.Key.TeacherId,
ClassId = s.Select(c => c.ClassId).FirstOrDefault()
}).ToList();
}
response.Schedules = scheduleList;
return(response);
}
public static void ForecastAndExport_v4(string forecast_tag, string data_tag, int forecastPerspective = 2)
{
string tag_name = forecast_tag;
DateTime fdate = DateTime.Today.AddDays(forecastPerspective); // дата прогноза
List <double> input = new List <double>(); // входной вектор
Dictionary <DateTime, double> solution = new Dictionary <DateTime, double>(); // прогноз
var context = new TagDbContext();
var model = CreateSomModel_v2(data_tag);
var data = context.TagValues
.Where(t => t.Tag.TagName.Trim() == data_tag && (SqlFunctions.DatePart("weekday", t.DateTime) - 1) == (int)fdate.DayOfWeek)
.OrderBy(v => v.DateTime)
.Select(v => v.Value)
.Take(24 + retrospective + 1)
.AsEnumerable()
.Reverse()
.ToArray();
for (int i = 0; i < 24; i++)
{
var vector = data.Skip(i).Take(retrospective).ToArray();
var value = model.Compute(vector)[0];
solution[fdate.AddHours(i + 1)] = value;
}
ExportToDB(tag_name, solution, context);
}
public async Task <bool> AnyEventsThisWeekByTypeAsync(IEnumerable <int> eventTypeIds)
{
return(await _eventsDbSet
.AnyAsync(x => SqlFunctions.DatePart("wk", x.StartDate) == SqlFunctions.DatePart("wk", DateTime.UtcNow) &&
eventTypeIds.Contains(x.EventType.Id) &&
x.RegistrationDeadline > DateTime.UtcNow));
}
public List <QuyDinhDiemDTO> getQDD()
{
List <QuyDinhDiemDTO> quyDinhDiemDTOs = new List <QuyDinhDiemDTO>();
quyDinhDiemDTOs = (from qd in db.QUYDINH_DIEM
from tg in db.THOIGIAN_XET
from dv in db.DON_VI
from ld in db.LOAI_DIEM
from tc in db.TIEU_CHUAN
where qd.MaLoaiDiem == ld.MaLoaiDiem && qd.MaDonVi == dv.MaDonVi && qd.Mathoigian == tg.MaThoiGian && qd.MaTieuChuan == tc.MaTieuChuan
select new QuyDinhDiemDTO
{
MaQuyDinhDiem = qd.MaQuyDinhDiem,
TenLoaiDiem = ld.TenLoaiDiem,
DiemToiThieu = qd.DiemToiThieu,
DonVi = qd.MaDonVi,
TenTieuChuan = tc.TenTieuChuan,
ThoiGian = string.Concat(
SqlFunctions.DatePart("day", tg.TuNgay).ToString().Trim() + "/" +
SqlFunctions.DatePart("month", tg.TuNgay).ToString().Trim() + "/" +
SqlFunctions.DatePart("year", tg.TuNgay).ToString().Trim(), "_",
SqlFunctions.DatePart("day", tg.DenNgay).ToString().Trim() + "/" +
SqlFunctions.DatePart("month", tg.DenNgay).ToString().Trim() + "/" +
SqlFunctions.DatePart("year", tg.DenNgay).ToString().Trim()),
TrangThai = qd.TrangThai
}).ToList();
return(quyDinhDiemDTOs);
}
public List <ThucHien_TieuChuanDTO> getTH_TC()
{
List <ThucHien_TieuChuanDTO> thucHien_TieuChuanDTOs = new List <ThucHien_TieuChuanDTO>();
thucHien_TieuChuanDTOs = (from thtc in db.THUCHIEN_TIEUCHUAN
from tg in db.THOIGIAN_XET
from dv in db.DON_VI
from sv in db.SINH_VIEN
from tc in db.TIEU_CHUAN
where thtc.Mssv == sv.Mssv && sv.DonVi == dv.MaDonVi && thtc.MaThoiGian == tg.MaThoiGian && thtc.MaTieuChuan == tc.MaTieuChuan
select new ThucHien_TieuChuanDTO
{
Mssv = thtc.Mssv,
TenSinhVien = sv.HoTen,
DonVi = dv.MaDonVi,
TenTieuChuan = tc.TenTieuChuan,
ThoiGian = string.Concat(
SqlFunctions.DatePart("day", tg.TuNgay).ToString().Trim() + "/" +
SqlFunctions.DatePart("month", tg.TuNgay).ToString().Trim() + "/" +
SqlFunctions.DatePart("year", tg.TuNgay).ToString().Trim(), "_",
SqlFunctions.DatePart("day", tg.DenNgay).ToString().Trim() + "/" +
SqlFunctions.DatePart("month", tg.DenNgay).ToString().Trim() + "/" +
SqlFunctions.DatePart("year", tg.DenNgay).ToString().Trim())
}).ToList();
return(thucHien_TieuChuanDTOs);
}
public IEnumerable <ShiftDTO> GetConflictingShifts(Guid rosterId, DayOfWeek day)
{
if (ClaimsAuthorization.CheckAccess("Put", "BusinessLocationId", db.Rosters.Find(rosterId).BusinessLocation.Id.ToString()))
{
List <ShiftDTO> conflictingShifts = new List <ShiftDTO>();
int dow = (int)day + 1; // SQL Day of week
var conflicts = (from s1 in db.Shifts
from s2 in db.Shifts
where s1.Id != s2.Id &&
s1.Employee == s2.Employee &&
s1.Roster.Id == rosterId &&
s2.Roster.Id == rosterId &&
SqlFunctions.DatePart("weekday", s1.StartTime) == dow &&
SqlFunctions.DatePart("weekday", s2.StartTime) == dow &&
s1.StartTime <= s2.FinishTime &&
s1.FinishTime >= s2.FinishTime &&
s1.IsPublished == false &&
s1.Employee != null
select s1);
var conflictingShiftsForDay = MapperFacade.MapperConfiguration.Map <IEnumerable <Shift>, IEnumerable <ShiftDTO> >(conflicts);
return(conflictingShiftsForDay);
}
else
{
throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.Unauthorized));
}
}
private void Load_Data()
{
txt_total_money.Text = DAL_Thongke.Get_Total_Money().ToString();
txt_total_paid_money.Text = DAL_Thongke.Get_Total_Money_Paid("0").ToString();
txt_interset_money.Text = DAL_Thongke.Get_Total_Money_Paid("1").ToString();
var lst_customer = (from data in _db.customers
group data by new
{
Column1 = (int?)SqlFunctions.DatePart("mm", data.CreatedAt)
}
into g
select new
{
month = g.Key.Column1,
count = g.Count(data => data.id != null)
}
).OrderBy(g => g.month).ToList();
chart_customer_by_month.DataSource = lst_customer;
DataTable table = new DataTable("Table1");
// Add three columns to the table.
table.Columns.Add("Month", typeof(String));
table.Columns.Add("Section", typeof(String));
table.Columns.Add("Value", typeof(Int32));
// Add data rows to the table.
table.Rows.Add(new object[] { "", "2", 1 });
table.Rows.Add(new object[] { "", "3", 9 });
chart_customer_by_month.DataSource = table;
chart_customer_by_month.SeriesDataMember = "Month";
chart_customer_by_month.SeriesTemplate.ArgumentDataMember = "Section";
chart_customer_by_month.SeriesTemplate.ValueDataMembers.AddRange(new String[] { "Value" });
chart_customer_by_month.SeriesNameTemplate.BeginText = "Month: ";
}
public List <IncidentChartModel> GetIncidents()
{
try
{
// getWeeklyIncidents();
IncidentEntities db = new IncidentEntities();
// IncidentEntities db = new IncidentEntities();
// var incidents = db.ActMonitors.ToList();
List <IncidentChartModel> lstChartModel = new List <IncidentChartModel>();
var incidents1 = db.ActMonitors.GroupBy(x => SqlFunctions.DatePart("ww", x.Actual_Date_and_Time_of_Completion));
var incidents = (from x in db.ActMonitors
join y in db.ActivityTypes on x.Type_of_Activity equals y.ID
select new { x.Status, x.Location, y.ActivityType1 }).ToList();
foreach (var item in incidents)
{
IncidentChartModel chart = new IncidentChartModel();
chart.Status = item.Status;
chart.Location = item.Location;
chart.ActivityType = item.ActivityType1;
lstChartModel.Add(chart);
}
return(lstChartModel);
}
catch (Exception ex)
{
throw;
}
}
public CatWeekDateReport GetCatReportWeekDate(int catId, DateTime?from, DateTime?to)
{
var _from = from;
var firstMonday = GetFirstMonday();
var cat = _context.VideoCategories.Find(catId);
var totalIPViewCount = _context.CategoryAccesses.Where(ca => ca.CategoryId == catId && (!from.HasValue || ca.Date >= from) && (!to.HasValue || ca.Date <= to)).Sum(ca => ca.IPViewCount);
var daysOfWeek = Enum.GetValues(typeof(DayOfWeek)).OfType <DayOfWeek>().ToList();
var data = (from dow in daysOfWeek
from ca in _context.CategoryAccesses.Where(ca => SqlFunctions.DatePart("dw", ca.Date) - 1 == (int)dow && ca.CategoryId == catId && (!_from.HasValue || ca.Date >= _from) && (!to.HasValue || ca.Date <= to)).DefaultIfEmpty()
select new { dow, ca })
.GroupBy(x => x.dow)
.Select(g => new CatWeekDate
{
DOW = g.Key,
IPViewCount = g.Sum(x => x.ca == null? 0 : x.ca.IPViewCount),
PageViewCount = g.Sum(x => x.ca == null? 0 : x.ca.PageViewCount)
}).OrderBy(cwd => cwd.DOW).ToList();
foreach (var catWeekDate in data)
{
catWeekDate.PercentIPViewCount = catWeekDate.IPViewCount * 100D / totalIPViewCount;
}
var report = new CatWeekDateReport
{
CatId = cat.Id,
CatName = cat.Name,
Data = data
};
return(report);
}
public CatWeekAvgChangeReport GetCatReportWeekAvgChange(int catId, DateTime from, DateTime to, double avgWeek)
{
var _from = from;
var avgDay = avgWeek / 7;
var cat = _context.VideoCategories.Find(catId);
var totalIPViewCount = _context.CategoryAccesses.Where(ca => ca.CategoryId == catId && ca.Date >= from && ca.Date <= to).Sum(ca => ca.IPViewCount);
var daysOfWeek = Enum.GetValues(typeof(DayOfWeek)).OfType <DayOfWeek>().ToList();
var data = (from dow in daysOfWeek
from ca in _context.CategoryAccesses.Where(ca => SqlFunctions.DatePart("dw", ca.Date) - 1 == (int)dow && ca.CategoryId == catId && ca.Date >= _from && ca.Date <= to).DefaultIfEmpty()
select new { dow, ca })
.GroupBy(x => x.dow)
.Select(g => new CatWeekAvgChange
{
DOW = g.Key,
IPViewCount = g.Sum(x => x.ca == null ? 0 : x.ca.IPViewCount),
}).OrderBy(cwd => cwd.DOW).ToList();
foreach (var catWeekChange in data)
{
catWeekChange.Change = Math.Round(catWeekChange.IPViewCount - avgDay, 2);
}
var report = new CatWeekAvgChangeReport
{
CatId = cat.Id,
CatName = cat.Name,
Total = totalIPViewCount,
TotalChange = Math.Round(totalIPViewCount - avgWeek, 2),
Data = data
};
return(report);
}
public JsonResult SalaryJson(int ID, int?Month, int?Year)
{
User user = HttpContext.CurrentUser();
int userID = user.ID;
Employee employee = db.Employees.FirstOrDefault(val => val.ID == ID);
if (user.RoleID == (int)RolesEnum.Employee && (user.EmployeeID != ID || employee.UserID != user.ID))
{
return(Json(new { ID = ID, Success = false, Error = "Not allowed." }));
}
if (user.RoleID == (int)RolesEnum.Manager && employee.User != null && employee.User.RoleID <= (int)RolesEnum.Manager && (user.EmployeeID != ID || employee.UserID != user.ID))
{
return(Json(new { ID = ID, Success = false, Error = "Not allowed." }));
}
List <Expense> expenses = db.Expenses.Where(val => val.Type.ForSalary && val.SalaryEmployeeID == ID &&
(!Month.HasValue || SqlFunctions.DatePart("MONTH", val.Date) == Month) &&
(!Year.HasValue || SqlFunctions.DatePart("YEAR", val.Date) == Year)).OrderBy(val => val.Date).ToList();
List <Payroll> payrolls = db.Payrolls.Where(val => val.EmployeeID == ID &&
(!Month.HasValue || SqlFunctions.DatePart("MONTH", val.Date) == Month) &&
(!Year.HasValue || SqlFunctions.DatePart("YEAR", val.Date) == Year)).OrderBy(val => val.Date).ToList();
List <Models.Reports.SalaryRow> rows = new List <Models.Reports.SalaryRow>(expenses.Select(val => new Models.Reports.SalaryRow(val)));
rows.AddRange(payrolls.Select(val => new Models.Reports.SalaryRow(val)));
return(Json(new { Success = true, Rows = rows.OrderBy(val => val.Date).ThenBy(val => val.ID).ToList() }));
}
public ActionResult Index(int?ID)
{
string settingsName = "/Employee/Index";
User user = HttpContext.CurrentUser();
int userID = user.ID;
Employee employee = db.Employees.FirstOrDefault(val => val.ID == ID && !val.Deleted);
if (!ID.HasValue && user.EmployeeID.HasValue || user.RoleID == (int)RolesEnum.Employee && (user.EmployeeID != ID || employee.UserID != user.ID))
{
return(Redirect("~/Employee/Index/" + user.EmployeeID));
}
if (user.RoleID == (int)RolesEnum.Manager && employee.User != null && employee.User.RoleID <= (int)RolesEnum.Manager && (user.EmployeeID != ID || employee.UserID != user.ID))
{
return(HttpNotFound());
}
int year = DateTime.Today.Year;
int month = DateTime.Today.Month;
List <int> years;
string[] months = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.MonthNames;
years = db.Expenses.Where(val => val.EmployeeID == ID).Select(val => SqlFunctions.DatePart("YEAR", val.Date)).Distinct().OrderByDescending(val => val).Select(val => val.Value).ToList();
years.AddRange(db.Transfers.Where(val => (val.WalletFrom.EmployeeID == ID || val.WalletTo.EmployeeID == ID))
.Select(val => SqlFunctions.DatePart("YEAR", val.Date)).Distinct().OrderByDescending(val => val).Select(val => val.Value).ToList());
for (int i = years.Any() ? years.First() : DateTime.Today.Year; i < DateTime.Today.Year + 2; i++)
{
if (!years.Contains(i))
{
years.Add(i);
}
}
years = years.Distinct().OrderByDescending(val => val).ToList();
List <Wallet> wallets = db.Wallets.Where(val => val.EmployeeID == ID || val.EmployeeWallets.Any(ew => ew.EmployeeID == ID && ew.Expense)).ToList();
employee.UpdateSalaryBalance();
db.SaveChanges();
object data = new
{
Year = year,
Month = month,
Years = years,
Months = months,
Employee = employee.ToJson(),
Settings = db.UserSettings.Where(val => val.UserID == userID && val.Name.IndexOf(settingsName) == 0).ToList().Select(val => val.ToJson()).ToList(),
Departments = db.Departments.Where(val => !val.Deleted || val.ID == employee.DepartmentID).OrderBy(val => val.OrderNumber).ToList().Select(val => val.ToJson()).ToList(),
Positions = db.Positions.Where(val => !val.Deleted || val.ID == employee.PositionID).OrderBy(val => val.OrderNumber).ToList().Select(val => val.ToJson()).ToList()
};
ViewBag.Employee = employee;
ViewBag.Data = serializer.Serialize(data);
ViewBag.Page = "Employee.Index";
return(View());
}
public static void ForecastAndExport_v2(string forecast_tag, string data_tag, int forecastPerspective = 2)
{
string tag_name = forecast_tag;
DateTime fdate = DateTime.Today.AddDays(forecastPerspective); // дата прогноза
List <double> input = new List <double>(); // входной вектор
Dictionary <DateTime, double> solution = new Dictionary <DateTime, double>(); // прогноз
var context = new TagDbContext();
//double yMax, yMin;
//if (fdate.DayOfWeek == DayOfWeek.Saturday || fdate.DayOfWeek == DayOfWeek.Sunday) //составляем модель на субботу и воскресенье
//{
// Console.WriteLine("Создание модели.");
// var model = CreateTimeSeriesModel_v2(data_tag, out yMax, out yMin, fdate.DayOfWeek);
// Console.WriteLine("Модели создана.");
// Console.WriteLine("Подготовка входных данных.");
// input = context.TagValues
// .Where(t => t.Tag.TagName == data_tag && (SqlFunctions.DatePart("weekday", t.DateTime) - 1) == (int)fdate.DayOfWeek)
// .OrderByDescending(t => t.DateTime)
// .Select(v => (yMax - v.Value) / (yMax - yMin))
// .Take(retrospective)
// .AsEnumerable()
// .Reverse()
// .ToList();
// Console.WriteLine("Входные данные готовы.");
// Console.WriteLine("Прогнозирование.");
// for (int i = 1; i <= 24; i++)
// {
// double model_result = model.Compute(input.ToArray())[0];
// double result = yMax - model_result * (yMax - yMin);
// solution[fdate.AddHours(i)] = result;
// input.RemoveAt(0);
// input.Add(model_result);
// }
//}
//else // составляем модель на будние дни
//{
DateTime startDate = fdate.AddMonths(-1); //вычитаем
var tender = context.TagValues
.Where(tv => tv.Tag.TagName == data_tag && tv.DateTime > startDate &&
(SqlFunctions.DatePart("weekday", tv.DateTime) - 1) == (int)fdate.DayOfWeek)
.GroupBy(tv => SqlFunctions.DatePart("Hour", tv.DateTime))
.Select(t => new { Hour = t.Key, Value = t.Average(tv => tv.Value) })
.ToArray();
foreach (var entity in tender)
{
solution[fdate.AddHours((int)entity.Hour)] = entity.Value;
}
//}
Console.WriteLine("Запись прогноза...");
ExportToDB(tag_name, solution, context);
}
