public void CreateTime()
{
#if !EFOLD
var q = this.Entities
.Select(x =>
DbFunctions.CreateTime(x.Time.Hours - 1, 2, 1));
#else
var q = this.Entities
.Select(x =>
EntityFunctions.CreateTime(x.Time.Hours - 1, 2, 1));
#endif
var q2 = q.AsEnumerable().Where(x => x.Value.Hours == 2);
q2.Should().NotBeEmpty();
}
public void FillHourGrid()
{
List <HourReport> hourReports = new List <HourReport>();
for (int i = 0; i < 24; i++)
{
var hourStart = (DateTime.Now.Date + new TimeSpan(i, 00, 00)).TimeOfDay;
var hourEnd = (DateTime.Now.Date + new TimeSpan(i, 59, 00)).TimeOfDay;
int count = _context.Reservations
.Where(t => _selectedDoctorId == 0 || t.UserId == _selectedDoctorId)
.Where(t => !checkBoxDate.Checked ||
DbFunctions.TruncateTime(t.StartTime) >= DbFunctions.TruncateTime(dateTimePickerStartDate.Value) && DbFunctions.TruncateTime(t.StartTime) <= DbFunctions.TruncateTime(dateTimePickerEndDate.Value))
.Count(t => hourStart <= DbFunctions.CreateTime(t.StartTime.Hour, t.StartTime.Minute, t.StartTime.Second) &&
hourEnd >= DbFunctions.CreateTime(t.StartTime.Hour, t.StartTime.Minute, t.StartTime.Second));
hourReports.Add(new HourReport()
{
Hour = i, TotalReservations = count
});
}
_hourReports = hourReports;
DataTable dataTable = new DataTable();
dataTable.Columns.Add("Hour");
dataTable.Columns.Add("Total Reservations");
foreach (var hourReport in hourReports)
{
DataRow row = dataTable.NewRow();
row["Hour"] = hourReport.Hour + ":00 - " + hourReport.Hour + ":59";
row["Total Reservations"] = hourReport.TotalReservations;
dataTable.Rows.Add(row);
}
metroGrid1.DataSource = dataTable;
}
protected override void LoadData()
{
daytimePasswords.PasswordActions
.Include(p => p.SymbolActions)
.Where(p => p.UserId == CurrentUserId)
.Where(p => p.ValidPassword == Settings.Password)
.Where(p => DbFunctions.CreateTime(p.StartTime.Hour, p.StartTime.Minute, p.StartTime.Second)
< DbFunctions.CreateTime(eveninigTime.Value.Hours, eveninigTime.Value.Minutes, eveninigTime.Value.Seconds) &&
DbFunctions.CreateTime(p.StartTime.Hour, p.StartTime.Minute, p.StartTime.Second)
>= DbFunctions.CreateTime(morningTime.Value.Hours, morningTime.Value.Minutes, morningTime.Value.Seconds))
.Load();
eveningtimePasswords.PasswordActions
.Include(p => p.SymbolActions)
.Where(p => p.UserId == CurrentUserId)
.Where(p => p.ValidPassword == Settings.Password)
.Where(p => DbFunctions.CreateTime(p.StartTime.Hour, p.StartTime.Minute, p.StartTime.Second)
>= DbFunctions.CreateTime(eveninigTime.Value.Hours, eveninigTime.Value.Minutes, eveninigTime.Value.Seconds) ||
DbFunctions.CreateTime(p.StartTime.Hour, p.StartTime.Minute, p.StartTime.Second)
< DbFunctions.CreateTime(morningTime.Value.Hours, morningTime.Value.Minutes, morningTime.Value.Seconds))
.Load();
//MessageBox.Show(daytimePasswords.PasswordActions.Local.Count.ToString() + " " + eveningtimePasswords.PasswordActions.Local.Count.ToString(), "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
/// <summary>
/// (!) Here we check period including start and including end!
/// </summary>
IQueryable <tblMessageSchedule> GetSchedsForDayPeriod(DateTime start, DateTime end, IQueryable <tblMessageSchedule> income)
{
int DayOfWeekNow = (int)start.DayOfWeek;
var scheds =
Logic.GetFilteredQueryable <tblMessageSchedule>()
.Where(x => x.IsActive && (x.InArchive == null || !x.InArchive.Value))
.Where(x =>
//CHECK ACTUALITY... x.ScheduleDate >= DateTime.Now - x.Actuality
//U WRONG, NOT HERE
//&&
(
(x.RepeatMode == RepeatModes.repeatMode_none &&
x.ScheduleDate >= start && x.ScheduleDate <= end
)
||
(DbFunctions.CreateTime(x.ScheduleDate.Value.Hour, x.ScheduleDate.Value.Minute, x.ScheduleDate.Value.Second) >= start.TimeOfDay &&
DbFunctions.CreateTime(x.ScheduleDate.Value.Hour, x.ScheduleDate.Value.Minute, x.ScheduleDate.Value.Second) <= end.TimeOfDay &&
(x.RepeatMode == RepeatModes.repeatMode_day ||
(x.RepeatMode == RepeatModes.repeatMode_week &&
DbFunctions.DiffDays(firstSunday, x.ScheduleDate) % 7 == DayOfWeekNow
) ||
(x.RepeatMode == RepeatModes.repeatMode_month &&
x.ScheduleDate.Value.Day == start.Day
) ||
(x.RepeatMode == RepeatModes.repeatMode_year &&
x.ScheduleDate.Value.Month == start.Month &&
x.ScheduleDate.Value.Day == start.Day
)
)
)
)
);
income = income == null ? scheds : income.Concat(scheds);
return(income);
}
// GET: Productions
public ActionResult Index(string searchString, string season, string month, string day, string showtime, string runtime, bool?worldPremiere, bool?isSearching, string calBool)
{
var productions = from p in db.Productions
select p;
var showTimes = from p in db.Productions
select new ShowtimeListVM
{
ShowTimeEve = p.ShowtimeEve,
ShowTimeMat = p.ShowtimeMat
};
var runTimes = from p in db.Productions
select p.Runtime;
//Pass Variables to ViewBag
ViewBag.SeasonYears = SeasonYears(); //Dictionary of Season Years <int, string>
ViewBag.ShowTimes = ShowtimeSort(showTimes); //Sort showtimes ascending
ViewBag.RunTimes = RuntimeSort(runTimes); //Sort runtimes ascending
ViewBag.CurrentFilter = searchString;
ViewBag.CurrentSeason = season;
ViewBag.CurrentMonth = month;
ViewBag.CurrentDay = day;
ViewBag.CurrentShowtime = showtime;
ViewBag.CurrentRuntime = runtime;
ViewBag.WorldPremiere = worldPremiere;
if (!String.IsNullOrEmpty(month) && month != "any" && !String.IsNullOrEmpty(day) && day != "any") // If day && month HasValue // If day && month HasValue
{
var monthInt32 = Int32.Parse(month);
monthInt32 += 1; // Add one to month for db comparison, value passed from view is 0-11 with 0:Jan 1:Feb etc...
var dayInt32 = Int32.Parse(day);
if (!IsValidDay(dayInt32, monthInt32)) // Call day validation check
{
ViewBag.DayEx = "Invalid Date";
isSearching = true;
ViewBag.IsSearching = isSearching;
return(View(productions.ToList()));
}
else
{
productions = productions.Where(p => p.OpeningDay.Month <= monthInt32 && p.ClosingDay.Month >= monthInt32);
productions = productions.Where(p => p.OpeningDay.Day <= dayInt32 && p.ClosingDay.Day >= dayInt32);
isSearching = true;
}
}
else
{
if (!String.IsNullOrEmpty(month) && month != "any")
{
var monthInt32 = Int32.Parse(month);
monthInt32 += 1;
productions = productions.Where(p => p.OpeningDay.Month <= monthInt32 && p.ClosingDay.Month >= monthInt32);
isSearching = true;
}
if (!String.IsNullOrEmpty(day) && day != "any")
{
var dayInt32 = Int32.Parse(day);
productions = productions.Where(p => p.OpeningDay.Day <= dayInt32 && p.ClosingDay.Day >= dayInt32);
isSearching = true;
}
}
//Compare values passed from view to values in productions
if (!String.IsNullOrEmpty(searchString))
{
productions = productions.Where(p => p.Title.Contains(searchString) || p.Playwright.Contains(searchString) || p.Description.Contains(searchString));
isSearching = true;
}
if (!String.IsNullOrEmpty(season) && season != "any")
{
var seasonInt32 = Int32.Parse(season);
productions = productions.Where(p => p.Season == seasonInt32);
isSearching = true;
}
if (!String.IsNullOrEmpty(showtime) && showtime != "any")
{
var dt = DateTime.Parse(showtime);
var time = dt.TimeOfDay;
productions = productions.Where(p => DbFunctions.CreateTime(((DateTime)p.ShowtimeEve).Hour, ((DateTime)p.ShowtimeEve).Minute, ((DateTime)p.ShowtimeEve).Second) == time || DbFunctions.CreateTime(((DateTime)p.ShowtimeMat).Hour, ((DateTime)p.ShowtimeMat).Minute, ((DateTime)p.ShowtimeMat).Second) == time);
isSearching = true;
}
if (!String.IsNullOrEmpty(runtime) && runtime != "any")
{
var runtimeInt32 = Int32.Parse(runtime);
productions = productions.Where(p => p.Runtime == runtimeInt32);
isSearching = true;
}
if (worldPremiere == true)
{
productions = productions.Where(p => p.IsWorldPremiere == true);
isSearching = true;
}
if (isSearching.HasValue && isSearching == true) //Pass isSearching boolean to ViewBag
{
ViewBag.IsSearching = isSearching;
}
else if (season == "any" || month == "any" || day == "any" || showtime == "any" || runtime == "any")
{
ViewBag.IsSearching = true;
}
else
{
ViewBag.IsSearching = false;
}
ViewBag.Results = productions.Count(); //Total search results
//check if calendar is displayed before this GET Request
ViewData["calDisplay"] = calBool;
var prodId = productions.Select(i => i.ProductionId).ToList();
//return only calendar events that are associated with the filtered results
var eventList = db.CalendarEvent.Where(x => prodId.Contains(x.ProductionId ?? 0)).ToList();
var eventArray = eventList.Select(x => new[]
{
x.EventId.ToString(),
x.Title,
x.StartDate.ToString("o"),
x.EndDate.ToString("o"),
x.TicketsAvailable.ToString(),
x.Color,
x.ProductionId.ToString()
}).ToArray();
ViewData["events"] = eventArray;
return(View(productions.OrderByDescending(p => p.OpeningDay).ToList()));
}
public List <SeatingSummary> SeatingByDateTime(DateTime date, TimeSpan time)
{
using (var context = new RestaurantContext())
{
// code from my LinqPad explorations
//step1 = Get the table info along with any walk-in bills and reservation bills for the specific time slot
var step1 = from data in context.Tables
select new
{
Table = data.TableNumber,
Seating = data.Capacity,
// This sub-query gets the bills for walk-in customers
Bills = from billing in data.Bills
where
billing.BillDate.Year == date.Year &&
billing.BillDate.Month == date.Month &&
billing.BillDate.Day == date.Day
//The following won't work in EF to Entities - it will return this exception:
// "The specified type member 'TimeOfDay' is not supported..."
//&& billing.BillDate.TimeOfDay <= time
&& DbFunctions.CreateTime(billing.BillDate.Hour, billing.BillDate.Minute, billing.BillDate.Second) <= time
&& (!billing.OrderPaid.HasValue || billing.OrderPaid >= time)
//&& (!billing.PaidStatus || billing.OrderPaid >= time)
select billing,
// This sub-query gets the bills for reservations
Reservations = from booking in data.Reservations //drill to the first collection
from billing in booking.Bills //drill to the next collection
where
billing.BillDate.Year == date.Year &&
billing.BillDate.Month == date.Month &&
billing.BillDate.Day == date.Day
//The following won't work in EF to Entities - it will return this exception:
// "The specified type member 'TimeOfDay' is not supported..."
//&& billing.BillDate.TimeOfDay <= time
&& DbFunctions.CreateTime(billing.BillDate.Hour, billing.BillDate.Minute, billing.BillDate.Second) <= time
&& (!billing.OrderPaid.HasValue || billing.OrderPaid >= time)
//&& (!billing.PaidStatus || billing.OrderPaid >= time)
select billing
};
//step2 - Union the walk-in bills and the reservation bills while extracting the relevant bill info
//.ToList() help to resolve the "Types in Union of Concat are contructed incompatibly" error
var step2 = from data in step1.ToList() //.ToList() forces the first result set to in-memory
select new
{
Table = data.Table,
Seating = data.Seating,
CommonBilling = from info in data.Bills.Union(data.Reservations)
// change to get only needed info, not entire entity
select new //info
{
BillID = info.BillID,
BillTotal = info.Items.Sum(bi => bi.Quantity * bi.SalePrice),
Waiter = info.Waiter.FirstName,
Reservation = info.Reservation
}
};
//step 3 -Get just the first CommonBilling item
// (presumes no overlaps can occur 0 i.e., two groups at the same table at the same time)
var step3 = from data in step2
select new
{
Table = data.Table,
Seating = data.Seating,
Taken = data.CommonBilling.Count() > 0,
// . FirstOrDefault() is effectively " flattening" my collection of 1 item into a
//single object whose properties I can get in step4 by using the dot (.) operator
CommonBilling = data.CommonBilling.FirstOrDefault()
};
var step4 = from data in step3
select new SeatingSummary()
{
Table = data.Table,
Seating = data.Seating,
Taken = data.Taken,
//use a ternary expression to conditionally get the bill id (if it exists
BillID = data.Taken ? //if (data.Taken)
data.CommonBilling.BillID //value to use if true
: // else
(int?)null, //value to use if false
BillTotal = data.Taken ?
data.CommonBilling.BillTotal : (decimal?)null,
Waiter = data.Taken ? data.CommonBilling.Waiter : (string)null,
ReservationName = data.Taken ?
(data.CommonBilling.Reservation != null ?
data.CommonBilling.Reservation.CustomerName : (string)null)
: (string)null
};
return(step4.ToList());
}
}
public List <SeatingSummary> SeatingByDateTime(DateTime date, TimeSpan time)
{
using (eRestaurantContext context = new eRestaurantContext())
{
// Step 1 - Get the table info along with any walk-in bills and reservation bills for the specific time slot
var step1 = from data in context.Tables
select new
{
Table = data.TableNumber,
Seating = data.Capacity,
// This sub-query gets the bills for walk-in customers
WalkIns = from walkIn in data.Bills
where
walkIn.BillDate.Year == date.Year &&
walkIn.BillDate.Month == date.Month &&
walkIn.BillDate.Day == date.Day
//&& walkIn.BillDate.TimeOfDay <= time
&& DbFunctions.CreateTime(walkIn.BillDate.Hour,
walkIn.BillDate.Minute,
walkIn.BillDate.Second) <= time &&
(!walkIn.OrderPaid.HasValue || walkIn.OrderPaid.Value >= time)
// && (!walkIn.PaidStatus || walkIn.OrderPaid >= time)
select walkIn,
// This sub-query gets the bills for reservations
Reservations = from booking in data.Reservations
from reservationParty in booking.Bills
where
reservationParty.BillDate.Year == date.Year &&
reservationParty.BillDate.Month == date.Month &&
reservationParty.BillDate.Day == date.Day
//&& reservationParty.BillDate.TimeOfDay <= time
&& DbFunctions.CreateTime(reservationParty.BillDate.Hour,
reservationParty.BillDate.Minute,
reservationParty.BillDate.Second) <= time &&
(!reservationParty.OrderPaid.HasValue || reservationParty.OrderPaid.Value >= time)
// && (!reservationParty.PaidStatus || reservationParty.OrderPaid >= time)
select reservationParty
};
// step1.Dump();
// Step 2 - Union the walk-in bills and the reservation bills while extracting the relevant bill info
// .ToList() helps resolve the "Types in Union or Concat are constructed incompatibly" error
var step2 = from data in step1.ToList() // .ToList() forces the first result set to be in memory
select new
{
Table = data.Table,
Seating = data.Seating,
CommonBilling = from info in data.WalkIns.Union(data.Reservations)
select new // info
{
BillID = info.BillID,
BillTotal = info.Items.Sum(bi => bi.Quantity * bi.SalePrice),
Waiter = info.Waiter.FirstName,
Reservation = info.Reservation
}
};
// step2.Dump();
// Step 3 - Get just the first CommonBilling item
// (presumes no overlaps can occur - i.e., two groups at the same table at the same time)
var step3 = from data in step2.ToList()
select new
{
Table = data.Table,
Seating = data.Seating,
Taken = data.CommonBilling.Count() > 0,
// .FirstOrDefault() is effectively "flattening" my collection of 1 item into a
// single object whose properties I can get in step 4 using the dot (.) operator
CommonBilling = data.CommonBilling.FirstOrDefault()
};
// step3.Dump();
// Step 4 - Build our intended seating summary info
var step4 = from data in step3
select new SeatingSummary // SeatingSummary() // the DTO class to use in my BLL
{
Table = data.Table,
Seating = data.Seating,
Taken = data.Taken,
// use a ternary expression to conditionally get the bill id (if it exists)
BillID = data.Taken ? // if(data.Taken)
data.CommonBilling.BillID // value to use if true
: (int?)null, // value to use if false
BillTotal = data.Taken ?
data.CommonBilling.BillTotal : (decimal?)null,
Waiter = data.Taken ? data.CommonBilling.Waiter : (string)null,
ReservationName = data.Taken ?
(data.CommonBilling.Reservation != null ?
data.CommonBilling.Reservation.CustomerName : (string)null)
: (string)null
};
return(step4.ToList());
}
}
public List <SeatingSummary> SeatingByDateTime(DateTime date, TimeSpan newtime)
{
using (eRestaurantContext context = new eRestaurantContext())
{
var step1 = from data in context.Tables
select new
{
Table = data.TableNumber,
Seating = data.Capacity,
// This sub-query gets the bills for walk-in customers
WalkIns = from walkIn in data.Bills
where
walkIn.BillDate.Year == date.Year &&
walkIn.BillDate.Month == date.Month &&
walkIn.BillDate.Day == date.Day
//Linq to Entity does not play nicely with DateTime/Timespan
//of which TimeOfDay belongs
//&& walkIn.BillDate.TimeOfDay <= newtime
&& DbFunctions.CreateTime(walkIn.BillDate.Hour, walkIn.BillDate.Minute, walkIn.BillDate.Second) <= newtime
//inside System.Data.Entity is a class of functions
//that will help with DateTime/TimeSpan concerns
&& (!walkIn.OrderPaid.HasValue || walkIn.OrderPaid.Value >= newtime)
// && (!walkIn.PaidStatus || walkIn.OrderPaid >= time)
select walkIn,
// This sub-query gets the bills for reservations
//Linq connects to SQL, we don't create the reservationtables entity
//OnModelCreate such table
Reservations = from booking in data.Reservations
from reservationParty in booking.Bills //dont need Reservation table,
//b/c we use the OnModelCreate
where
reservationParty.BillDate.Year == date.Year &&
reservationParty.BillDate.Month == date.Month &&
reservationParty.BillDate.Day == date.Day
//&& reservationParty.BillDate.TimeOfDay <= newtime
&& DbFunctions.CreateTime(reservationParty.BillDate.Hour, reservationParty.BillDate.Minute, reservationParty.BillDate.Second) <= newtime &&
(!reservationParty.OrderPaid.HasValue || reservationParty.OrderPaid.Value >= newtime)
// && (!reservationParty.PaidStatus || reservationParty.OrderPaid >= time)
select reservationParty
};
var step2 = from data in step1.ToList() // .ToList() forces the first result set to be in memory
select new
{
Table = data.Table,
Seating = data.Seating,
CommonBilling = from info in data.WalkIns.Union(data.Reservations)
select new // info
{
BillID = info.BillID,
BillTotal = info.Items.Sum(bi => bi.Quantity * bi.SalePrice),
Waiter = info.Waiter.FirstName,
Reservation = info.Reservation
}
};
//we call it Items, so we change from BillItems to Items above
// Step 3 - Get just the first CommonBilling item
// (presumes no overlaps can occur - i.e., two groups at the same table at the same time)
var step3 = from data in step2.ToList()
select new
{
Table = data.Table,
Seating = data.Seating,
Taken = data.CommonBilling.Count() > 0,
// .FirstOrDefault() is effectively "flattening" my collection of 1 item into a
// single object whose properties I can get in step 4 using the dot (.) operator
CommonBilling = data.CommonBilling.FirstOrDefault()
};
// Step 4 - Build our intended seating summary info
var step4 = from data in step3
select new SeatingSummary() // the DTO class to use in my BLL
{
Table = data.Table,
Seating = data.Seating,
Taken = data.Taken,
// use a ternary expression to conditionally get the bill id (if it exists)
BillID = data.Taken ? // if(data.Taken)
data.CommonBilling.BillID // value to use if true
: (int?)null, // value to use if false
BillTotal = data.Taken ?
data.CommonBilling.BillTotal : (decimal?)null,
Waiter = data.Taken ? data.CommonBilling.Waiter : (string)null,
ReservationName = data.Taken ?
(data.CommonBilling.Reservation != null ?
data.CommonBilling.Reservation.CustomerName : (string)null)
: (string)null
};
return(step4.ToList());
}
}
public async Task <ReturnResult> Saveshift([FromBody] ShitfMaster ShiftDetail)
{
bool success = false;
string Message = "";
var currentUserId = HttpContext.Current.User.Identity.GetUserId();
var user = (await _userService.GetAllAsync()).Where(x => x.Id == HttpContext.Current.User.Identity.GetUserId() && x.IsActive == true).FirstOrDefault();
//if (user != null)// cehck for user
//{
//}
if (ShiftDetail.Id == 0)
{
var data = _genericService.ShitfMaster.GetAll().Where(x => x.ShitfName == ShiftDetail.ShitfName.Trim()).ToList();
if (data.Count() == 0)
{
var result = _genericService.ShitfMaster.GetAll().Where(y => DbFunctions.CreateTime(y.ToTime.Hour, y.ToTime.Minute, y.ToTime.Second) == DbFunctions.CreateTime(ShiftDetail.ToTime.Hour, ShiftDetail.ToTime.Minute, ShiftDetail.ToTime.Second) &&
DbFunctions.CreateTime(y.FromTime.Hour, y.FromTime.Minute, y.FromTime.Second) == DbFunctions.CreateTime(ShiftDetail.FromTime.Hour, ShiftDetail.FromTime.Minute, ShiftDetail.FromTime.Second)).ToList();
if (ShiftDetail.FromTime != ShiftDetail.ToTime)
{
if (result.Count() == 0)
{
ShiftDetail.IsActive = true;
ShiftDetail.CreatedBy = currentUserId;
ShiftDetail.CreatedOn = DateTime.UtcNow;
ShiftDetail.UpdatedBy = currentUserId;
ShiftDetail.UpdatedOn = DateTime.UtcNow;
ShiftDetail.FromTime = ShiftDetail.FromTime;
ShiftDetail.ToTime = ShiftDetail.ToTime;
ShiftDetail.OrganizationId = user == null ? null : user.OrganizationId;
_genericService.ShitfMaster.Insert(ShiftDetail);
Message = "Shift saved successfully!!";
success = true;
}
else
{
return(new ReturnResult {
Message = "Shift time already assigned to other shift", Success = false
});
}
}
else
{
return(new ReturnResult {
Message = "Shift time invalid", Success = false
});
}
}
else
{
return(new ReturnResult {
Message = "Shift name already assigned to other shift", Success = false
});
}
}
else
{
var shiftFromDb = _genericService.ShitfMaster.GetById(ShiftDetail.Id);
if (shiftFromDb != null)
{
var data = _genericService.ShitfMaster.GetAll().Where(x => x.Id != ShiftDetail.Id && x.ShitfName == ShiftDetail.ShitfName.Trim()).ToList();
if (data.Count() == 0)
{
var result = _genericService.ShitfMaster.GetAll().Where(y => y.Id != ShiftDetail.Id && y.ToTime == ShiftDetail.ToTime && y.FromTime == ShiftDetail.FromTime).ToList();
if (result.Count() == 0)
{
if (ShiftDetail.FromTime != ShiftDetail.ToTime)
{
if (shiftFromDb != null)
{
shiftFromDb.ShitfName = ShiftDetail.ShitfName;
shiftFromDb.FromTime = ShiftDetail.FromTime;
shiftFromDb.ToTime = ShiftDetail.ToTime;
shiftFromDb.UpdatedBy = currentUserId;
shiftFromDb.UpdatedOn = DateTime.UtcNow;
ShiftDetail.IsActive = true;
shiftFromDb.CreatedOn = shiftFromDb.CreatedOn;
_genericService.ShitfMaster.Update(shiftFromDb);
ShiftDetail.OrganizationId = user == null ? null : user.OrganizationId;
Message = "Shift update successfully!!";
success = true;
}
;
}
else
{
return(new ReturnResult {
Message = "Shift time invalid", Success = false
});
}
}
else
{
return(new ReturnResult {
Message = "Shift time already assigned to other shift", Success = false
});
}
}
else
{
return(new ReturnResult {
Message = "Shift name already assigned to other shift", Success = false
});
}
}
}
_genericService.Commit();
return(new ReturnResult {
Message = Message, Success = true
});
}
public ActionResult Report(ReportVM _report)
{
var UserRole = CheckUser();
if (UserRole == 1 && _report.Report.DateFrom != null && _report.Report.DateTo != null)
{
var _FDate = Convert.ToDateTime(_report.Report.DateFrom);
var _fDateFormat = _FDate.ToString("yyyy-MM-dd");
DateTime FDate = Convert.ToDateTime(_fDateFormat);
var _TDate = Convert.ToDateTime(_report.Report.DateTo);
var _tDateFormat = _TDate.ToString("yyyy-MM-dd");
DateTime TDate = Convert.ToDateTime(_tDateFormat);
int diffBetweenTwoDates = 0;
if (TDate > FDate || TDate == FDate)
{
diffBetweenTwoDates = Convert.ToInt32((TDate - FDate).TotalDays);
}
var Delay = (from r in db.LogedinUsers
where DbFunctions.CreateTime(SqlFunctions.DatePart("hh", r.CheckInTime),
SqlFunctions.DatePart("mi", r.CheckInTime),
SqlFunctions.DatePart("ss", r.CheckInTime)) > DbFunctions.CreateTime(SqlFunctions.DatePart("hh", r.user.CheckInTime),
SqlFunctions.DatePart("mi", r.user.CheckInTime),
SqlFunctions.DatePart("ss", r.user.CheckInTime)) && r.Day >= FDate && r.Day <= TDate
group r by r.user.Name into g
select new
{
Name = g.Key,
delay = g.Count(),
attendance = 0
}).ToList();
var Attendance = db.LogedinUsers
.Where(r => r.Day >= FDate && r.Day <= TDate)
.Select(z => new { z.user.Name, z.Day })
.GroupBy(x => new { x.Name })
.Select(g => new
{
Name = g.Key.Name,
delay = 0,
attendance = g.Count()
}).ToList();
var _total = Delay.Union(Attendance);
var _final = _total.Select(e => new { e.Name, e.delay, e.attendance }).GroupBy(e => e.Name).Select(g => new report
{
Name = g.Key,
Del = g.Sum(x => x.delay),
Attendance = diffBetweenTwoDates + 1 - g.Sum(x => x.attendance)
}).ToList();
var ReportVM = new ReportVM
{
Reports = _final,
};
Session["EmpHistory"] = _final;
return(View(ReportVM));
}
else if (UserRole == 1 && (_report.Report.DateFrom == null || _report.Report.DateTo == null))
{
var _reoprtVM = new ReportVM();
return(View(_reoprtVM));
}
else
{
return(RedirectToAction("Login"));
}
}
public JsonResult AddHorario(ActividadVM actividadVM)
{
var result = false;
try
{
var horario = db.HORARIO.Find(actividadVM.horario.ID_HORARIO);
if (horario == null)
{
horario = new HORARIO();
db.HORARIO.Add(horario);
}
horario.ID_ACTIVIDAD = actividadVM.ID_ACTIVIDAD;
horario.ID_LOCACION = actividadVM.horario.ID_LOCACION;
horario.HORA_INICIO = actividadVM.horario.HORA_INICIO;
horario.HORA_FIN = actividadVM.horario.HORA_FIN;
horario.DIA = (short)(Enumeradores.DIAS)Enum.Parse(typeof(Enumeradores.DIAS), actividadVM.horario.DIA, true).GetHashCode();
if (horario.HORA_INICIO > horario.HORA_FIN)
{
return(Json(result, JsonRequestBehavior.AllowGet));
}
//si el horario esta disponible en esa locacion
//profesor disponible en ese horario
//// overlaps de fechas
var query = db.HORARIO.Where(e => (DbFunctions.CreateDateTime(e.ACTIVIDAD.FECHA_INICIO.Year, e.ACTIVIDAD.FECHA_INICIO.Month, e.ACTIVIDAD.FECHA_INICIO.Day, 0, 0, 0)
<= DbFunctions.CreateDateTime(actividadVM.FECHA_FIN.Year, actividadVM.FECHA_FIN.Month, actividadVM.FECHA_FIN.Day, 0, 0, 0)) &&
(DbFunctions.CreateDateTime(actividadVM.FECHA_INICIO.Year, actividadVM.FECHA_INICIO.Month, actividadVM.FECHA_INICIO.Day, 0, 0, 0)
<= DbFunctions.CreateDateTime(e.ACTIVIDAD.FECHA_FIN.Year, e.ACTIVIDAD.FECHA_FIN.Month, e.ACTIVIDAD.FECHA_FIN.Day, 0, 0, 0)) && e.ACTIVIDAD.ESTADO == true);
var t = query.Count();
//// overlaps de horarios
if (query.Count() > 0)
{
query = query.Where(e => (DbFunctions.CreateTime(e.HORA_INICIO.Hour, e.HORA_INICIO.Minute, e.HORA_INICIO.Second)
< DbFunctions.CreateTime(horario.HORA_FIN.Hour, horario.HORA_FIN.Minute, horario.HORA_FIN.Second)) &&
(DbFunctions.CreateTime(horario.HORA_INICIO.Hour, horario.HORA_INICIO.Minute, horario.HORA_INICIO.Second)
< DbFunctions.CreateTime(e.HORA_FIN.Hour, e.HORA_FIN.Minute, e.HORA_FIN.Second)));
}
t = query.Count();
// overlops de dia
if (query.Count() > 0)
{
query = query.Where(e => (horario.DIA == e.DIA));
}
t = query.Count();
//a partir de aca query tiene todos los horarios que se crusan en fecha hora y dia
// overlaps de profesor
query = query.Where(e => (e.ACTIVIDAD.ID_PROFESOR == actividadVM.ID_PROFESOR) || (e.ID_LOCACION == horario.ID_LOCACION));
t = query.Count();
if (t > 0)
{
return(Json(result, JsonRequestBehavior.AllowGet));
}
db.SaveChanges();
result = true;
}
catch (Exception ex)
{
throw ex;
}
return(Json(result, JsonRequestBehavior.AllowGet));
}
public static EficienciaPorDiaDto GetEfficiencyPerDay(DateTime fechaInicio, DateTime fechaTermino, int tipo)
{
#region Declaración
EficienciaPorDiaDto eficienciaTotal = new EficienciaPorDiaDto();
List <DetalleEficienciaDto> lista = new List <DetalleEficienciaDto>();
DetalleEficienciaDto eficiencia = new DetalleEficienciaDto();
DetalleEficienciaDto separador = new DetalleEficienciaDto();
List <DetalleEficienciaDto> data = new List <DetalleEficienciaDto>();
ResumenEficienciaDto resumen = new ResumenEficienciaDto();
List <ResumenEficienciaDto> listaResumen = new List <ResumenEficienciaDto>();
bool primerUsuario = true;
bool primeraFila = true;
int sTotalE = 0;
int sTotalI = 0;
int totalE = 0;
int totalI = 0;
TimeSpan horaI = new TimeSpan();
TimeSpan horaF = new TimeSpan();
TimeSpan totalHora = new TimeSpan();
TimeSpan paramEuidHora = new TimeSpan(1, 0, 0);
string usuario = "";
DateTime dateT = new DateTime();
repository = unitOfWork.Repository <MovimientoSecado>();
repositoryI = unitOfWork.Repository <InfoFieldBook>();
repositoryU = unitOfWork.Repository <Usuario>();
#endregion
if (tipo == 1)
{
data = (from mc in repository.Table
from us in repositoryU.Table
from inf in repositoryI.Table
where mc.fechaInicio >= fechaInicio && mc.fechaInicio <= fechaTermino &&
mc.usuario == us.nombre_usuario && mc.euid == inf.euid
orderby mc.fechaInicio, us.nombre_usuario
group mc by new
{
inf.euid,
inf.crop,
inf.client,
inf.gmoEvent,
inf.opExpName,
inf.location,
inf.projecLead,
inf.sag,
mc.usuario,
mc.fechaInicio,
us.nombre,
us.apellido
} into gcs
select new DetalleEficienciaDto
{
Client = gcs.Key.client,
Crop = gcs.Key.crop,
Euid = gcs.Key.euid,
ExpName = gcs.Key.opExpName,
FechaPacking = gcs.Key.fechaInicio,
Fecha = (DateTime)DbFunctions.TruncateTime(gcs.Key.fechaInicio),
Time = (TimeSpan)DbFunctions.CreateTime(gcs.Key.fechaInicio.Hour, gcs.Key.fechaInicio.Minute, gcs.Key.fechaInicio.Second),
GmoEvent = gcs.Key.gmoEvent,
Location = gcs.Key.location,
ProjectLead = gcs.Key.projecLead,
Sag = gcs.Key.sag,
Usuario = gcs.Key.usuario,
NombreCompleto = gcs.Key.nombre + " " + gcs.Key.apellido
}).ToList();
}
else if (tipo == 2)
{
data = (from mc in repository.Table
from us in repositoryU.Table
from inf in repositoryI.Table
where mc.fechaTermino >= fechaInicio && mc.fechaTermino <= fechaTermino &&
mc.usuario == us.nombre_usuario && mc.euid == inf.euid
orderby mc.fechaInicio, us.nombre_usuario
group mc by new
{
inf.euid,
inf.crop,
inf.client,
inf.gmoEvent,
inf.opExpName,
inf.location,
inf.projecLead,
inf.sag,
mc.usuario,
mc.fechaTermino,
us.nombre,
us.apellido
} into gcs
select new DetalleEficienciaDto
{
Client = gcs.Key.client,
Crop = gcs.Key.crop,
Euid = gcs.Key.euid,
ExpName = gcs.Key.opExpName,
FechaPacking = (DateTime)gcs.Key.fechaTermino,
Fecha = (DateTime)DbFunctions.TruncateTime(gcs.Key.fechaTermino),
Time = (TimeSpan)DbFunctions.CreateTime(((DateTime)gcs.Key.fechaTermino).Hour, ((DateTime)gcs.Key.fechaTermino).Minute, ((DateTime)gcs.Key.fechaTermino).Second),
GmoEvent = gcs.Key.gmoEvent,
Location = gcs.Key.location,
ProjectLead = gcs.Key.projecLead,
Sag = gcs.Key.sag,
Usuario = gcs.Key.usuario,
NombreCompleto = gcs.Key.nombre + " " + gcs.Key.apellido
}).ToList();
}
if (data.Count > 0)
{
foreach (var item in data)
{
if (dateT != item.Fecha)
{
eficiencia.Fecha = item.Fecha;
eficiencia.FechaPacking = item.FechaPacking;
eficiencia.Usuario = item.Usuario;
if (primeraFila)
{
horaI = item.Time;
}
if (!primerUsuario)
{
//Detalle
horaF = lista[lista.Count - 1].Time;
separador = new DetalleEficienciaDto();
separador.Usuario = "Sub Total";
separador.Euid = sTotalE.ToString("N0", CultureInfo.CurrentCulture);
separador.IndEuid = sTotalI.ToString("N0", CultureInfo.CurrentCulture);
separador.Time = horaF.Subtract(horaI);
totalHora = totalHora.Add(separador.Time);
if (separador.Time.TotalMinutes > 0)
{
var euidHora = 3600 / ((separador.Time.TotalMinutes / sTotalE) * 60);
separador.EuidHora = euidHora.ToString("N0", CultureInfo.CurrentCulture);
}
sTotalE = 0;
sTotalI = 0;
horaI = item.Time;
lista.Add(separador);
//Resumen
resumen = new ResumenEficienciaDto();
resumen.Usuario = item.Usuario;
resumen.Fecha = dateT;
resumen.Hora = separador.Time;
resumen.NumeroEuid = separador.Euid;
resumen.NumeroIndEuid = separador.IndEuid;
resumen.EuidHora = separador.EuidHora ?? "0";
listaResumen.Add(resumen);
}
primerUsuario = false;
dateT = item.Fecha;
}
if (usuario != item.Usuario)
{
if (!primeraFila)
{
//Detalle
horaF = lista[lista.Count - 1].Time;
separador = new DetalleEficienciaDto();
separador.Usuario = "Sub Total";
separador.Euid = sTotalE.ToString("N0", CultureInfo.CurrentCulture);
separador.IndEuid = sTotalI.ToString("N0", CultureInfo.CurrentCulture);
separador.Time = horaF.Subtract(horaI);
totalHora = totalHora.Add(separador.Time);
if (separador.Time.TotalMinutes > 0)
{
var euidHora = 3600 / ((separador.Time.TotalMinutes / sTotalE) * 60);
separador.EuidHora = euidHora.ToString("N0", CultureInfo.CurrentCulture);
}
sTotalE = 0;
sTotalI = 0;
horaI = item.Time;
lista.Add(separador);
//Resumen
resumen = new ResumenEficienciaDto();
resumen.Usuario = usuario;
resumen.Fecha = item.Fecha;
resumen.Hora = separador.Time;
resumen.NumeroEuid = separador.Euid;
resumen.NumeroIndEuid = separador.IndEuid;
resumen.EuidHora = separador.EuidHora ?? "0";
listaResumen.Add(resumen);
}
eficiencia.Usuario = item.Usuario;
usuario = item.Usuario;
}
eficiencia.Euid = item.Euid;
eficiencia.IndEuid = item.IndEuid;
eficiencia.Crop = item.Crop;
eficiencia.Client = item.Client;
eficiencia.ProjectLead = item.ProjectLead;
eficiencia.Location = item.Location;
eficiencia.ExpName = item.ExpName;
eficiencia.GmoEvent = item.GmoEvent;
eficiencia.Sag = item.Sag;
eficiencia.Time = item.Time;
if (eficiencia.Euid != "")
{
sTotalE++;
totalE++;
}
if (eficiencia.IndEuid != "")
{
sTotalI++;
totalI++;
}
lista.Add(eficiencia);
eficiencia = new DetalleEficienciaDto();
primeraFila = false;
}
//Detalle
separador = new DetalleEficienciaDto();
horaF = lista[lista.Count - 1].Time;
separador.Usuario = "Sub Total";
separador.Euid = sTotalE.ToString("N0", CultureInfo.CurrentCulture);
separador.IndEuid = sTotalI.ToString("N0", CultureInfo.CurrentCulture);
separador.Time = horaF.Subtract(horaI);
if (separador.Time.TotalMinutes > 0)
{
var euidHora = 3600 / ((separador.Time.TotalMinutes / sTotalE) * 60);
separador.EuidHora = euidHora.ToString("N0", CultureInfo.CurrentCulture);
}
totalHora = totalHora.Add(separador.Time);
sTotalE = 0;
sTotalI = 0;
lista.Add(separador);
//Resumen
resumen = new ResumenEficienciaDto();
resumen.Usuario = usuario;
resumen.Fecha = dateT;
resumen.Hora = separador.Time;
resumen.NumeroEuid = separador.Euid;
resumen.NumeroIndEuid = separador.IndEuid;
resumen.EuidHora = separador.EuidHora ?? "0";
listaResumen.Add(resumen);
//Total
separador = new DetalleEficienciaDto();
separador.Usuario = "Total";
separador.Euid = totalE.ToString("N0", CultureInfo.CurrentCulture);
separador.IndEuid = totalI.ToString("N0", CultureInfo.CurrentCulture);
separador.Time = totalHora;
if (separador.Time.TotalMinutes > 0)
{
var euidHora = 3600 / ((separador.Time.TotalMinutes / totalE) * 60);
separador.EuidHora = euidHora.ToString("N0", CultureInfo.CurrentCulture);
}
lista.Add(separador);
//Detalle y Resumen
eficienciaTotal.Detalle = lista;
eficienciaTotal.Resumen = listaResumen;
}
return(eficienciaTotal);
}
/// <summary>
/// Builds the metric query.
/// </summary>
/// <param name="metricSource">The metric source.</param>
/// <param name="dateRange">The date range.</param>
/// <returns></returns>
protected List <MetricValue> GetMetricValues(List <int> metricSource, DateRange dateRange, string metricKey)
{
var rockContext = new RockContext();
var metricService = new MetricService(rockContext);
var metricQueryable = new MetricService(rockContext).Queryable();
if (!string.IsNullOrEmpty(metricKey))
{
metricQueryable = metricService.Queryable().Where(a => a.Title.EndsWith(metricKey));
}
else
{
metricQueryable = metricService.GetByIds(metricSource);
}
var metricValueQueryable = metricQueryable.SelectMany(a => a.MetricValues).AsQueryable().AsNoTracking();
// filter by date context
if (dateRange != null)
{
metricValueQueryable = metricValueQueryable.Where(a => a.MetricValueDateTime >= dateRange.Start && a.MetricValueDateTime <= dateRange.End);
}
// filter by campus context
if (CampusContext != null)
{
metricValueQueryable = metricValueQueryable.Where(a => a.EntityId == CampusContext.Id);
}
// filter by schedule context
if (ScheduleContext != null)
{
var scheduleTime = new ScheduleService(rockContext).Get(ScheduleContext.Guid).StartTimeOfDay;
metricValueQueryable = metricValueQueryable.Where(a => scheduleTime == DbFunctions.CreateTime(
a.MetricValueDateTime.Value.Hour,
a.MetricValueDateTime.Value.Minute,
a.MetricValueDateTime.Value.Second
)
);
}
// filter by group context
if (GroupContext != null)
{
metricValueQueryable = metricValueQueryable.Where(a => a.ForeignId == GroupContext.Id);
}
return(metricValueQueryable.ToList());
}
public void DbFunctionsTests_CreateTime_Test()
{
this.AssertException <NotSupportedException>(() => {
this.GetOrderQuery().Select(x => DbFunctions.CreateTime(HOUR, MINUTE, SECOND)).First();
});
}
public List <SeatingSummary> SeatingByDateTime(DateTime date, TimeSpan time)
{
using (var context = new eRestaurantContext())
{
var step1 = from data in context.Tables
select new
{
Table = data.TableNumber,
Seating = data.Capacity,
// This sub-query gets the bills for walk-in customers
WalkIns = from walkIn in data.Bills
where
walkIn.BillDate.Year == date.Year &&
walkIn.BillDate.Month == date.Month &&
walkIn.BillDate.Day == date.Day
//&& walkIn.BillDate.TimeOfDay <= time
&& DbFunctions.CreateTime(walkIn.BillDate.Hour, walkIn.BillDate.Minute, walkIn.BillDate.Second) <= time &&
(!walkIn.OrderPaid.HasValue || walkIn.OrderPaid.Value >= time)
// && (!walkIn.PaidStatus || walkIn.OrderPaid >= time)
select walkIn,
// This sub-query gets the bills for reservations
Reservations = from booking in data.Reservations
from reservationParty in booking.Bills
where
reservationParty.BillDate.Year == date.Year &&
reservationParty.BillDate.Month == date.Month &&
reservationParty.BillDate.Day == date.Day
//&& reservationParty.BillDate.TimeOfDay <= time
&& DbFunctions.CreateTime(reservationParty.BillDate.Hour, reservationParty.BillDate.Minute, reservationParty.BillDate.Second) <= time &&
(!reservationParty.OrderPaid.HasValue || reservationParty.OrderPaid.Value >= time)
// && (!reservationParty.PaidStatus || reservationParty.OrderPaid >= time)
select reservationParty
};
var step2 = from data in step1.ToList() // .ToList() forces the first result set to be in memory
select new
{
Table = data.Table,
Seating = data.Seating,
CommonBilling = from info in data.WalkIns.Union(data.Reservations)
select new // info
{
BillID = info.BillID,
BillTotal = info.Items.Sum(bi => bi.Quantity * bi.SalePrice),
Waiter = info.Waiter.FirstName,
Reservation = info.Reservation
}
};
var step3 = from data in step2.ToList()
select new
{
Table = data.Table,
Seating = data.Seating,
Taken = data.CommonBilling.Count() > 0,
// .FirstOrDefault() is effectively "flattening" my collection of 1 item into a
// single object whose properties I can get in step 4 using the dot (.) operator
CommonBilling = data.CommonBilling.FirstOrDefault()
};
var step4 = from data in step3
select new SeatingSummary() // the DTO class to use in my BLL
{
Table = data.Table,
Seating = data.Seating,
Taken = data.Taken,
// use a ternary expression to conditionally get the bill id (if it exists)
BillID = data.Taken ? // if(data.Taken)
data.CommonBilling.BillID // value to use if true
: (int?)null, // value to use if false
BillTotal = data.Taken ?
data.CommonBilling.BillTotal : (decimal?)null,
WaiterName = data.Taken ? data.CommonBilling.Waiter : (string)null,
ReservationName = data.Taken ?
(data.CommonBilling.Reservation != null ?
data.CommonBilling.Reservation.CustomerName : (string)null)
: (string)null
};
return(step4.ToList());
}
}
public List <SeatingSummary> SeatingByDateTime(DateTime date, TimeSpan time)
{
using (var context = new RestaurantContext())
{
// copy/paste my code from LinqPad (adapt)
#region Adapted from LinqPad
// Find out information on the tables in the restaurant at a specific date/time
// Create a date and time object to use for sample input data
//var date = Bills.Max(b => b.BillDate).Date; // just want the date portion
//var time = Bills.Max(b => b.BillDate).TimeOfDay.Add(new TimeSpan(0, 30, 0));
//date.Add(time).Dump("The test date/time I am using");
// Step 1 - Get the table info along with any walk-in bills and reservation bills
// for a specific time slot
// since the data type of context.Tables is a DbSet<Table>,
// that means I can add a bit of extra to my LINQ query to
// declare that the variable data is of type Table
var step1 = from Table data in context.Tables
select new
{
Table = data.TableNumber,
Seating = data.Capacity,
// This sub-query gets the bills for walk-in customers
WalkIns = from Bill walkIn in data.Bills
where walkIn.BillDate.Year == date.Year &&
walkIn.BillDate.Month == date.Month &&
walkIn.BillDate.Day == date.Day
// The following won't work in EF to Entities
//&& (!walkIn.OrderPaid.HasValue || walkIn.OrderPaid.Value >= time)
//
&& DbFunctions.CreateTime(walkIn.BillDate.Hour, walkIn.BillDate.Minute, walkIn.BillDate.Second) <= time
select walkIn,
// This sub-query gets the bills for reservations
Reservations = from Reservation booking in data.Reservations
from reservationParty in booking.Bills
where reservationParty.BillDate.Year == date.Year &&
reservationParty.BillDate.Month == date.Month &&
reservationParty.BillDate.Day == date.Day &&
DbFunctions.CreateTime(reservationParty.BillDate.Hour, reservationParty.BillDate.Minute, reservationParty.BillDate.Second) <= time
//&& (!reservationParty.OrderPaid.HasValue || reservationParty.OrderPaid >= time)
select reservationParty
};
//step1.Dump("Results of Step 1");
// Step 2 - Union the walk-in bills and the reservation bills while extracting the
// relevant bill info.
// .ToList() helps resolve the error
// "Types in Union or Concat are constructed incompatibly"
var step2 = from data in step1.ToList()
select new
{
Table = data.Table,
Seating = data.Seating,
CommonBilling = from Bill info in data.WalkIns.Union(data.Reservations)
select new // trimmed down billing info
{
BillID = info.BillID,
BillTotal = info.Items.Sum(bi => bi.Quantity * bi.SalePrice),
Waiter = info.Waiter.FirstName,
Reservation = info.Reservation
}
};
//step2.Dump("Results of Step 2");
// Step 3 - Get just the first CommonBilling item
// (presumes no overlaps can occur - i.e., two groups at the same
// table at the same time)
var step3 = from data in step2.ToList()
select new
{
Table = data.Table,
Seating = data.Seating,
Taken = data.CommonBilling.Count() > 0,
// .FirstOrDefault() is effectively "flattening" my collection of 1 item into a
// single object whose properties I can get in Step 4 using the dot (.) operator
CommonBilling = data.CommonBilling.FirstOrDefault()
};
//step3.Dump();
// Step4 - Build our intended seating summary info
var step4 = from data in step3
select new SeatingSummary() // the DTO class to use in my BLL
{
Table = data.Table,
Seating = data.Seating,
Taken = data.Taken,
// use a ternary expression to conditionally get the bill id (if it exists)
BillID = data.Taken ? // if(data.Taken)
data.CommonBilling.BillID // value to use if true
: (int?)null, // value to use if false
BillTotal = data.Taken ?
data.CommonBilling.BillTotal : (decimal?)null,
Waiter = data.Taken ? data.CommonBilling.Waiter : (string)null,
ReservationName = data.Taken ?
(data.CommonBilling.Reservation != null ?
data.CommonBilling.Reservation.CustomerName :
(string)null)
: (string)null
};
//step4.Dump("The final, boiled-down list of table occupancy at a point in time");
#endregion
return(step4.ToList());
}
}
public List <OACYPerTransactionDTO> GetByPostDateEffDate(string pipelineDuns, DateTime?postdate, DateTime?effdate, string keyword, string Cycle)
{
var pDate = postdate.GetValueOrDefault().Date;
var pTime = postdate.GetValueOrDefault().TimeOfDay;
var eDate = effdate.GetValueOrDefault().Date;
UprdDbEntities1 db = new UprdDbEntities1();
var query = db.OACYPerTransactions.Where(a => a.TransactionServiceProvider == pipelineDuns).AsQueryable();
if (!string.IsNullOrEmpty(keyword))
{
query = query.Where(a => a.Loc.Contains(keyword) || a.LocName.Contains(keyword));
}
if (!string.IsNullOrEmpty(Cycle))
{
query = query.Where(a => a.CycleIndicator == Cycle);
}
if (!string.IsNullOrEmpty(pDate.ToString()) && pDate != DateTime.MinValue && TimeSpan.MinValue != pTime)
{
query = query.Where(a => DbFunctions.TruncateTime(a.PostingDateTime) == pDate && DbFunctions.CreateTime(a.PostingDateTime.Value.Hour, a.PostingDateTime.Value.Minute, a.PostingDateTime.Value.Second) >= pTime);
}
if (!string.IsNullOrEmpty(eDate.ToString()) && eDate != DateTime.MinValue)
{
query = query.Where(a => DbFunctions.TruncateTime(a.EffectiveGasDayTime) == eDate);
}
return(query.Select(o => new OACYPerTransactionDTO
{
OACYID = o.OACYID,
TransactionID = o.TransactionID,
ReceiceFileID = o.ReceiceFileID,
CreatedDate = o.CreatedDate,
TransactionServiceProviderPropCode = o.TransactionServiceProviderPropCode,
TransactionServiceProvider = o.TransactionServiceProvider,
PostingDate = o.PostingDateTime,
EffectiveGasDay = o.EffectiveGasDayTime,
Loc = o.Loc,
LocName = o.LocName,
LocZn = o.LocZn,
FlowIndicator = o.FlowIndicator,
LocPropDesc = o.LocPropDesc,
LocQTIDesc = o.LocQTIDesc,
MeasurementBasis = o.MeasurementBasis,
ITIndicator = o.ITIndicator,
AllQtyAvailableIndicator = o.AllQtyAvailableIndicator,
DesignCapacity = o.DesignCapacity,
OperatingCapacity = o.OperatingCapacity,
TotalScheduleQty = o.TotalScheduleQty,
OperationallyAvailableQty = o.OperationallyAvailableQty,
PipelineID = o.PipelineID,
CycleIndicator = o.CycleIndicator,
AvailablePercentage = o.AvailablePercentage
}).ToList());
//keyword = keyword.Trim();
// IQueryable<OACYPerTransaction> oacylist;
//if (!string.IsNullOrEmpty(Cycle) && !string.IsNullOrEmpty(keyword) && pDate != DateTime.MinValue && eDate != DateTime.MinValue)
//{
// return dbcontext.OACYPerTransactions.Where(a => a.TransactionServiceProvider == pipelineDuns
// && DbFunctions.TruncateTime(a.PostingDateTime) == pDate
// && DbFunctions.TruncateTime(a.EffectiveGasDayTime) == eDate
// && (((a.LocName ?? "").ToLower().Contains(keyword.ToLower())) || ((a.Loc ?? "").ToLower().Contains(keyword.ToLower())))
// && (((a.CycleIndicator ?? "").ToLower().Contains(Cycle.ToLower()))));
//}
//else if (string.IsNullOrEmpty(Cycle) && string.IsNullOrEmpty(keyword) && pDate == DateTime.MinValue && eDate == DateTime.MinValue)
//{
// var count = dbcontext.OACYPerTransactions.Where(a => a.TransactionServiceProvider == pipelineDuns).Count();
// if (count > 0)
// {
// DateTime? recentdate = GetRecentPostDateUsngDuns(pipelineDuns);
// return GetByPostDateEffDate(pipelineDuns, recentdate, null, string.Empty, string.Empty);
// }
//}
//else if (!string.IsNullOrEmpty(Cycle) && !string.IsNullOrEmpty(keyword) && pDate != DateTime.MinValue && eDate == DateTime.MinValue)
//{
// return dbcontext.OACYPerTransactions.Where(a => a.TransactionServiceProvider == pipelineDuns
// && DbFunctions.TruncateTime(a.PostingDateTime) == pDate
// && (((a.LocName ?? "").ToLower().Contains(keyword.ToLower())) || ((a.Loc ?? "").ToLower().Contains(keyword.ToLower())))
// && (((a.CycleIndicator ?? "").ToLower().Contains(Cycle.ToLower()))));
//}
//else if (string.IsNullOrEmpty(Cycle) && !string.IsNullOrEmpty(keyword) && pDate != DateTime.MinValue && eDate != DateTime.MinValue)
//{
// return dbcontext.OACYPerTransactions.Where(a => a.TransactionServiceProvider == pipelineDuns
// && DbFunctions.TruncateTime(a.PostingDateTime) == pDate
// && DbFunctions.TruncateTime(a.EffectiveGasDayTime) == eDate
// && (((a.LocName ?? "").ToLower().Contains(keyword.ToLower())) || ((a.Loc ?? "").ToLower().Contains(keyword.ToLower()))));
//}
//else if (!string.IsNullOrEmpty(Cycle) && string.IsNullOrEmpty(keyword) && pDate != DateTime.MinValue && eDate != DateTime.MinValue)
//{
// return dbcontext.OACYPerTransactions.Where(a => a.TransactionServiceProvider == pipelineDuns
// && DbFunctions.TruncateTime(a.PostingDateTime) == pDate
// && DbFunctions.TruncateTime(a.EffectiveGasDayTime) == eDate
// && (((a.CycleIndicator ?? "").ToLower().Contains(Cycle.ToLower()))));
//}
//else if (!string.IsNullOrEmpty(Cycle) && !string.IsNullOrEmpty(keyword) && pDate == DateTime.MinValue && eDate != DateTime.MinValue)
//{
// return dbcontext.OACYPerTransactions.Where(a => a.TransactionServiceProvider == pipelineDuns
// && DbFunctions.TruncateTime(a.EffectiveGasDayTime) == eDate
// && (((a.LocName ?? "").ToLower().Contains(keyword.ToLower())) || ((a.Loc ?? "").ToLower().Contains(keyword.ToLower())))
// && (((a.CycleIndicator ?? "").ToLower().Contains(Cycle.ToLower()))));
//}
//else if (!string.IsNullOrEmpty(Cycle) && !string.IsNullOrEmpty(keyword) && pDate == DateTime.MinValue && eDate == DateTime.MinValue)
//{
// return dbcontext.OACYPerTransactions.Where(a => a.TransactionServiceProvider == pipelineDuns
// && (((a.LocName ?? "").ToLower().Contains(keyword.ToLower())) || ((a.Loc ?? "").ToLower().Contains(keyword.ToLower())))
// && (((a.CycleIndicator ?? "").ToLower().Contains(Cycle.ToLower()))));
//}
//else if (!string.IsNullOrEmpty(Cycle) && string.IsNullOrEmpty(keyword) && pDate != DateTime.MinValue && eDate == DateTime.MinValue)
//{
// return dbcontext.OACYPerTransactions.Where(a => a.TransactionServiceProvider == pipelineDuns
// && DbFunctions.TruncateTime(a.PostingDateTime) == pDate
// && (((a.CycleIndicator ?? "").ToLower().Contains(Cycle.ToLower()))));
//}
//else if (string.IsNullOrEmpty(Cycle) && !string.IsNullOrEmpty(keyword) && pDate == DateTime.MinValue && eDate != DateTime.MinValue)
//{
// return dbcontext.OACYPerTransactions.Where(a => a.TransactionServiceProvider == pipelineDuns
// && DbFunctions.TruncateTime(a.EffectiveGasDayTime) == eDate
// && (((a.LocName ?? "").ToLower().Contains(keyword.ToLower())) || ((a.Loc ?? "").ToLower().Contains(keyword.ToLower()))));
//}
//else if (string.IsNullOrEmpty(Cycle) && string.IsNullOrEmpty(keyword) && pDate != DateTime.MinValue && eDate != DateTime.MinValue)
//{
// return dbcontext.OACYPerTransactions.Where(a => a.TransactionServiceProvider == pipelineDuns
// && DbFunctions.TruncateTime(a.PostingDateTime) == pDate
// && DbFunctions.TruncateTime(a.EffectiveGasDayTime) == eDate);
//}
//else if (string.IsNullOrEmpty(Cycle) && !string.IsNullOrEmpty(keyword) && pDate != DateTime.MinValue && eDate == DateTime.MinValue)
//{
// return dbcontext.OACYPerTransactions.Where(a => a.TransactionServiceProvider == pipelineDuns
// && DbFunctions.TruncateTime(a.PostingDateTime) == pDate
// && (((a.LocName ?? "").ToLower().Contains(keyword.ToLower())) || ((a.Loc ?? "").ToLower().Contains(keyword.ToLower()))));
//}
//else if (!string.IsNullOrEmpty(Cycle) && string.IsNullOrEmpty(keyword) && pDate == DateTime.MinValue && eDate == DateTime.MinValue)
//{
// return dbcontext.OACYPerTransactions.Where(a => a.TransactionServiceProvider == pipelineDuns
// && (((a.CycleIndicator ?? "").ToLower().Contains(Cycle.ToLower()))));
//}
//else if (string.IsNullOrEmpty(Cycle) && !string.IsNullOrEmpty(keyword) && pDate == DateTime.MinValue && eDate == DateTime.MinValue)
//{
// return dbcontext.OACYPerTransactions.Where(a => a.TransactionServiceProvider == pipelineDuns
// && (((a.LocName ?? "").ToLower().Contains(keyword.ToLower())) || ((a.Loc ?? "").ToLower().Contains(keyword.ToLower()))));
//}
//else if (string.IsNullOrEmpty(Cycle) && string.IsNullOrEmpty(keyword) && pDate != DateTime.MinValue && eDate == DateTime.MinValue)
//{
// return dbcontext.OACYPerTransactions.Where(a => a.TransactionServiceProvider == pipelineDuns
// && DbFunctions.TruncateTime(a.PostingDateTime) == pDate);
//}
//else if (string.IsNullOrEmpty(Cycle) && string.IsNullOrEmpty(keyword) && pDate == DateTime.MinValue && eDate != DateTime.MinValue)
//{
// return dbcontext.OACYPerTransactions.Where(a => a.TransactionServiceProvider == pipelineDuns
// && DbFunctions.TruncateTime(a.EffectiveGasDayTime) == eDate);
//}
//return new List<OACYPerTransaction>().AsQueryable();
}
public IHttpActionResult GetEvents(
//Tags
//[FromUri] List<TagDto> tags = null,
[FromUri] string[] tags = null,
// Paging parameters
int page = 1,
int itemsPerPage = 10,
string sortBy = "eventStart",
bool reverse = false,
// Search Parameters
bool eligibleOnly = false,
string searchText = null,
string gender = null,
int?ageMin = null,
int?ageMax = null,
int?cPartMin = null,
int?cPartMax = null,
//int? PartMin = null,
//int? PartMax = null,
double?posLat = null,
double?posLng = null,
double radius = 10000,
bool?approvalReq = null,
// Date-Time Parameters
DateTime?dateAfter = null,
DateTime?dateBefore = null,
DateTime?timeAfter = null,
DateTime?timeBefore = null)
{
// Because: Without position the query is meaningless
if (posLat == null || posLng == null)
{
return(BadRequest("A location(Lat/Lng) is required"));
}
//// Prepare tagList - Not needed ?
//tags = tags ?? new string[0];
// Prepare recieved coordinates
var coordinates = string.Format(CultureInfo.InvariantCulture, "POINT({0} {1})", posLng, posLat);
int timeResult = 0;
// if both timeAfter and timeBefore !=null, check if timeAfter>timeBefore
if (timeAfter.HasValue && timeBefore.HasValue)
{
// timeResult: (t1 < t2 == -1) (t1 == t2 == 0) (t1 > t2 == 1)
timeResult = TimeSpan.Compare(timeAfter.Value.TimeOfDay, timeBefore.Value.TimeOfDay);
}
int userAge = 0;
string notUserGender = null;
if (eligibleOnly == true)
{
// USER: Get current user - (Check if admin)
var user = this.AppUserManager.FindById(User.Identity.GetUserId());
//bool isAdmin = User.IsInRole("Admin");
// USER: Get DateOfBirth, current date.
DateTime userBirth = user.DateOfBirth;
DateTime today = DateTime.Today;
notUserGender = (user.Gender == "female") ? "male" : "female";
// Calc: UserAge
userAge = today.Year - userBirth.Year;
if (userBirth > today.AddYears(-userAge))
{
userAge--;
}
}
var query = db.Events.Where(e =>
e != null &&
//e.Status == open && *******ACTIVATE Status*****
(eligibleOnly == false ||
// Conditions based on user
e.AgeMin <= userAge &&
e.AgeMax >= userAge &&
e.Gender != notUserGender) &&
// Conditions based on query string + (eventstart > Now)
(ageMin == null ||
e.AgeMin >= ageMin) &&
(ageMax == null ||
e.AgeMax <= ageMax) &&
(approvalReq == null ||
e.ApprovalReq == approvalReq) &&
e.Coordinates.Distance(DbGeography.FromText(coordinates, 4326)) < radius &&
//(tags.Count() == 0 ||
//e.Tags.Any(eTag => tags.Any(pTag => pTag.Name == eTag.Name))) &&
(tags.Count() == 0 ||
e.Tags.Any(eTag => tags.Any(pTag => pTag == eTag.Name))) &&
// Could: Treat below DT block as 1 scenario.
// Then: If betweenDateAfterTime == true && onDateBetweenTime == true
// Then: Query must satisfy betweenDateAfterTime block OR onDateBetweenTime block
e.EventStart >= DateTime.Now && // This will be server time? != Users local time => Must sync on save or on query.
(dateAfter == null ||
DbFunctions.TruncateTime(e.EventStart) >= DbFunctions.TruncateTime(dateAfter)) &&
(dateBefore == null ||
DbFunctions.TruncateTime(e.EventStart) <= DbFunctions.TruncateTime(dateBefore)) &&
// Run this if timeAfter && timeBefore HasValue && timeAfter>timeBefore
(timeResult != 1 ||
(DbFunctions.CreateTime(e.EventStart.Hour, e.EventStart.Minute, e.EventStart.Second) >= DbFunctions.CreateTime(timeAfter.Value.Hour, timeAfter.Value.Minute, timeAfter.Value.Second))
||
(DbFunctions.CreateTime(e.EventStart.Hour, e.EventStart.Minute, e.EventStart.Second) <= DbFunctions.CreateTime(timeBefore.Value.Hour, timeBefore.Value.Minute, timeBefore.Value.Second))) &&
// Else, run this
(timeResult == 1 ||
(timeAfter == null ||
DbFunctions.CreateTime(e.EventStart.Hour, e.EventStart.Minute, e.EventStart.Second) >= DbFunctions.CreateTime(timeAfter.Value.Hour, timeAfter.Value.Minute, timeAfter.Value.Second)) &&
(timeBefore == null ||
DbFunctions.CreateTime(e.EventStart.Hour, e.EventStart.Minute, e.EventStart.Second) <= DbFunctions.CreateTime(timeBefore.Value.Hour, timeBefore.Value.Minute, timeBefore.Value.Second))) &&
(gender == null ||
//gender == "all" || // (Result of "all": "ownGender" + "all".) (NEW/now: Result of "all": all only.)
e.Gender == gender) &&
(cPartMin == null ||
e.Participants.Count >= cPartMin) && // .Count vs .Count() ?
(cPartMax == null ||
e.Participants.Count <= cPartMax)
//&&
//(partMin == null ||
//e.PartMin >= partMin) &&
//(partMax == null ||
//e.PartMax <= partMax)
//(searchText == null ||
//e.Name.ToLower().Contains(searchText.ToLower()))
).ToList().Select(e => this.TheModelFactory.Create(e, ""));
// sorting (done with the System.Linq.Dynamic library available on NuGet)
query = query.OrderBy(sortBy + (reverse ? " descending" : ""));
var queryPaged = query.Skip((page - 1) * itemsPerPage).Take(itemsPerPage);
var json = new
{
count = query.Count(),
events = queryPaged
};
return(Ok(json));
}
public IHttpActionResult GetUnscByCriteria([FromBody] UnscDataFilter criteria)
{
var source = (dynamic)null;
try
{
//SortingPagingInfo sortingPagingInfo = new SortingPagingInfo();
//sortingPagingInfo.SortField = criteria.sort;
//sortingPagingInfo.SortDirection = criteria.SortDirection;
//sortingPagingInfo.PageSize = criteria.size;
//sortingPagingInfo.CurrentPageIndex = criteria.page;
//UprdUnscRepository uprdUnscRepository = new UprdUnscRepository();
var pTime = criteria.postTime.GetValueOrDefault().TimeOfDay;
UprdDbEntities1 db = new UprdDbEntities1();
var query = db.UnscPerTransactions.Where(a => a.TransactionServiceProvider == criteria.PipelineDuns).AsQueryable();
if (!string.IsNullOrEmpty(criteria.keyword))
{
query = query.Where(a => a.Loc.Contains(criteria.keyword) || a.LocName.Contains(criteria.keyword));
}
if (!string.IsNullOrEmpty(criteria.postStartDate.ToString()) && TimeSpan.MinValue != pTime)
{
query = query.Where(a => DbFunctions.TruncateTime(a.PostingDateTime) == DbFunctions.TruncateTime(criteria.postStartDate) &&
DbFunctions.CreateTime(a.PostingDateTime.Value.Hour, a.PostingDateTime.Value.Minute, a.PostingDateTime.Value.Second) >= pTime);
}
if (!string.IsNullOrEmpty(criteria.EffectiveStartDate.ToString()))
{
query = query.Where(a => DbFunctions.TruncateTime(a.EffectiveGasDayTime) == DbFunctions.TruncateTime(criteria.EffectiveStartDate));
}
if (!string.IsNullOrEmpty(criteria.EffectiveEndDate.ToString()))
{
query = query.Where(a => DbFunctions.TruncateTime(a.EndingEffectiveDay) == DbFunctions.TruncateTime(criteria.EffectiveEndDate));
}
var data = query.Select(a => new UnscPerTransactionDTO {
TransactionID = a.TransactionID,
//AvailablePercentage=
ChangePercentage = a.ChangePercentage,
CreatedDate = a.CreatedDate,
DUNSNo = a.TransactionServiceProvider,
EffectiveGasDay = a.EffectiveGasDayTime,
//EffectiveGasDayTime=a.EffectiveGasDayTime,
EndingEffectiveDay = a.EndingEffectiveDay,
Loc = a.Loc,
LocName = a.LocName,
LocPurpDesc = a.LocPurpDesc,
LocQTIDesc = a.LocQTIDesc,
LocZn = a.LocZn,
MeasBasisDesc = a.MeasBasisDesc,
PipelineID = a.PipelineID,
//PipelineNameDuns=a.
PostingDate = a.PostingDateTime,
//PostingDateTime=a.PostingDateTime,
TotalDesignCapacity = a.TotalDesignCapacity,
TransactionServiceProvider = a.TransactionServiceProvider,
TransactionServiceProviderPropCode = a.TransactionServiceProviderPropCode,
UnsubscribeCapacity = a.UnsubscribeCapacity
}).ToList();
UnscResultDTO result = new UnscResultDTO();
result.unscPerTransactionDTO = (data != null && data.Count > 0) ? data : new List <UnscPerTransactionDTO>();
result.RecordCount = (data != null && data.Count > 0) ? data.Count : 0;
source = result;
// if (!string.IsNullOrEmpty(criteria.PipelineDuns))
// {
// source = uprdUnscRepository.GetUnscListWithPaging(criteria.PipelineDuns, criteria.keyword, criteria.postStartDate, criteria.EffectiveStartDate, criteria.EffectiveEndDate, sortingPagingInfo);
// }
// int count = sortingPagingInfo.PageCount;
// int CurrentPage = sortingPagingInfo.CurrentPageIndex;
// int PageSize = sortingPagingInfo.PageSize;
// int TotalCount = count;
// int TotalPages = (int)Math.Ceiling(count / (double)PageSize);
// // if CurrentPage is greater than 1 means it has previousPage
// var previousPage = CurrentPage > 1 ? "Yes" : "No";
// // if TotalPages is greater than CurrentPage means it has nextPage
// var nextPage = CurrentPage < TotalPages ? "Yes" : "No";
// // Object which we are going to send in header
// var paginationMetadata = new
// {
// totalCount = TotalCount,
// pageSize = PageSize,
// currentPage = CurrentPage,
// totalPages = TotalPages,
// previousPage,
// nextPage
// };
// // Setting Header
// HttpContext.Current.Response.Headers.Add("Paging-Headers", JsonConvert.SerializeObject(paginationMetadata));
}
catch (Exception ex)
{
throw ex;
}
//if (source == null)
//{
// var resp = new HttpResponseMessage(HttpStatusCode.NotFound)
// {
// ReasonPhrase = "Records Not Found."
// };
// throw new HttpResponseException(resp);
//}
return(Ok(source));
}
