public IQueryable <TeamPlayer> GetLeagueLadder(string leagueName, DateTime date)
{
var ret = new Dictionary <string, TeamPlayer>();
var tournamentEvent =
GetQuery <TournamentEvent>(x => x.Tournament.TournamentName == leagueName &&
EntityFunctions.AddDays(x.StartDate, -10) <= date &&
date <= EntityFunctions.AddDays(x.EndDate, 10))
.FirstOrDefault();
GetQuery <Match>(x => x.TournamentEvent.EventName == tournamentEvent.EventName)
.Include(x => x.TeamsPlayerA)
.Include(x => x.TeamsPlayerB)
.ToList()
.ForEach(x =>
{
if (!ret.ContainsKey(x.TeamsPlayerA.Slug))
{
ret.Add(x.TeamsPlayerA.Slug, x.TeamsPlayerA);
}
if (!ret.ContainsKey(x.TeamsPlayerB.Slug))
{
ret.Add(x.TeamsPlayerB.Slug, x.TeamsPlayerB);
}
});
return(ret.Values.AsQueryable());
}
public ActionResult HetThoiGianDangKy()
{
//lọc ra danh sách từ ngày đăng ký dến DateTime.Now > 7
var list = db.CHITIETDANGKY.Where(d => d.NGAY_DK < EntityFunctions.AddDays(DateTime.Now, -7) && d.XACNHAN == false);
return(View(list));
}
private static IQueryable <Associate> AssociateAvailability(AssociateSearchModel search, IQueryable <Associate> associates)
{
if (search.AvailableFrom.HasValue && search.AvailableTo.HasValue)
{
associates = from a in associates
where !a.Individual.Any(i => i.StartDate < search.AvailableTo.Value && (
i.EndDate.Value > search.AvailableFrom.Value ||
(EntityFunctions.AddDays(i.EndDate.Value, (i.NoticeAmount * -1)) > search.AvailableFrom.Value && i.NoticeIntervalId == (int)NoticeIntervalEnum.Days) ||
(EntityFunctions.AddDays(i.EndDate.Value, (i.NoticeAmount * -7)) > search.AvailableFrom.Value && i.NoticeIntervalId == (int)NoticeIntervalEnum.Weeks) ||
(EntityFunctions.AddMonths(i.EndDate.Value, (i.NoticeAmount * -1)) > search.AvailableFrom.Value && i.NoticeIntervalId == (int)NoticeIntervalEnum.Months)
)
)
select a;
}
if (search.NoticePeriod.HasValue && search.NoticeIntervalId.HasValue)
{
associates = from a in associates
where a.Individual.Any(i => (i.Role.NoticeIntervalId == search.NoticeIntervalId.Value || i.NoticeIntervalId == search.NoticeIntervalId.Value) &&
((i.Role.NoticeAmount == null && i.NoticeAmount == null) || i.Role.NoticeAmount <= search.NoticePeriod.Value || i.NoticeAmount <= search.NoticePeriod.Value))
select a;
}
return(associates);
}
static void Main(string[] args)
{
using (var ctx = new MyContext())
{
if (!ctx.Entities.Any())
{
ctx.Entities.Add(new MyEntity()
{
Date = new DateTime(2000, 1, 1)
});
ctx.Entities.Add(new MyEntity()
{
Date = new DateTime(2012, 10, 1)
});
ctx.Entities.Add(new MyEntity()
{
Date = new DateTime(2012, 12, 12)
});
ctx.SaveChanges();
}
var q = from e in ctx.Entities
where e.Date > EntityFunctions.AddDays(new DateTime(2012, 10, 1), 10)
select e;
foreach (var entity in q)
{
Console.WriteLine("{0} {1}", entity.Id, entity.Date);
}
}
}
public virtual ActionResult Index()
{
IQueryable <DogEvent> events =
_dogEventRepo.Where(
f => f.EventDate > DateTime.UtcNow && f.EventDate < EntityFunctions.AddDays(DateTime.Now, 7));
return(View(events));
}
public TournamentEvent GetTournamentEventFromTournamentAndYear(int year, string tournamentName)
{
var tournamentEvent = GetQuery <TournamentEvent>(
t => t.Tournament.TournamentName == tournamentName &&
(EntityFunctions.AddDays(t.StartDate, 3)).Value.Year == year)//for tennis
.FirstOrDefault();
return(tournamentEvent);
}
public ActionResult InTime()
{
using (var ctx = new AuctionSiteDBEntities())
{
int Iduser = CurrentContext.GetCurUser().ID;
var list = ctx.Products.Where(p => ((p.UserID == Iduser) &&
(DateTime.Now <= EntityFunctions.AddDays(p.TimePost, p.IntervalTime))))
.ToList();
return(View(list));
}
}
public IEnumerable <Employee> GetEmployeeFree(DateTime datePick, int time)
{
var emps = Context.Tours.Where(t => (t.TimeStart >= datePick && t.TimeStart <= EntityFunctions.AddDays(datePick, time)) ||
EntityFunctions.AddDays(t.TimeStart, t.Time) >= datePick &&
EntityFunctions.AddDays(t.TimeStart, t.Time) <= EntityFunctions.AddDays(datePick, time))
.Select(e => e.EmployeeId);
var employees = Context.Employees.Where(e => !emps.Contains(e.Id)).ToList();
return(employees);
}
public List <Attendance> GetAttendancesForEmployee(Employee e)
{
using (var db = new SystemContext())
{
List <Attendance> attendances = (from a in db.Attendances
where a.Employee.Id == e.Id &&
a.LoginDate <= DateTime.Now &&
a.LoginDate >= EntityFunctions.AddDays(a.LoginDate, -7)
select a).ToList <Attendance>();
return(attendances);
}
}
public IEnumerable <Tournament> GetDaysTournaments(DateTime matchDate, string sport)
{
var tournaments = GetQuery <TournamentEvent>(t => EntityFunctions.AddDays(t.StartDate, 0) <= matchDate &&
EntityFunctions.AddDays(t.EndDate, 0) >= matchDate &&
t.Tournament.Competition.Sport.SportName == sport
/* &&
* t.TournamentInProgress*/)
.Select(t => t.Tournament)
.ToList();
return(tournaments);
}
/// <summary>
/// Gets the days difference.
/// </summary>
public void GetDaysDiff()
{
int exportedFilesRemainingDays = int.Parse(Utils.GetSystemValue("ExportedFilesRemainingDays"));
int completedStatusCodeId = Utils.GetCodeIdByCodeValue("ExportFileStatus", "COMPLETED");
var x = (from ef in DataContext.ExportFiles
where ef.ExportFileStatusCodeId == completedStatusCodeId && ef.IsActive
select new
{
cd = ef.CreatedDate,
ad = EntityFunctions.AddDays(ef.CreatedDate, exportedFilesRemainingDays),
x = (EntityFunctions.DiffDays(EntityFunctions.AddDays(ef.CreatedDate, exportedFilesRemainingDays), Utils.Now))
});
}
public void DateTimeOffsetAddDays()
{
DateTimeOffset offset = stored.AddDays(-1);
#if !EFOLD
var q = this.Entities
.Where(x =>
DbFunctions.AddDays(x.Offset, -1) == offset);
#else
var q = this.Entities
.Where(x =>
EntityFunctions.AddDays(x.Offset, -1) == offset);
#endif
q.Should().NotBeEmpty();
}
public void DateTimeAddDays()
{
DateTime date = stored.AddDays(-1);
#if !EFOLD
var q = this.Entities
.Where(x =>
DbFunctions.AddDays(x.DateTime, -1) == date);
#else
var q = this.Entities
.Where(x =>
EntityFunctions.AddDays(x.DateTime, -1) == date);
#endif
q.Should().NotBeEmpty();
}
public ActionResult MarkItemsAsRead(string timeFrame)
{
IQueryable <SubscriptionPost> subscriptionPostsUnread = _dbContext.SubscriptionPosts
.Where(x => x.Subscription.UserSubscriptions
.Any(y => y.User.Email == WebSecurity.CurrentUserName) &&
!x.PostsRead.Any(y => y.UserSubscription.User.Email == WebSecurity.CurrentUserName &&
y.SubscriptionPost.Id == x.Id));
switch (timeFrame)
{
case "DAY":
subscriptionPostsUnread =
subscriptionPostsUnread
.Where(x => x.PublishDateUTC <= EntityFunctions.AddDays(DateTime.UtcNow, -1));
break;
case "WEEK":
subscriptionPostsUnread =
subscriptionPostsUnread
.Where(x => x.PublishDateUTC <= EntityFunctions.AddDays(DateTime.UtcNow, -7));
break;
case "MONTH":
subscriptionPostsUnread =
subscriptionPostsUnread
.Where(x => x.PublishDateUTC <= EntityFunctions.AddMonths(DateTime.UtcNow, -1));
break;
}
foreach (var subscriptionPost in subscriptionPostsUnread)
{
var userSubscription = subscriptionPost.Subscription.UserSubscriptions
.Single(x => x.User.Email == WebSecurity.CurrentUserName);
userSubscription.UnreadItems--;
var postRead = new PostRead
{
ReadOnUTC = DateTime.UtcNow,
SubscriptionPost = subscriptionPost,
UserSubscription = userSubscription
};
_dbContext.PostsRead.Add(postRead);
}
_dbContext.SaveChanges();
return(Content("MARKED"));
}
public Form_Request_SqlFunctions()
{
InitializeComponent();
var context = new ExampleModel(DbConnectionFactory.CreateTransient());
DateTime monday = new DateTime(2018, 4, 2);
MyEntity test = new MyEntity {
Id = 1, Name = "Example", WeekDay = (int)DayOfWeek.Monday
};
context.MyEntities.Add(test);
context.SaveChanges();
var count = context.MyEntities.Count();
var find = context.MyEntities.Where(x => monday < EntityFunctions.AddDays(monday, 1)).FirstOrDefault();
}
public void DateTimeFunction()
{
Context context = new Context(SetUpCodeFirst.Connection);
IQueryable <TableWithSeveralFieldsType> insertedRecord = GetInsertedRecordQueryable(context);
Assert.AreEqual(insertedRecord.Select(c => new { c.MyDateTime.Year }).First().Year, 1969);
Assert.AreEqual(insertedRecord.Select(c => new { c.MyDateTime.Month }).First().Month, 09);
Assert.AreEqual(insertedRecord.Select(c => new { c.MyDateTime.Day }).First().Day, 15);
Assert.AreEqual(insertedRecord.Select(c => new { c.MyDateTime.Hour }).First().Hour, 20);
Assert.AreEqual(insertedRecord.Select(c => new { c.MyDateTime.Minute }).First().Minute, 3);
Assert.AreEqual(insertedRecord.Select(c => new { c.MyDateTime.Second }).First().Second, 19);
#pragma warning disable 618
Assert.AreEqual(insertedRecord.Select(c => new { Date = EntityFunctions.AddDays(c.MyDateTime, 4) }).First().Date.Value.Day, 19);
Assert.AreEqual(insertedRecord.Select(c => new { ElapsedDays = EntityFunctions.DiffDays(c.MyDateTime, c.MyDateTime) }).First().ElapsedDays.Value, 0);
#pragma warning restore 618
context.Dispose();
}
public Dictionary <string, int> CalculateWeeklyExpense()
{
List <Operation> lstEmployee = new List <Operation>();
Dictionary <string, int> dictWeeklySum = new Dictionary <string, int>();
int foodSum = db.Operations.Where
(cat => cat.type == "Food" && (cat.creationDate > EntityFunctions.AddDays(DateTime.Now, -7)))
.Sum(cat => (int?)cat.amount) ?? 0;
int shoppingSum = db.Operations.Where
(cat => cat.type == "Shopping" && (cat.creationDate > EntityFunctions.AddDays(DateTime.Now, -7)))
.Sum(cat => (int?)cat.amount) ?? 0;
int travelSum = db.Operations.Where
(cat => cat.type == "Travel" && (cat.creationDate > EntityFunctions.AddDays(DateTime.Now, -7)))
.Sum(cat => (int?)cat.amount) ?? 0;
int healthSum = db.Operations.Where
(cat => cat.type == "Health" && (cat.creationDate > EntityFunctions.AddDays(DateTime.Now, -7)))
.Sum(cat => (int?)cat.amount) ?? 0;
//int travelSum = db.Operations.Where
// (cat => cat.type == "Travel" && (cat.creationDate > DateTime.Now.AddDays(-28)))
// .Select(cat => cat.amount)
// .Sum();
//int healthSum = db.Operations.Where
// (cat => cat.type == "Health" && (cat.creationDate > DateTime.Now.AddDays(-28)))
// .Select(cat => cat.amount)
// .Sum();
dictWeeklySum.Add("Food", foodSum);
dictWeeklySum.Add("Shopping", shoppingSum);
dictWeeklySum.Add("Travel", travelSum);
dictWeeklySum.Add("Health", healthSum);
return(dictWeeklySum);
}
private void TotalTask_Status()
{
ViewBag.Users = db.tbl_securityUserMaster.Count(p => p.employeeID != "-1");
if (sUser_ID != "")
{
decimal dCount = db.tbl_pmsTxTask.Where(p => p.status_ID == "1" && p.assignedUser_ID == sUser_ID).Count();
ViewBag.NewStatus_Count = dCount;
DateTime dtNow = DateTime.Now.Date;
decimal dDeadlineCount = db.tbl_pmsTxTask.Where(p => p.DeadlineDate >= dtNow && p.DeadlineDate <= EntityFunctions.AddDays(dtNow, 7) && p.assignedUser_ID == sUser_ID).Count();
ViewBag.Deadline_Count = dDeadlineCount;
decimal dDueCount = db.tbl_pmsTxTask.Where(p => DateTime.Now > p.DeadlineDate && p.assignedUser_ID == sUser_ID).Count();
ViewBag.Due_Count = dDueCount;
}
}
static void Querying()
{
using (var ctx = new ProjectsContext())
{
//id
var bigProject = ctx.Projects.Find(1);
//LINQ
var usersInProjectWithLINQ = (from r in ctx.Resources
from p in ctx.Projects
where p.Name == "Big Project" &&
r.ProjectResources.Select(x => x.Project).Contains(p)
select r).ToList();
var projectsByName = ctx.Projects.Where(x => x.Name == "Big Project").ToList();
var customersWithoutProjects = ctx.Customers.Where(c => c.Projects.Any() == false).ToList();
//or
var resourcesKnowingVBOrCS = ctx.Technologies.Where(t => t.Name == "VB.NET" || t.Name == "C#").SelectMany(x => x.Resources).Select(x => x.Name).ToList();
//grouping
var resourcesGroupedByProjectRole = ctx.Projects.SelectMany(x => x.ProjectResources).Select(x => new { Role = x.Role, Resource = x.Resource.Name }).GroupBy(x => x.Role).Select(x => new { Role = x.Key, Resources = x }).ToList();
//grouping and counting
var projectsByCustomer = ctx.Projects.GroupBy(x => x.Customer).Select(x => new { Customer = x.Key.Name, Count = x.Count() }).ToList();
//top 10 customers having more projects in descending order
var top10CustomersWithMoreProjects = ctx.Projects.GroupBy(x => x.Customer.Name).Select(x => new { x.Key, Count = x.Count() }).OrderByDescending(x => x.Count).Take(10).ToList();
//grouping by date part and counting
var countOfProjectsByMonth = ctx.Projects.GroupBy(x => EntityFunctions.CreateDateTime(x.Start.Year, x.Start.Month, 1, 0, 0, 0)).Select(x => new { Month = x.Key, Count = x.Count() }).ToList();
//group and count the days between two dates
var projectsGroupedByDurationDays = ctx.Projects.Where(x => x.End != null).GroupBy(x => EntityFunctions.DiffDays(x.Start, x.End.Value)).Select(x => new { Duration = x.Key, List = x }).ToList();
//order by extension method
var technologiesSortedByName = ctx.Technologies.OrderBy("Name").ThenBy("TechnologyId").ToList();
//create a base query
var projectsQuery = from p in ctx.Projects select p;
//add sorting
var projectsSortedByDateQuery = projectsQuery.OrderBy(x => x.Start);
//execute and get the sorted results
var projectsSortedByDateResults = projectsSortedByDateQuery.ToList();
//add paging
var projectsWithPagingQuery = projectsQuery.OrderBy(x => x.Start).Take(5).Skip(0);
//execute and get the first 5 results
var projectsWithPagingResults = projectsWithPagingQuery.ToList();
//add a restriction
var projectsStartingAWeekAgoQuery = projectsQuery.Where(x => x.Start >= EntityFunctions.AddDays(DateTime.Today, -7));
//execute and get the projects that started a week ago
var projectsStartingAWeekAgoResults = projectsStartingAWeekAgoQuery.ToList();
//eager load properties
var resourcesIncludingTechnologies = ctx.Resources.Include(x => x.Technologies).ToList();
var projectsIncludingCustomers = ctx.Projects.Include("Customer").ToList();
//distinct
var roles = ctx.Resources.SelectMany(x => x.ProjectResources).Where(x => x.Resource.Name == "Ricardo Peres").Select(x => x.Role).Distinct().ToList();
//check existence
var existsProjectBySomeCustomer = ctx.Projects.Any(x => x.Customer.Name == "Some Customer");
//count
var numberOfClosedProjects = ctx.Projects.Where(x => x.End != null && x.End < DateTime.Now).Count();
//average
var averageProjectDuration = ctx.Projects.Where(x => x.End != null).Average(x => EntityFunctions.DiffDays(x.Start, x.End));
//sum
var sumProjectDurationsByCustomer = ctx.Projects.Where(x => x.End != null).Select(x => new { Customer = x.Customer.Name, Days = EntityFunctions.DiffDays(x.Start, x.End) }).GroupBy(x => x.Customer).Select(x => new { Customer = x.Key, Sum = x.Sum(y => y.Days) }).ToList();
//return the resources and project names only
var resourcesXprojects = ctx.Projects.SelectMany(x => x.ProjectResources).Select(x => new { Resource = x.Resource.Name, Project = x.Project.Name }).ToList();
//return the customer names and their project counts
var customersAndProjectCount = ctx.Customers.Select(x => new { x.Name, Count = x.Projects.Count() }).ToList();
//subquery
var usersKnowingATechnology = (from r in ctx.Resources where r.Technologies.Any(x => (ctx.Technologies.Where(t => t.Name == "ASP.NET")).Contains(x)) select r).ToList();
var usersKnowingATechnology2 = (from r in ctx.Resources where r.Technologies.Any(x => (from t in ctx.Technologies where t.Name == "ASP.NET" select t).Contains(x)) select r).ToList();
//contains
var customersToFind = new String[] { "Some Customer", "Another Customer" };
var projectsOfCustomers = ctx.Projects.Where(x => customersToFind.Contains(x.Customer.Name)).ToList();
//spatial
var location = DbGeography.FromText("POINT(41 8)");
var area = DbGeography.MultiPointFromText("MULTIPOINT(53.095124 -0.864716, 53.021255 -1.337128, 52.808019 -1.345367, 52.86153 -1.018524)", 4326);
/*var pointInsideArea = ctx.Venues.Where(x => area.Intersects(x.Location)).ToList();
*
* var venuesAndDistanceToLocation = (from v in ctx.Venues
* orderby v.Location.Distance(location)
* select new { Venue = v, Distance = v.Location.Distance(location) }).ToList();*/
//Entity-SQL
ObjectContext octx = (ctx as IObjectContextAdapter).ObjectContext;
//filtering
var usersInProjectWithESQL = octx.CreateQuery <Resource>("SELECT VALUE pr.Resource FROM ProjectResources AS pr WHERE pr.Project.Name = @name", new ObjectParameter("name", "Big Project")).ToList();
//contains
var usersKnowingATechnologyESQL = octx.CreateQuery <Resource>("SELECT VALUE r FROM Resources AS r WHERE EXISTS (SELECT VALUE t FROM Technologies AS t WHERE t.Name = @name AND r IN t.Resources)", new ObjectParameter("name", "ASP.NET")).ToList();
//flatten
var userTechnologiesESQL = octx.CreateQuery <Technology>("FLATTEN(SELECT VALUE r.Technologies FROM Resources AS r)").ToList();
//paging
var pagedResourcesESQL = octx.CreateQuery <Resource>("SELECT VALUE r FROM Resources AS r ORDER BY r.Name SKIP 5 LIMIT(5)").ToList();
//paging with parameters
var pagedResourcesWithParametersESQL = octx.CreateQuery <Resource>("SELECT VALUE r FROM Resources AS r ORDER BY r.Name SKIP @skip LIMIT(@limit)", new ObjectParameter("skip", 5), new ObjectParameter("limit", 5)).ToList();
//top
var lastProjectESQL = octx.CreateQuery <Project>("SELECT VALUE TOP(1) p FROM Projects AS p ORDER BY p.Start DESC").SingleOrDefault();
//between
var projectsStartingInADateIntervalESQL = octx.CreateQuery <Project>("SELECT VALUE p FROM Projects AS P WHERE p.Start BETWEEN @start AND @end", new ObjectParameter("start", DateTime.Today.AddDays(-14)), new ObjectParameter("end", DateTime.Today.AddDays(-7))).ToList();
//in
var projectsStartingInSetOfDatesESQL = octx.CreateQuery <Project>("SELECT VALUE p FROM Projects AS P WHERE p.Start IN MULTISET(DATETIME '2013-12-25 0:0:0', DATETIME '2013-12-31 0:0:0')").ToList();
//projection
var projectNameAndDurationESQL = octx.CreateQuery <Object>("SELECT p.Name, DIFFDAYS(p.Start, p.[End]) FROM Projects AS p WHERE p.[End] IS NOT NULL").ToList();
//count
var numberOfClosedProjectsESQL = octx.CreateQuery <Int32>("SELECT VALUE COUNT(p.ProjectId) FROM Projects AS p WHERE p.[End] IS NOT NULL AND p.[End] < @now", new ObjectParameter("now", DateTime.Now)).Single();
//group
var customersAndProjectCountIndicatorESQL = octx.CreateQuery <Object>("SELECT p.Customer.Name, COUNT(p.Name) FROM Projects AS p GROUP BY p.Customer").ToList();
//case
var customersAndProjectRangeESQL = octx.CreateQuery <Object>("SELECT p.Customer.Name, CASE WHEN COUNT(p.Name) > 10 THEN 'Lots' ELSE 'Few' END AS Amount FROM Projects AS p GROUP BY p.Customer").ToList();
if (customersAndProjectRangeESQL.Any() == true)
{
var r = customersAndProjectRangeESQL.OfType <IExtendedDataRecord>().First();
var nameIndex = r.GetOrdinal("Name");
var name = r.GetString(nameIndex);
}
//max number of days
var maxDurationESQL = octx.CreateQuery <Int32?>("SELECT VALUE MAX(DIFFDAYS(p.Start, p.[End])) FROM Projects AS p WHERE p.[End] IS NOT NULL").SingleOrDefault();
//string contains
var technologiesContainingNetESQL = octx.CreateQuery <String>("SELECT VALUE t.Name FROM Technologies AS T WHERE CONTAINS(t.Name, '.NET')").ToList();
//SQL
var projectFromSQL = ctx.Projects.SqlQuery("SELECT * FROM Project WHERE Name = @p0", "Big Project").ToList();
//stored procedure
var projectFromProcedure = ctx.Projects.SqlQuery("SELECT * FROM dbo.GetProjectById(@p0)", 1).SingleOrDefault();
var result = ctx.Database.ExecuteSqlCommand("UPDATE Project SET [End] = null WHERE ProjectId = {0}", 100);
//current date and time
var now = ctx.Database.SqlQuery <DateTime>("SELECT GETDATE()").Single();
var model = ctx.Database.SqlQuery(typeof(Byte[]), "SELECT Model FROM __MigrationHistory").OfType <Object>().Single();
//call function
var serverTimestamp = ctx.ExecuteScalar <DateTime>("SELECT GETDATE()");
//update records
var updateCount = ctx.ExecuteNonQuery("UPDATE ProjectDetail SET Budget = Budget * 1.1 WHERE ProjectId = {0}", 1);
//extensions
var projectsBetweenTodayAndBeforeToday = ctx.Projects.Between(x => x.Start, DateTime.Today.AddDays(-1), DateTime.Today).ToList();
//projects with 10 to 20 resources
var projectsWithTwoOrThreeResources = ctx.Projects.Select(x => new { x.Name, ResourceCount = x.ProjectResources.Count() }).Between(x => x.ResourceCount, 10, 20).ToList();
//extension method
var soundex = ctx.Projects.Select(x => x.Name.Soundex()).ToList();
//first level cache
var user = ctx.Resources.Local.SingleOrDefault(x => x.Technologies.Any(y => y.Name == "ASP.NET"));
//no caching
var technologies = ctx.Technologies.AsNoTracking().ToList();
}
}
/// <summary>
/// Method to Get the Beat Details info on the basis of status id
/// </summary>
/// <param name="statusID">represent 0 if rejected, 1 if approved</param>
/// <returns></returns>
public IList <CoveragePlan> GetBeatInfoDetails(int statusID)
{
return(SmartDostDbContext.CoveragePlans.Where(c => (EntityFunctions.TruncateTime(c.CoverageDate) >= EntityFunctions.TruncateTime(DateTime.Now) && EntityFunctions.TruncateTime(c.CoverageDate) <= EntityFunctions.AddDays(DateTime.Now, 15)) && c.IsCoverage == false && c.StatusID == statusID).ToList());
}
public void Run()
{
using (var context = new Context(SetUpCodeFirst.Connection))
{
TableWithSeveralFieldsType table = new TableWithSeveralFieldsType()
{
MyInt = 10,
MyString = " My current string with leading and trailing spaces ",
MyDateTime = new DateTime(1969, 09, 15, 20, 03, 19)
};
context.TableWithSeveralFieldsTypes.Add(table);
context.SaveChanges();
Console.WriteLine(context.TableWithSeveralFieldsTypes.Select(c => new { c.MyDateTime.Day }).First().Day);
#pragma warning disable 618
Console.WriteLine(context.TableWithSeveralFieldsTypes.Select(c => new { Date = EntityFunctions.AddDays(c.MyDateTime, 4) }).First());
Console.WriteLine(context.TableWithSeveralFieldsTypes.Select(c => new { ElapsedDays = EntityFunctions.DiffDays(c.MyDateTime, c.MyDateTime) }).First().ElapsedDays.Value);
#pragma warning restore 618
// ReSharper disable StringIndexOfIsCultureSpecific.1
Console.WriteLine(context.TableWithSeveralFieldsTypes.Select(c => c.MyString.IndexOf(CanonicalFunctionsTest1.MyStringValue.Substring(5, 4))).First());
// ReSharper restore StringIndexOfIsCultureSpecific.1
Console.WriteLine(context.TableWithSeveralFieldsTypes.Where(c => c.MyString.Contains(CanonicalFunctionsTest1.MyStringValue.Substring(3, 5))).First());
Console.WriteLine(context.TableWithSeveralFieldsTypes.Where(c => c.MyString.StartsWith(CanonicalFunctionsTest1.MyStringValue.Substring(3, 5))).FirstOrDefault());
Console.WriteLine(context.TableWithSeveralFieldsTypes.Where(c => c.MyString.StartsWith(CanonicalFunctionsTest1.MyStringValue.Substring(0, 5))).First());
string stringEnd = CanonicalFunctionsTest1.MyStringValue.Substring(CanonicalFunctionsTest1.MyStringValue.Length - 5, 5);
//Console.WriteLine(context.TableWithSeveralFieldsTypes.Where(c => c.MyString.EndsWith(CanonicalFunctionsTest.MYSTRINGVALUE.Substring(CanonicalFunctionsTest.MYSTRINGVALUE.Length - 5, 5))).First());
Console.WriteLine(context.TableWithSeveralFieldsTypes.Where(c => c.MyString.EndsWith(stringEnd)).First());
}
}
public List <Pelicula> listarPeliculasYEstrenos()
{
// obtengo todas las carteleras que esten vigentes actualmente y las que tengan fecha de estreno hasta 30 dias a partir del dia de la fecha.
List <Cartelera> carteleras = ctx.Carteleras.Where(carte => carte.FechaInicio <= EntityFunctions.AddDays(DateTime.Now, 30) && carte.FechaFin >= DateTime.Now).ToList();
// Las meto en un hashset para evitar duplicados
HashSet <Pelicula> peliculas = new HashSet <Pelicula>();
foreach (var carte in carteleras)
{
peliculas.Add(carte.Pelicula);
}
return(peliculas.ToList());
}
public IList <Contas_a_Receber> RecuperaDadosEmAbertoCliente(Cliente cliente)
{
using (testeEntities2 contexto = new testeEntities2())
{
var contasreceber = new Contas_a_Receber();
if (contasreceber.valor_a_receber == 0)
{
var recebe = contexto.Contas_a_Receber.Where(p => p.data_a_receber.Value < EntityFunctions.AddDays(DateTime.Today, -30)).
Select(p => new Contas_a_Receber()
{
id_cliente = p.id_cliente,
data_a_receber = p.data_a_receber,
valor_a_receber = p.valor_a_receber
}).ToList();
return(recebe.ToList());
}
return(null);
}
}
public IEnumerable <Report> GetAllReportsLastWeek()
{
var _context = new ApplicationDbContext();
return(_context.Reports.Where(r => r.Creation > EntityFunctions.AddDays(DateTime.Now, -7)).ToList());
}
public List <Messages> LoadMessages(string FromUserName, string ToUserName, DateTime startDate)
{
List <Messages> msgs = new List <Messages>();
try
{
using (var ctx = new TTContext())
{
IQueryable <Messages> msg = ctx.Messages.Where(c => (c.FromUser == FromUserName && c.ToUser == ToUserName) || (c.FromUser == ToUserName && c.ToUser == FromUserName) && (c.CreateDate <= startDate && c.CreateDate >= EntityFunctions.AddDays(startDate, -1)));
msgs = msg.ToList();
}
}
catch (Exception)
{
throw;
}
return(msgs);
}
// GET: Home
public ActionResult Index()
{
List <Product> SetPrice = new List <Product>();
using (var ctx = new AuctionSiteDBEntities())
{
var listget = ctx.Auctions.ToList();
var listmax = ctx.Products.Where(p => p.Bought == false && (DateTime.Now <= EntityFunctions.AddDays(p.TimePost, p.IntervalTime))).OrderByDescending(p => p.PriceDisplay).ToList().Take(5);
var listend = ctx.Products.Where(p => p.Bought == false && (DateTime.Now <= EntityFunctions.AddDays(p.TimePost, p.IntervalTime)))
.OrderBy(p => (EntityFunctions.DiffSeconds(DateTime.Now, EntityFunctions.AddDays(p.TimePost, p.IntervalTime)))).ToList().Take(5);
var TopSetPriceList = listget.GroupBy(a => a.IDPro).OrderByDescending(a => a.Key).ToList().Take(5);
foreach (var id in TopSetPriceList)
{
Product pro = ctx.Products.Where(p => p.ID == id.Key && p.Bought == false).FirstOrDefault();
if (pro != null)
{
SetPrice.Add(pro);
}
}
HomeVM vm = new HomeVM();
vm.MostSettingPrice = new List <Product>();
vm.MaxPrice = new List <Product>();
vm.EndDate = new List <Product>();
vm.EndDate.AddRange(listend);
vm.MostSettingPrice.AddRange(SetPrice);
vm.MaxPrice.AddRange(listmax);
return(View(vm));
}
}
private void TotalTask_Status()
{
//decimal dCount = db.tbl_pmsTxTask.Where(p => p.assignedUser_ID == sUser_ID && p.status_ID == "1").Count();
decimal dCount = db.tbl_pmsTxTask.Where(p => p.status_ID == "1").Count();
ViewBag.NewStatus_Count = dCount;
DateTime dtNow = DateTime.Now.Date;
//decimal dDeadlineCount = db.tbl_pmsTxTask.Where(p => p.assignedUser_ID == sUser_ID && p.DeadlineDate >= dtNow && p.DeadlineDate <= EntityFunctions.AddDays(dtNow, 7)).Count();
decimal dDeadlineCount = db.tbl_pmsTxTask.Where(p => p.DeadlineDate >= dtNow && p.DeadlineDate <= EntityFunctions.AddDays(dtNow, 7)).Count();
ViewBag.Deadline_Count = dDeadlineCount;
//decimal dDueCount = db.tbl_pmsTxTask.Where(p => p.assignedUser_ID == sUser_ID && DateTime.Now > p.DeadlineDate).Count();
decimal dDueCount = db.tbl_pmsTxTask.Where(p => DateTime.Now > p.DeadlineDate).Count();
ViewBag.Due_Count = dDueCount;
}
private List <LiabilitiesExpenseViewModel> GetExpenseList(DateTime date, string driverC, int liabilitiesNo)
{
//var result = (from o in _containerRepository .GetAllQueryable()
// join d in _dispatchRepository.GetAllQueryable()
// on new { o.OrderD, o.OrderNo, o.DetailNo } equals new { d.OrderD, d.OrderNo, d.DetailNo }
// join e in _expenseDetailRepository.GetAllQueryable()
// on new { d.OrderD, d.OrderNo, d.DetailNo, d.DispatchNo } equals new { e.OrderD, e.OrderNo, e.DetailNo, e.DispatchNo }
// join m in _expenseRepository.GetAllQueryable()
// on e.ExpenseC equals m.ExpenseC
// where (d.DriverC == driverC &&
// d.TransportD < EntityFunctions.AddDays(date,1) &&
// e.PaymentMethodI == Constants.CASH)
// select new LiabilitiesExpenseViewModel()
// {
// ContainerNo = o.ContainerNo,
// TransportD =d.TransportD,
// ContainerSizeN = o.ContainerSizeI == Constants.CONTAINERSIZE1 ? Constants.CONTAINERSIZE1N :
// o.ContainerSizeI == Constants.CONTAINERSIZE2 ? Constants.CONTAINERSIZE2N :
// Constants.CONTAINERSIZE3N,
// ExpenseC = m.ExpenseC,
// ExpenseN =m.ExpenseN,
// Amount = e.Amount
// }
// );
var result = (from e in _expenseDetailRepository.GetAllQueryable()
join d in _dispatchRepository.GetAllQueryable()
on new { e.OrderD, e.OrderNo, e.DetailNo, e.DispatchNo } equals new { d.OrderD, d.OrderNo, d.DetailNo, d.DispatchNo } into ed
from d in ed.DefaultIfEmpty()
join o in _containerRepository.GetAllQueryable()
on new { d.OrderD, d.OrderNo, d.DetailNo } equals new { o.OrderD, o.OrderNo, o.DetailNo } into edo
from o in edo.DefaultIfEmpty()
join or in _orderRepository.GetAllQueryable()
on new { e.OrderD, e.OrderNo } equals new { or.OrderD, or.OrderNo } into edor
from or in edor.DefaultIfEmpty()
join c in _customerRepository.GetAllQueryable()
on new { or.CustomerMainC, or.CustomerSubC } equals new { c.CustomerMainC, c.CustomerSubC } into cedor
from c in cedor.DefaultIfEmpty()
join m in _expenseRepository.GetAllQueryable()
on e.ExpenseC equals m.ExpenseC into mcedor
from m in mcedor.DefaultIfEmpty()
join f in _liabilitiesItemRepository.GetAllQueryable()
on new { e.OrderD, e.OrderNo, e.DetailNo, e.DispatchNo, e.ExpenseNo } equals new { f.OrderD, f.OrderNo, f.DetailNo, f.DispatchNo, f.ExpenseNo } into fmcedor
from f in fmcedor.DefaultIfEmpty()
where (d.DriverC == driverC &&
d.TransportD < EntityFunctions.AddDays(date, 1) &&
e.PaymentMethodI == Constants.CASH) &&
(f == null || (f.LiabilitiesD == date && f.LiabilitiesNo == liabilitiesNo))
select new LiabilitiesExpenseViewModel()
{
OrderD = e.OrderD,
OrderNo = e.OrderNo,
DetailNo = e.DetailNo,
DispatchNo = e.DispatchNo,
ExpenseNo = e.ExpenseNo,
ContainerNo = o.ContainerNo,
TransportD = d.TransportD,
ContainerSizeI = o.ContainerSizeI,
//ContainerSizeN = o.ContainerSizeI == Constants.CONTAINERSIZE1 ? Constants.CONTAINERSIZE1N :
// o.ContainerSizeI == Constants.CONTAINERSIZE2 ? Constants.CONTAINERSIZE2N :
// Constants.CONTAINERSIZE3N,
ExpenseC = m.ExpenseC,
ExpenseN = m.ExpenseN,
//Amount = e.Amount,
Amount = (e.Amount ?? 0) + (e.TaxAmount ?? 0),
TaxAmount = e.TaxAmount,
CustomerN = c != null ? c.CustomerN : "",
LiabilitiesStatusI = f == null ? "0" : f.LiabilitiesStatusI,
LiabilitiesD = f == null ? (DateTime?)null : f.LiabilitiesD,
LiabilitiesNo = f == null ? 0 : f.LiabilitiesNo
}
);
//var previousDate = _liabilitiesRepository.Query(i => i.DriverC == driverC && i.LiabilitiesI == "1" && i.LiabilitiesD < date).OrderBy("LiabilitiesD desc").FirstOrDefault();
//if (previousDate != null)
//{
// return result.AsQueryable().Where(i => i.TransportD >= previousDate.LiabilitiesD).ToList();
//}
//else
//{
return(result.ToList());
//}
}
public NotificationResultViewModel SendUpcomingBoxNotifications()
{
try
{
int notificationsSent = 0;
Stopwatch sw = new Stopwatch();
sw.Start();
using (ManBoxEntities ent = new ManBoxEntities())
{
var upcomingDeliveries = from sd in ent.SubscriptionDeliveries
where sd.DeliveryStateCV == CodeValues.DeliveryState.Pending &&
sd.Subscription.GiftId == null &&
sd.Subscription.SubscriptionStateCV == CodeValues.SubscriptionState.Subscribed &&
EntityFunctions.AddDays(sd.DeliveryDate.Value, -daysBefore).Value < DateTime.Now &&
!sd.SubscriptionDeliveryMessages.Any(m => m.DeliveryMessageTypeCV == CodeValues.DeliveryMessageType.Upcoming)
select new
{
SubscriptionDelivery = sd,
Name = sd.Subscription.User.FirstName,
Token = sd.Subscription.Token,
Email = sd.Subscription.User.Email,
LangIso = sd.Subscription.User.Language.IsoCode,
CountryIso = sd.Subscription.User.Country.IsoCode,
Address = sd.Subscription.Address,
Products = from m in sd.SubscriptionDeliveryModels
select new
{
ProductName = (from tt in ent.TranslationTexts where tt.TranslationId == m.Model.Product.TitleTrId && tt.LanguageId == sd.Subscription.User.LanguageId select tt.Text).FirstOrDefault(),
ModelName = m.Model.Name,
Quantity = m.Quantity,
Price = m.Model.ShopPrice,
TotalPrice = m.Model.ShopPrice * m.Quantity
}
};
var notificationMails = new List <UpcomingBoxNotificationMail>();
foreach (var del in upcomingDeliveries)
{
var itemsTotal = Utilities.GetDeliveryTotal(del.SubscriptionDelivery);
var shippingFee = Utilities.CalculateShippingFee(itemsTotal);
var couponAmount = Utilities.CalculateCouponAmount(itemsTotal, del.SubscriptionDelivery);
var total = itemsTotal + shippingFee - couponAmount;
var couponLabel = Utilities.GetCouponLabel(del.SubscriptionDelivery.Coupon);
var products = new List <MailProduct>();
var notificationMail = new UpcomingBoxNotificationMail()
{
Name = del.Name,
Email = del.Email,
Token = del.Token,
LanguageIso = del.LangIso,
CountryIso = del.CountryIso,
Address = new MailAddress()
{
City = del.Address.City,
Street = del.Address.Street,
Province = del.Address.Province,
PostalCode = del.Address.PostalCode
},
SubTotal = itemsTotal,
Total = total,
ShippingFee = shippingFee,
CouponAmount = couponAmount,
CouponLabel = couponLabel
};
foreach (var prod in del.Products)
{
products.Add(new MailProduct()
{
ModelName = prod.ModelName,
ProductName = prod.ProductName,
Price = prod.Price,
Quantity = prod.Quantity,
TotalPrice = prod.TotalPrice
});
}
notificationMail.Products = products;
SendMail(notificationMail);
ShipmentRepository.StoreDeliveryMessage(del.SubscriptionDelivery, CodeValues.DeliveryMessageType.Upcoming);
notificationsSent++;
}
sw.Stop();
ent.SaveChanges();
}
return(new NotificationResultViewModel()
{
NotificationsSent = notificationsSent,
EndedDateTime = DateTime.Now,
ElapsedTime = sw.Elapsed,
MessageType = CodeValues.DeliveryMessageType.Upcoming
});
}
catch (Exception e)
{
logger.Log(e);
throw;
}
}
/// <summary>
/// Gets all pending delete exported files after a defined period.
/// </summary>
/// <returns>pending delete file requests.</returns>
public IEnumerable <Data.ExportFile> GetGeneratedOldExportFiles()
{
//check if a record already exisit
int completedStatusCodeId = Utils.GetCodeIdByCodeValue("ExportFileStatus", "COMPLETED");
int exportedFilesRemainingDays = int.Parse(Utils.GetSystemValue("ExportedFilesRemainingDays"));
//Get completed records that are older than the defined days in "ExportedFilesRemainingDays" as a systemvalue
return(DataContext.ExportFiles.Where(ef => ef.ExportFileStatusCodeId == completedStatusCodeId &&
ef.IsActive && EntityFunctions.DiffDays(EntityFunctions.AddDays(ef.CreatedDate, exportedFilesRemainingDays), Utils.Now) >= 0));
}