public void CreateDateTime()
{
#if !EFOLD
var q = this.Entities
.Select(x => DbFunctions.CreateDateTime(x.DateTime.Year, 5, 1, 0, 0, 0));
#else
var q = this.Entities
.Select(x => EntityFunctions.CreateDateTime(x.DateTime.Year, 5, 1, 0, 0, 0));
#endif
var q2 = q.AsEnumerable()
.Where(x => x.Value.Month == 5);
q2.Should().NotBeEmpty();
}
public TargetCalcsMgr LoadCurrentMetrics(bool periodYearOnly, bool companyOverallOnly, DateIntervalType dateIntervalType)
{
this.DateInterval = dateIntervalType;
try
{
decimal[] targetArray = this.TargetList.Select(l => l.TARGET_ID).ToArray();
List <PERSPECTIVE_TARGET_CALC> tcList;
if (dateIntervalType == DateIntervalType.month)
{
tcList = (from c in this.Entities.PERSPECTIVE_TARGET_CALC
where (targetArray.Contains(c.TARGET_ID) && c.PERIOD_YEAR == this.ToDate.Year && c.PERIOD_MONTH == this.ToDate.Month)
select c).OrderByDescending(l => l.TARGET_ID).ToList();
}
else if (dateIntervalType == DateIntervalType.year)
{
tcList = (from c in this.Entities.PERSPECTIVE_TARGET_CALC
where (targetArray.Contains(c.TARGET_ID) && EntityFunctions.CreateDateTime(c.PERIOD_YEAR, c.PERIOD_MONTH, 1, 0, 0, 0) >= this.FromDate && EntityFunctions.CreateDateTime(c.PERIOD_YEAR, c.PERIOD_MONTH, 1, 0, 0, 0) <= this.ToDate)
select c).OrderByDescending(c => EntityFunctions.CreateDateTime(c.PERIOD_YEAR, c.PERIOD_MONTH, 1, 0, 0, 0)).ThenBy(l => l.TARGET_ID).ToList();
}
else
{
tcList = (from c in this.Entities.PERSPECTIVE_TARGET_CALC
where (targetArray.Contains(c.TARGET_ID))
select c).OrderByDescending(c => EntityFunctions.CreateDateTime(c.PERIOD_YEAR, c.PERIOD_MONTH, 1, 0, 0, 0)).ThenBy(l => l.TARGET_ID).ToList();
}
if (tcList != null)
{
foreach (PERSPECTIVE_TARGET_CALC tc in tcList)
{
this.TargetList.Where(t => t.TARGET_ID == tc.TARGET_ID).FirstOrDefault().PERSPECTIVE_TARGET_CALC.Add(tc);
}
}
}
catch (Exception e)
{
// SQMLogger.LogException(e);
}
return(this);
}
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();
}
}
static string ProcessIncidents()
{
PSsqmEntities entities = new PSsqmEntities();
SETTINGS setting = null;
int workdays = 7;
int rollupMonthsAhead = 0;
string nextStep = "";
DateTime fromDate = DateTime.UtcNow.AddMonths(-11); // set the incident 'select from' date. TODO: get this from SETTINGS table
// set end date to end of current month to clear spurrious entries ?
DateTime rollupToDate = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.DaysInMonth(DateTime.UtcNow.Year, DateTime.UtcNow.Month));
WriteLine("INCIDENT Rollup Started: " + DateTime.UtcNow.ToString("hh:mm MM/dd/yyyy"));
List <SETTINGS> sets = SQMSettings.SelectSettingsGroup("AUTOMATE", ""); // ABW 20140805
try
{
setting = sets.Where(x => x.SETTING_CD == "ROLLUP_WORKDAYS").FirstOrDefault();
if (setting != null && !string.IsNullOrEmpty(setting.VALUE))
{
if (!int.TryParse(setting.VALUE, out workdays))
{
workdays = 7;
}
}
setting = sets.Where(x => x.SETTING_CD == "ROLLUP_NEXTPAGE").FirstOrDefault();
if (setting != null && !string.IsNullOrEmpty(setting.VALUE) && setting.VALUE.Length > 1)
{
nextStep = setting.VALUE;
}
// fetch all incidents occurring after the minimum reporting date
List <INCIDENT> incidentList = (from i in entities.INCIDENT.Include("INCFORM_INJURYILLNESS")
where
i.ISSUE_TYPE_ID != (decimal)EHSIncidentTypeId.PreventativeAction &&
i.INCIDENT_DT >= fromDate && i.DETECT_PLANT_ID > 0
select i).OrderBy(l => l.DETECT_PLANT_ID).ThenBy(l => l.INCIDENT_DT).ToList();
List <PLANT> plantList = SQMModelMgr.SelectPlantList(entities, 1, 0);
PLANT plant = null;
// fetch all the plant accounting records for the target timespan
DateTime minDate = incidentList.Select(l => l.INCIDENT_DT).Min();
minDate = minDate.AddMonths(-1);
PLANT_ACCOUNTING pa = null;
List <PLANT_ACCOUNTING> paList = (from a in entities.PLANT_ACCOUNTING
where
EntityFunctions.CreateDateTime(a.PERIOD_YEAR, a.PERIOD_MONTH, 1, 0, 0, 0) >= minDate && EntityFunctions.CreateDateTime(a.PERIOD_YEAR, a.PERIOD_MONTH, 1, 0, 0, 0) <= rollupToDate
select a).OrderBy(l => l.PLANT_ID).ThenBy(l => l.PERIOD_YEAR).ThenBy(l => l.PERIOD_MONTH).ToList();
List <EHSIncidentTimeAccounting> summaryList = new List <EHSIncidentTimeAccounting>();
foreach (INCIDENT incident in incidentList)
{
WriteLine("Incident ID: " + incident.INCIDENT_ID.ToString() + " Occur Date: " + Convert.ToDateTime(incident.INCIDENT_DT).ToShortDateString());
incident.INCFORM_CAUSATION.Load();
if (incident.ISSUE_TYPE_ID == (decimal)EHSIncidentTypeId.InjuryIllness)
{
incident.INCFORM_LOSTTIME_HIST.Load();
}
plant = plantList.Where(l => l.PLANT_ID == (decimal)incident.DETECT_PLANT_ID).FirstOrDefault();
summaryList = EHSIncidentMgr.SummarizeIncidentAccounting(summaryList, EHSIncidentMgr.CalculateIncidentAccounting(incident, plant.LOCAL_TIMEZONE, workdays));
}
plant = null;
PLANT_ACTIVE pact = null;
DateTime periodDate;
foreach (PLANT_ACCOUNTING pah in paList.OrderBy(l => l.PLANT_ID).ToList())
{
if (pact == null || pact.PLANT_ID != pah.PLANT_ID)
{
pact = (from a in entities.PLANT_ACTIVE where a.PLANT_ID == pah.PLANT_ID && a.RECORD_TYPE == (int)TaskRecordType.HealthSafetyIncident select a).SingleOrDefault();
}
//if (pact != null && pact.EFF_END_DATE.HasValue && new DateTime(pah.PERIOD_YEAR, pah.PERIOD_MONTH, 1).Date >= ((DateTime)pact.EFF_START_DATE).Date)
if (pact != null && pact.EFF_START_DATE.HasValue && new DateTime(pah.PERIOD_YEAR, pah.PERIOD_MONTH, 1).Date >= ((DateTime)pact.EFF_START_DATE).Date)
{
pah.TIME_LOST = pah.TOTAL_DAYS_RESTRICTED = 0;
pah.TIME_LOST_CASES = pah.RECORDED_CASES = pah.FIRST_AID_CASES = 0;
}
}
plant = null;
pact = null;
foreach (EHSIncidentTimeAccounting period in summaryList.OrderBy(l => l.PlantID).ThenBy(l => l.PeriodYear).ThenBy(l => l.PeriodMonth).ToList())
{
if (plant == null || plant.PLANT_ID != period.PlantID)
{
plant = plantList.Where(l => l.PLANT_ID == period.PlantID).FirstOrDefault();
pact = (from a in entities.PLANT_ACTIVE where a.PLANT_ID == plant.PLANT_ID && a.RECORD_TYPE == (int)TaskRecordType.HealthSafetyIncident select a).SingleOrDefault();
}
periodDate = new DateTime(period.PeriodYear, period.PeriodMonth, 1);
if (pact != null && pact.EFF_START_DATE.HasValue && periodDate >= pact.EFF_START_DATE)
{
// write PLANT_ACCOUNTING metrics
if ((pa = paList.Where(l => l.PLANT_ID == period.PlantID && l.PERIOD_YEAR == period.PeriodYear && l.PERIOD_MONTH == period.PeriodMonth).FirstOrDefault()) == null)
{
paList.Add((pa = new PLANT_ACCOUNTING()));
pa.PLANT_ID = period.PlantID;
pa.PERIOD_YEAR = period.PeriodYear;
pa.PERIOD_MONTH = period.PeriodMonth;
}
pa.TIME_LOST = period.LostTime;
pa.TOTAL_DAYS_RESTRICTED = period.RestrictedTime;
pa.TIME_LOST_CASES = period.LostTimeCase;
pa.RECORDED_CASES = period.RecordableCase;
pa.FIRST_AID_CASES = period.FirstAidCase;
pa.LAST_UPD_DT = DateTime.UtcNow;
pa.LAST_UPD_BY = "automated";
EHSModel.UpdatePlantAccounting(entities, pa);
}
}
WriteLine("INCIDENT Rollup Completed: " + DateTime.UtcNow.ToString("hh:mm MM/dd/yyyy"));
}
catch (Exception ex)
{
WriteLine("INCIDENT RollUp Error: " + ex.ToString());
}
return(nextStep);
}
private ApiResult DealLink(dynamic json)
{
ApiResult res = new ApiResult();
try
{
BLL.plans bllPlans = new BLL.plans();
rootDto <Model.Api.message> dto = JsonConvert.DeserializeObject <rootDto <Model.Api.message> >(json.ToString());
DateTime dtNow = DateTime.Now;
DateTime dtToDay = DateTime.Parse(dtNow.ToString("yyyy-MM-dd"));
string strMsg = dto.Data.msg;
string[] strArr = strMsg.Split(new string[] { "http" }, StringSplitOptions.None);
List <string> aLink = new List <string>();
for (int i = 0; i < strArr.Length; i++)
{
if (i == 0 && string.IsNullOrEmpty(strArr[0]))
{
continue;
}
string strItem = strArr[i];
int index = PageFunc.getIndex(strItem);
string strLink = string.Format("http{0}", strItem.Substring(0, index));
if (strLink.IndexOf("uland.taobao.com") != -1)
{
}
else if (strLink.IndexOf("detail.tmall.com") != -1)
{
string itemId = PageFunc.GetQueryString("id", strLink);
string strContent = HttpHelper.HttpGet("http://g5.vipdamai.net/hcapi.ashx?gid=" + itemId);
hcRoot hc = JsonConvert.DeserializeObject <hcRoot>(strContent);
if (hc.error == "0")
{
if (hc.data != null)
{
int total = 0;
var list = bllPlans.GetList(1, int.MaxValue, ref total, p => p.userNumberId == hc.data.seller_id && EntityFunctions.CreateDateTime(p.zctime.Value.Year, p.zctime.Value.Month, p.zctime.Value.Day, 0, 0, 0) == dtToDay, p => p.id);
if (total > 0)
{
foreach (var item in list)
{
item.item_id = hc.data.num_iid;
item.goodsname = hc.data.title;
item.shopname = hc.data.shop_title;
if (!string.IsNullOrEmpty(hc.data.coupon_info))
{
string couponPrice = hc.data.coupon_info.Substring(hc.data.coupon_info.IndexOf("减") + 1).Replace("元", "");
item.coupon_price = couponPrice;
item.PayMoney = decimal.Parse(hc.data.zk_final_price) - decimal.Parse(couponPrice);
}
else
{
item.PayMoney = decimal.Parse(hc.data.zk_final_price);
}
bllPlans.Update(item);
}
}
else
{
var item = new Model.plans();
item.item_id = itemId;
item.goodsname = hc.data.title;
item.shopname = hc.data.shop_title;
item.pic = hc.data.pict_url;
item.zctime = dtNow;
//item.coupon_url = hc.data.coupon_click_url;
item.userNumberId = hc.data.seller_id;
item.ifok = "待补充";
bllPlans.Add(item);
}
}
}
}
else if (strLink.IndexOf("pub.alimama.com") != -1)
{
string userNumberId = PageFunc.GetQueryString("userNumberId", strLink);
var model = bllPlans.GetModel(p => p.userNumberId == userNumberId && EntityFunctions.CreateDateTime(p.zctime.Value.Year, p.zctime.Value.Month, p.zctime.Value.Day, 0, 0, 0) == dtToDay);
if (model != null)
{
model.planname = "默认计划名";
model.planlink = strLink;
model.ifok = "正常";
bllPlans.Update(model);
}
else
{
model = new Model.plans();
model.planname = "默认计划名";
model.userNumberId = userNumberId;
model.planlink = strLink;
model.zctime = dtNow;
model.ifok = "正常";
bllPlans.Add(model);
}
}
}
res.message = "";
res.success = true;
}
catch (Exception e)
{
res.success = false;
res.message = "请求失败" + e.Message;
LogHelper.Error(res.message, e);
}
return(res);
}
private static dynamic ObtenerResultadosAnualesParaGanar(Celula celula)
{
dynamic rtn = new ExpandoObject();
ManejadorDeCelulas manejadorDeCelulas = new ManejadorDeCelulas();
List <int> red = new List <int>()
{
celula.CelulaId
};
red.AddRange(manejadorDeCelulas.ObtenerRedInferior(celula.CelulaId));
rtn.id = celula.CelulaId;
rtn.nombre = celula.Descripcion;
rtn.resultadoAnual = new List <dynamic>();
DateTime fechaInicial = DateTime.Now.FirstMondayOfYear();
DateTime fechaFinal = fechaInicial.AddYears(1).AddDays(-1);
List <Celula> totalCelulas = (from o in SesionActual.Instance.getContexto <IglesiaEntities>().Celula
where
red.Contains(o.CelulaId) &&
o.Creacion <= fechaFinal &&
o.Borrado == false
select o).ToList();
List <CelulaMiembroAsistencia> totalAsistencias = (from o in SesionActual.Instance.getContexto <IglesiaEntities>().CelulaMiembroAsistencia
where
red.Contains(o.CelulaId) &&
EntityFunctions.CreateDateTime(o.Anio, o.Mes, o.Dia, 0, 0, 0) > fechaInicial &&
EntityFunctions.CreateDateTime(o.Anio, o.Mes, o.Dia, 0, 0, 0) <= fechaFinal
select o).ToList();
List <CelulaInvitadosAsistencia> totalInvitados = (from o in SesionActual.Instance.getContexto <IglesiaEntities>().CelulaInvitadosAsistencia
where
red.Contains(o.CelulaId) &&
EntityFunctions.CreateDateTime(o.Anio, o.Mes, o.Dia, 0, 0, 0) > fechaInicial &&
EntityFunctions.CreateDateTime(o.Anio, o.Mes, o.Dia, 0, 0, 0) <= fechaFinal
select o).ToList();
DateTime fecha = fechaInicial;
while (fecha < DateTime.Now)
{
dynamic resultadoSemanal = new ExpandoObject();
DateTime fechaSiguiente = fecha.Date.AddDays(7);
resultadoSemanal.semana = fecha.GetWeekNumber();
resultadoSemanal.activas = (from o in totalCelulas
where
o.Creacion <= fechaSiguiente
select o).Count();
resultadoSemanal.realizadas = (from o in totalAsistencias
where
new DateTime(o.Anio, o.Mes, o.Dia, 0, 0, 0) > fecha &&
new DateTime(o.Anio, o.Mes, o.Dia, 0, 0, 0) <= fechaSiguiente
select o.CelulaId).Distinct().Count();
resultadoSemanal.invitados = (from o in totalInvitados
where
new DateTime(o.Anio, o.Mes, o.Dia, 0, 0, 0) > fecha &&
new DateTime(o.Anio, o.Mes, o.Dia, 0, 0, 0) <= fechaSiguiente
select(int?) o.NumeroDeInvitados).Sum() ?? 0;
resultadoSemanal.asistencia = (from o in totalAsistencias
where
new DateTime(o.Anio, o.Mes, o.Dia, 0, 0, 0) > fecha &&
new DateTime(o.Anio, o.Mes, o.Dia, 0, 0, 0) <= fechaSiguiente
select o).Count();
// Agregamos el resultado semanal al resultado anual
rtn.resultadoAnual.Add(resultadoSemanal);
// Una semana mas
fecha = fecha.AddDays(7);
}
return(rtn);
}
protected void Page_Load(object sender, EventArgs e)
{
string pageMode = "";
if (!string.IsNullOrEmpty(Request.QueryString["m"])) // .../...aspx?p=xxxxx
{
pageMode = Request.QueryString["m"].ToLower(); // page mode (web == running manually from the menu)
}
if (IsPostBack)
{
if (pageMode != "web")
{
System.Web.UI.ScriptManager.RegisterStartupScript(this, GetType(), "closePage", "window.onunload = CloseWindow();", true);
}
return;
}
output = new StringBuilder();
SETTINGS setting = null;
bool validIP = true;
int workdays = 7;
string pageURI = HttpContext.Current.Request.Url.AbsoluteUri;
string nextPage = "";
PLANT_ACCOUNTING pa = null;
fromDate = DateTime.UtcNow.AddMonths(-11); // set the incident 'select from' date. TODO: get this from SETTINGS table
// set end date to end of current month to clear spurrious entries ?
DateTime rollupToDate = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.DaysInMonth(DateTime.UtcNow.Year, DateTime.UtcNow.Month));
WriteLine("Incident Rollup Started: " + DateTime.UtcNow.ToString("hh:mm MM/dd/yyyy"));
WriteLine(pageURI);
try
{
string currentIP = GetIPAddress();
List <SETTINGS> sets = SQMSettings.SelectSettingsGroup("AUTOMATE", ""); // ABW 20140805
string strValidIP = sets.Find(x => x.SETTING_CD == "ValidIP").VALUE.ToString();
setting = sets.Where(x => x.SETTING_CD == "ROLLUP_WORKDAYS").FirstOrDefault();
if (setting != null && !string.IsNullOrEmpty(setting.VALUE))
{
if (!int.TryParse(setting.VALUE, out workdays))
{
workdays = 7;
}
}
setting = sets.Where(x => x.SETTING_CD == "ROLLUP_NEXTPAGE").FirstOrDefault();
if (setting != null && !string.IsNullOrEmpty(setting.VALUE) && setting.VALUE.Length > 1)
{
nextPage = setting.VALUE;
}
/*
* int rollupMonthsAhead = 0;
* setting = sets.Where(x => x.SETTING_CD == "ROLLUP_MONTHS_AHEAD").FirstOrDefault();
* if (setting != null && !string.IsNullOrEmpty(setting.VALUE))
* {
* int.TryParse(setting.VALUE, out rollupMonthsAhead);
* rollupToDate = rollupToDate.AddMonths(rollupMonthsAhead);
* }
*/
/*
* if (strValidIP.Equals(currentIP))
* {
* WriteLine("Main Incident RollUp being accessed from a valid IP address " + currentIP);
* validIP = true;
*
* if (Request.QueryString["validation"] != null)
* {
* if (Request.QueryString["validation"].ToString().Equals("Vb12M11a4"))
* validIP = true;
* }
* else
* {
* WriteLine("Main Incident RollUp requested from incorrect source.");
* validIP = false;
* }
* }
* else
* {
* WriteLine("Main Incident RollUp being accessed from invalid IP address " + currentIP);
* validIP = false;
* }
*/
}
catch (Exception ex)
{
validIP = false;
WriteLine("Main Incident RollUp Error validating IP Address: " + ex.ToString());
}
// make sure this code is NOT moved to production
//validIP = true;
if (!validIP)
{
WriteLine("Main Incident RollUp Invalid IP Address");
ltrStatus.Text = output.ToString().Replace("\n", "<br/>");
WriteLogFile();
if (pageMode != "web")
{
System.Web.UI.ScriptManager.RegisterStartupScript(this, GetType(), "closePage", "window.onunload = CloseWindow();", true);
}
return;
}
try
{
PSsqmEntities entities = new PSsqmEntities();
// fetch all incidents occurring after the minimum reporting date
List <INCIDENT> incidentList = (from i in entities.INCIDENT.Include("INCFORM_INJURYILLNESS")
where
i.ISSUE_TYPE_ID != (decimal)EHSIncidentTypeId.PreventativeAction &&
i.INCIDENT_DT >= fromDate && i.DETECT_PLANT_ID > 0
select i).OrderBy(l => l.DETECT_PLANT_ID).ThenBy(l => l.INCIDENT_DT).ToList();
List <PLANT> plantList = SQMModelMgr.SelectPlantList(entities, 1, 0);
PLANT plant = null;
// fetch all the plant accounting records for the target timespan
DateTime minDate = incidentList.Select(l => l.INCIDENT_DT).Min();
minDate = minDate.AddMonths(-1);
List <PLANT_ACCOUNTING> paList = (from a in entities.PLANT_ACCOUNTING
where
EntityFunctions.CreateDateTime(a.PERIOD_YEAR, a.PERIOD_MONTH, 1, 0, 0, 0) >= minDate && EntityFunctions.CreateDateTime(a.PERIOD_YEAR, a.PERIOD_MONTH, 1, 0, 0, 0) <= rollupToDate
select a).OrderBy(l => l.PLANT_ID).ThenBy(l => l.PERIOD_YEAR).ThenBy(l => l.PERIOD_MONTH).ToList();
List <EHSIncidentTimeAccounting> summaryList = new List <EHSIncidentTimeAccounting>();
foreach (INCIDENT incident in incidentList)
{
WriteLine("Incident ID: " + incident.INCIDENT_ID.ToString() + " Occur Date: " + Convert.ToDateTime(incident.INCIDENT_DT).ToShortDateString());
incident.INCFORM_CAUSATION.Load();
if (incident.ISSUE_TYPE_ID == (decimal)EHSIncidentTypeId.InjuryIllness)
{
incident.INCFORM_LOSTTIME_HIST.Load();
}
plant = plantList.Where(l => l.PLANT_ID == (decimal)incident.DETECT_PLANT_ID).FirstOrDefault();
summaryList = EHSIncidentMgr.SummarizeIncidentAccounting(summaryList, EHSIncidentMgr.CalculateIncidentAccounting(incident, plant.LOCAL_TIMEZONE, workdays));
}
plant = null;
PLANT_ACTIVE pact = null;
DateTime periodDate;
foreach (PLANT_ACCOUNTING pah in paList.OrderBy(l => l.PLANT_ID).ToList())
{
if (pact == null || pact.PLANT_ID != pah.PLANT_ID)
{
pact = (from a in entities.PLANT_ACTIVE where a.PLANT_ID == pah.PLANT_ID && a.RECORD_TYPE == (int)TaskRecordType.HealthSafetyIncident select a).SingleOrDefault();
}
//if (pact != null && pact.EFF_END_DATE.HasValue && new DateTime(pah.PERIOD_YEAR, pah.PERIOD_MONTH, 1).Date >= ((DateTime)pact.EFF_START_DATE).Date)
if (pact != null && pact.EFF_START_DATE.HasValue && new DateTime(pah.PERIOD_YEAR, pah.PERIOD_MONTH, 1).Date >= ((DateTime)pact.EFF_START_DATE).Date)
{
pah.TIME_LOST = pah.TOTAL_DAYS_RESTRICTED = 0;
pah.TIME_LOST_CASES = pah.RECORDED_CASES = pah.FIRST_AID_CASES = 0;
}
}
plant = null;
pact = null;
foreach (EHSIncidentTimeAccounting period in summaryList.OrderBy(l => l.PlantID).ThenBy(l => l.PeriodYear).ThenBy(l => l.PeriodMonth).ToList())
{
if (plant == null || plant.PLANT_ID != period.PlantID)
{
plant = plantList.Where(l => l.PLANT_ID == period.PlantID).FirstOrDefault();
pact = (from a in entities.PLANT_ACTIVE where a.PLANT_ID == plant.PLANT_ID && a.RECORD_TYPE == (int)TaskRecordType.HealthSafetyIncident select a).SingleOrDefault();
}
periodDate = new DateTime(period.PeriodYear, period.PeriodMonth, 1);
if (pact != null && pact.EFF_START_DATE.HasValue && periodDate >= pact.EFF_START_DATE)
{
// write PLANT_ACCOUNTING metrics
if ((pa = paList.Where(l => l.PLANT_ID == period.PlantID && l.PERIOD_YEAR == period.PeriodYear && l.PERIOD_MONTH == period.PeriodMonth).FirstOrDefault()) == null)
{
paList.Add((pa = new PLANT_ACCOUNTING()));
pa.PLANT_ID = period.PlantID;
pa.PERIOD_YEAR = period.PeriodYear;
pa.PERIOD_MONTH = period.PeriodMonth;
}
pa.TIME_LOST = period.LostTime;
pa.TOTAL_DAYS_RESTRICTED = period.RestrictedTime;
pa.TIME_LOST_CASES = period.LostTimeCase;
pa.RECORDED_CASES = period.RecordableCase;
pa.FIRST_AID_CASES = period.FirstAidCase;
pa.LAST_UPD_DT = DateTime.UtcNow;
pa.LAST_UPD_BY = "automated";
EHSModel.UpdatePlantAccounting(entities, pa);
}
}
}
catch (Exception ex)
{
WriteLine("Main Incident RollUp Error - " + ex.ToString());
}
WriteLine("");
WriteLine("Completed: " + DateTime.UtcNow.ToString("hh:mm MM/dd/yyyy"));
ltrStatus.Text = output.ToString().Replace("\n", "<br/>");
WriteLogFile();
try
{
if (!string.IsNullOrEmpty(nextPage))
{
int s1 = pageURI.LastIndexOf('/');
int s2 = pageURI.LastIndexOf('.') > -1 ? pageURI.LastIndexOf('.') : pageURI.Length;
string nextPageURI = pageURI.Substring(0, s1 + 1) + nextPage + pageURI.Substring(s2, pageURI.Length - s2);
Response.Redirect(nextPageURI);
}
}
catch (Exception ex)
{
output = new StringBuilder();
WriteLine("RollUp Redirect Error - " + ex.ToString());
WriteLogFile();
}
if (pageMode != "web")
{
System.Web.UI.ScriptManager.RegisterStartupScript(this, GetType(), "closePage", "window.onunload = CloseWindow();", true);
}
}
public override void Execute(IJobExecutionContext context)
{
// Check today's time and find all users whose trial expiry is today
// Look up all users whose trial expires today
List <IUser> usersWithExpiredTrial = (from user in _database.GetUsers
where !user.SubscriptionId.HasValue &&
EntityFunctions.DiffDays(user.TrialExpiryDate, EntityFunctions.CreateDateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day, 0, 0, 0)) == 0 &&
!user.SentTrialExpiryEmail
select user).ToList();
// Generate the email contents
// TODO
// EmailResult trialExpiredEmailContents = _accountEmailFactory.TrialExpired();
// Get the email
IEmail trialExpiredEmail = _emailFactory.CreateEmail(
contents: this.GetEmailContents(),
subject: "Trial Membership Conclusion",
recipients: usersWithExpiredTrial);
// Finally send the email
trialExpiredEmail.Send();
// Mark that we have sent the email to the users
foreach (var user in usersWithExpiredTrial)
{
user.SentTrialExpiryEmail = true;
}
_database.SaveChanges();
// Now let the admins know about the email sent
IEmail adminNotificationEmail = _emailFactory.CreateEmailForAdministrators(
this.GenerateAdminNotificationEmail(usersWithExpiredTrial),
"Users With Expired Trial Report " + DateTime.UtcNow.ToShortDateString());
adminNotificationEmail.Send();
}