/// <summary> /// called on page load /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void linqOrders_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { try { //params for UK ORDERS //return recorda after start exworks date 25/09/2011 equivalent to (CAST('2011-09-25' as datime) in t-sql DateTime _startexworks = wwi_func.vdatetime("2011-09-25"); //return records after starting order number 1000000 for uk orders int _loworder = 0; int _highorder = 1000000; //agetnatoriginid and agentatdestinationid should be the same e.g. 1018 for uk int _agent = 1018; //@filecoverprinted should be 0 int _filecoverprinted = 0; //need this! e.KeyExpression = "OrderID"; //make sure you have: using System.Linq; using System.Linq.Dynamic; using System.Linq.Expressions; or you will get the error //The type arguments for method 'System.Linq.Enumerable.Where<TSource>(System.Collections.Generic.IEnumerable<TSource>, System.Func<TSource,int,bool>)' cannot be inferred from the usage. var _nquery = new linq.linq_new_orders_udfDataContext().new_orders(_startexworks, _loworder, _highorder, _agent, _filecoverprinted); e.QueryableSource = _nquery; } catch (Exception ex) { string _ex = ex.Message.ToString(); this.dxlblErr.Text = _ex; this.dxpnlErr.Visible = true; } }
/// <summary> /// called on page load /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void linqOrders_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { try { //need this! e.KeyExpression = "OrderTemplateID"; //make sure you have: using System.Linq; using System.Linq.Dynamic; using System.Linq.Expressions; or you will get the error //The type arguments for method 'System.Linq.Enumerable.Where<TSource>(System.Collections.Generic.IEnumerable<TSource>, System.Func<TSource,int,bool>)' cannot be inferred from the usage. //if (!string.IsNullOrEmpty(_query)) //{ // // var _nquery = new linq.linq_view_order_templatesDataContext().view_order_templates.Where(_query); // e.QueryableSource = _nquery; //} //else //{ // var _nquery = new linq.linq_view_order_templatesDataContext().view_order_templates.Where(_t => _t.OrderTemplateID == -1); // e.QueryableSource = _nquery; //} } catch (Exception ex) { string _ex = ex.Message.ToString(); this.dxlblErr.Text = _ex; this.dxpnlErr.Visible = true; } }
/// <summary> /// This method fires when the data source for the Behavior Incident DevExpress GridView is selecting /// and it handles the select /// </summary> /// <param name="sender">The efBehaviorIncidentDataSource control</param> /// <param name="e">The LinqServerModeDataSourceSelectEventArgs event</param> protected void efBehaviorIncidentDataSource_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { //Set the key expression e.KeyExpression = "BehaviorIncidentPK"; //Set the source to a LINQ query PyramidContext context = new PyramidContext(); e.QueryableSource = from bi in context.BehaviorIncident.AsNoTracking() .Include(bi => bi.Child) .Include(bi => bi.Classroom) .Include(bi => bi.Classroom.Program) .Include(bi => bi.CodeProblemBehavior) join cp in context.ChildProgram on bi.ChildFK equals cp.ChildFK where currentProgramRole.ProgramFKs.Contains(bi.Classroom.ProgramFK) && cp.ProgramFK == bi.Classroom.ProgramFK && cp.EnrollmentDate <= bi.IncidentDatetime && (cp.DischargeDate.HasValue == false || cp.DischargeDate >= bi.IncidentDatetime) select new { bi.BehaviorIncidentPK, bi.Creator, bi.CreateDate, bi.IncidentDatetime, ChildName = "(" + cp.ProgramSpecificID + ") " + bi.Child.FirstName + " " + bi.Child.LastName, ClassroomName = "(" + bi.Classroom.ProgramSpecificID + ") " + bi.Classroom.Name, ProblemBehavior = bi.CodeProblemBehavior.Description, bi.Classroom.Program.ProgramName }; }
/// <summary> /// This method fires when the data source for the child DevExpress GridView is selecting /// and it handles the select /// </summary> /// <param name="sender">The efChildDataSource control</param> /// <param name="e">The LinqServerModeDataSourceSelectEventArgs event</param> protected void efChildDataSource_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { //Set the key expression e.KeyExpression = "ChildProgramPK"; //Set the source to a LINQ query PyramidContext context = new PyramidContext(); e.QueryableSource = from c in context.Child.Include(c => c.ChildProgram).AsNoTracking() join cp in context.ChildProgram.Include(cp => cp.CodeDischargeReason).Include(cp => cp.Program) on c.ChildPK equals cp.ChildFK where currentProgramRole.ProgramFKs.Contains(cp.ProgramFK) select new { c.ChildPK, Name = c.FirstName + " " + c.LastName, c.BirthDate, cp.ChildProgramPK, cp.ProgramSpecificID, cp.HasIEP, cp.IsDLL, cp.EnrollmentDate, cp.DischargeDate, DischargeReason = cp.CodeDischargeReason.Description, cp.DischargeReasonSpecify, cp.ProgramFK, cp.Program.ProgramName }; }
/// <summary> /// This method fires when the data source for the employee DevExpress GridView is selecting /// and it handles the select /// </summary> /// <param name="sender">The efEmployeeDataSource control</param> /// <param name="e">The LinqServerModeDataSourceSelectEventArgs event</param> protected void efEmployeeDataSource_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { //Set the key expression e.KeyExpression = "ProgramEmployeePK"; //Set the source to a LINQ query PyramidContext context = new PyramidContext(); e.QueryableSource = (from pe in context.ProgramEmployee.AsNoTracking().Include(pe => pe.CodeTermReason).Include(pe => pe.Program) join jf in context.JobFunction.AsNoTracking().Include(jf => jf.CodeJobType) on pe.ProgramEmployeePK equals jf.ProgramEmployeeFK into jobFunctions join t in context.Training.AsNoTracking().Include(t => t.CodeTraining) on pe.ProgramEmployeePK equals t.ProgramEmployeeFK into trainings where currentProgramRole.ProgramFKs.Contains(pe.ProgramFK) select new { pe.ProgramEmployeePK, Name = pe.FirstName + " " + pe.LastName, pe.EmailAddress, JobFunctions = from jf in jobFunctions where jf.EndDate.HasValue == false select jf.CodeJobType.Description, Trainings = from t in trainings where t.TrainingCodeFK == (int)Utilities.TrainingFKs.INTRODUCTION_TO_COACHING || t.TrainingCodeFK == (int)Utilities.TrainingFKs.PRACTICE_BASED_COACHING || t.TrainingCodeFK == (int)Utilities.TrainingFKs.TPITOS_OBSERVER || t.TrainingCodeFK == (int)Utilities.TrainingFKs.TPOT_OBSERVER orderby t.TrainingDate ascending select(t.CodeTraining.Abbreviation + ": " + SqlFunctions.DatePart("month", t.TrainingDate) + "/" + SqlFunctions.DatePart("day", t.TrainingDate) + "/" + SqlFunctions.DatePart("year", t.TrainingDate)), pe.HireDate, pe.TermDate, TermReason = pe.CodeTermReason.Description + " " + (pe.TermReasonSpecify == null ? "" : "(" + pe.TermReasonSpecify + ")"), pe.ProgramFK, pe.Program.ProgramName }); }
protected void EntityServerModeDataSource1_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { CalculatedFieldsTestEntities db = new CalculatedFieldsTestEntities(); e.QueryableSource = db.CustomProducts(30, new DateTime(2018, 5, 16)); e.KeyExpression = "Id"; }
/// <summary> /// This method fires when the data source for the ASQSE DevExpress GridView is selecting /// and it handles the select /// </summary> /// <param name="sender">The efASQSEDataSource control</param> /// <param name="e">The LinqServerModeDataSourceSelectEventArgs event</param> protected void efASQSEDataSource_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { //Set the key expression e.KeyExpression = "ASQSEPK"; //Set the source to a LINQ query PyramidContext context = new PyramidContext(); e.QueryableSource = from a in context.ASQSE.AsNoTracking() .Include(a => a.Program) .Include(a => a.Child) .Include(a => a.CodeASQSEInterval) join cp in context.ChildProgram on a.ChildFK equals cp.ChildFK join sa in context.ScoreASQSE on a.IntervalCodeFK equals sa.IntervalCodeFK where currentProgramRole.ProgramFKs.Contains(a.ProgramFK) && cp.ProgramFK == a.ProgramFK && cp.EnrollmentDate <= a.FormDate && (cp.DischargeDate.HasValue == false || cp.DischargeDate >= a.FormDate) && a.Version == sa.Version select new { a.ASQSEPK, a.FormDate, a.TotalScore, ChildIdAndName = "(" + cp.ProgramSpecificID + ") " + a.Child.FirstName + " " + a.Child.LastName, a.Program.ProgramName, Interval = a.CodeASQSEInterval.Description, ScoreType = (a.TotalScore > sa.CutoffScore ? "Above Cutoff" : a.TotalScore >= sa.MonitoringScoreStart && a.TotalScore <= sa.MonitoringScoreEnd ? "Monitor" : a.TotalScore >= 0 && a.TotalScore < sa.MonitoringScoreStart ? "Well Below" : "Error!") }; }
protected void CargarComprobantes_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { FayceEntities db = new FayceEntities(); e.KeyExpression = "IdComprobanteVentaAuxiliar"; e.DefaultSorting = "IdComprobanteVentaAuxiliar DESC"; e.QueryableSource = from cv in db.ComprobanteVentaAuxiliar select cv; //e.QueryableSource = from cv in db.ComprobanteVenta // join p in db.Persona on cv.IdPersona equals p.Id // join mc in db.MotivoComprobante on cv.IdMotivoComprobante equals mc.IdMotivo // join es in db.Estado on cv.IdEstado equals es.Id // join u in db.Usuario on cv.IdUsuario equals u.id // select new DTOComprobantes // { // Id = cv.IdComprobanteVenta, // Fecha = cv.Fecha, // Serie = cv.Serie, // Numero = cv.Numero, // Cliente = p.TipoPersona != "NATURAL" ? p.RazonSocial : p.ApellidoPaterno + " " + p.ApellidoMaterno + ", " + p.Nombres, // Total = cv.Total, // Motivo = mc.Descripcion, // Observacion = cv.Observacion, // Estado = es.Descripcion, // Usuario = u.Nombre, // IdOrdenVenta = cv.IdOrdenVenta // }; }
/// <summary> /// this code is used with LinqServerModePricer_Selecting so we can run in server mode /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void LinqServerModePricer_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { //get user id to limit result set if (Session["user"] != null) { UserClass _thisuser = (UserClass)Session["user"]; Int32 _uid = _thisuser.UserId; Int32 _cid = _thisuser.CompanyId; //dynamic queries using system.Linq.dynamic + Dynamic.cs library e.KeyExpression = "quote_Id"; //a key expression is required if (_uid != 0 && _cid != 0) { var _nquery = new linq.linq_pricer_view1DataContext().view_price_clients.Where( c => c.request_date.GetValueOrDefault().Date == DateTime.Today && c.client_visible == true && c.request_user_id == _thisuser.UserId && c.request_company_id == _thisuser.CompanyId ); //c => c.CompanyID == 7 e.QueryableSource = _nquery; //Int32 _count = _nquery.Count(); } } else //default to display nothing in grid { e.KeyExpression = "quote_Id"; //a key expression is required var _nquery = new linq.linq_pricer_view1DataContext().view_price_clients.Where(c => c.quote_Id == -1); //_count = _nquery.Count(); e.QueryableSource = _nquery; } }//end linq server mode
/// <summary> /// This method fires when the data source for the OtherSEScreen DevExpress GridView is selecting /// and it handles the select /// </summary> /// <param name="sender">The efOtherSEScreenDataSource control</param> /// <param name="e">The LinqServerModeDataSourceSelectEventArgs event</param> protected void efOtherSEScreenDataSource_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { //Set the key expression e.KeyExpression = "OtherSEScreenPK"; //Set the source to a LINQ query PyramidContext context = new PyramidContext(); e.QueryableSource = from ose in context.OtherSEScreen.AsNoTracking() .Include(ose => ose.Program) .Include(ose => ose.Child) .Include(ose => ose.CodeScoreType) .Include(ose => ose.CodeScreenType) join cp in context.ChildProgram on ose.ChildFK equals cp.ChildFK where currentProgramRole.ProgramFKs.Contains(ose.ProgramFK) && cp.ProgramFK == ose.ProgramFK && cp.EnrollmentDate <= ose.ScreenDate && (cp.DischargeDate.HasValue == false || cp.DischargeDate >= ose.ScreenDate) select new { ose.OtherSEScreenPK, ScreenType = ose.CodeScreenType.Description, ose.ScreenDate, ose.Score, ChildIdAndName = "(" + cp.ProgramSpecificID + ") " + ose.Child.FirstName + " " + ose.Child.LastName, ose.Program.ProgramName, ScoreType = ose.CodeScoreType.Description }; }
/// <summary> /// this code is used with LinqServerModePricer_Selecting so we can run in server mode /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void LinqServerModePricer_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { if (Session["user"] != null) { string _query = ""; string _mode = this.dxhfMethod.Contains("mode") ? this.dxhfMethod["mode"].ToString() : "0"; //only 0 or 1 (quick search) UserClass _thisuser = (UserClass)Session["user"]; ParameterCollection _params = new ParameterCollection(); //check quick search parameters if (_mode == "1") { _query = get_filter(); } //company id if (this.dxcbocompany.Value != null && this.dxcbocompany.Value.ToString() != "") { _params.Add("request_company_id", this.dxcbocompany.Value.ToString()); } //now rebuild query with additional parameters if any string _f = ""; if (_params.Count > 0) { foreach (Parameter p in _params) { string _a = _f != "" ? " AND " : ""; _f += _a + "(" + p.Name.ToString() + "==" + p.DefaultValue.ToString() + ")"; } if (_query != "") { _query = _f + " AND " + _query; } else { _query = _f; } } //dynamic queries using system.Linq.dynamic + Dynamic.cs library e.KeyExpression = "quote_Id"; //a key expression is required if (!string.IsNullOrEmpty(_query)) { var _nquery = new linq.linq_pricer_view1DataContext().view_price_clients.Where(_query); //c => c.CompanyID == 7 e.QueryableSource = _nquery; //Int32 _count = _nquery.Count(); } else //default to display nothing in grid { var _nquery = new linq.linq_pricer_view1DataContext().view_price_clients.Where(c => c.quote_Id == -1); //_count = _nquery.Count(); e.QueryableSource = _nquery; } } }//end linq server mode
/// <summary> /// This method fires when the data source for the reports DevExpress GridView is selecting /// and it handles the select /// </summary> /// <param name="sender">The efReportDataSource control</param> /// <param name="e">The LinqServerModeDataSourceSelectEventArgs event</param> protected void efReportDataSource_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { //Set the primary key e.KeyExpression = "ReportCatalogPK"; //Set the source to a LINQ query PyramidContext context = new PyramidContext(); e.QueryableSource = context.ReportCatalog.AsNoTracking(); }
/// <summary> /// this code is used with LinqServerModeDataSource_Selecting so we can run in server mode /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void LinqServerModePodTop_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { try { Int32 _companyid = -1; //after testing default to empty string Int32 _userid = -1; //company id: always add as a search param as user must be logged in //if (Page.Session["user"] != null) if (Page.Session["user"] != null) { _companyid = (Int32)((UserClass)Page.Session["user"]).CompanyId; _userid = (Int32)((UserClass)Page.Session["user"]).UserId; } //020513 search over last 12 months int _months = 12; DateTime _minDate = DateTime.Now.AddMonths(0 - _months); //dynamic queries using system.Linq.dynamic + Dynamic.cs library //20/10/2010 we have build a unique index (OrderIx) from OrderId, TitleId, ContainerSubId as usual primary keys are not going to //be unique in the view. aspxgrid only works properly when it has a unique key e.KeyExpression = "OrderIx"; //"OrderID"; //a key expression is required if (_companyid > 0 && _userid > 0) { //020513 get from date range startdate + N months //var _nquery = new linq_classesDataContext().view_orders.Where(c => c.CompanyID == _companyid && c.ContactID == _userid).OrderByDescending(c => c.OrderID).Take(10); //c => c.CompanyID == 7 var _nquery = new linq.linq_view_orders_udfDataContext().view_orders_by_age(_minDate, _months).Where(c => c.CompanyID == _companyid && c.ContactID == _userid).OrderByDescending(c => c.OrderID).Take(10); e.QueryableSource = _nquery; //Int32 _count = _nquery.Count(); } else if (_companyid == -1 && _userid > 0) //internal user { //var _nquery = new linq_classesDataContext().view_orders.OrderByDescending(c => c.OrderID).Take(10); //c => c.CompanyID == 7 var _nquery = new linq.linq_view_orders_udfDataContext().view_orders_by_age(_minDate, _months).OrderByDescending(c => c.OrderID).Take(10); //_count = _nquery.Count(); e.QueryableSource = _nquery; } else //they should never reach this as must be logged in to get dashboard but just a precaution { //var _nquery = new linq_classesDataContext().view_orders.OrderBy("OrderID").OrderByDescending(c => c.OrderID).Take(10); var _nquery = new linq.linq_view_orders_udfDataContext().view_orders_by_age(_minDate, _months).OrderBy("OrderID").OrderByDescending(c => c.OrderID).Take(10); //_count = _nquery.Count(); e.QueryableSource = _nquery; } } catch (Exception ex) { this.dxlblerr4.Text = ex.Message.ToString(); this.dxlblerr4.Visible = true; } }
/// <summary> /// this code is used with LinqServerModeDataSource_Selecting so we can run in server mode /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void linqContainer_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { //important! need a key id or error=key expression is undefined e.KeyExpression = "HouseBLID"; //string _sessionfilter = Page.Session["SESSIONFILTER"] != null ? Page.Session["SESSIONFILTER"].ToString() : null; //if (!Page.IsPostBack || _sessionfilter == "clear") //{ // var _nquery = new linq.linq_view_containersDataContext().view_containers.Where(c => c.ContainerID == -1); // e.QueryableSource = _nquery; //} }
/// <summary> /// This method fires when the data source for the Benchmarks of Quality FCC DevExpress GridView is selecting /// and it handles the select /// </summary> /// <param name="sender">The efBOQFCCDataSource control</param> /// <param name="e">The LinqServerModeDataSourceSelectEventArgs event</param> protected void efBOQFCCDataSource_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { //Set the primary key e.KeyExpression = "BenchmarkOfQualityFCCPK"; //Set the source to a LINQ query PyramidContext context = new PyramidContext(); e.QueryableSource = context.BenchmarkOfQualityFCC.AsNoTracking().Include(boqfcc => boqfcc.Program) .Where(boqfcc => currentProgramRole.ProgramFKs.Contains(boqfcc.ProgramFK)); }
protected void LinqServerModeDataSource1_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { DataClassesDataContext db = new DataClassesDataContext(); e.KeyExpression = "EmployeeID"; e.QueryableSource = from employee in db.Employees join order in db.Orders on employee.EmployeeID equals order.EmployeeID select new { employee.EmployeeID, employee.FirstName, employee.LastName, order.OrderID, order.OrderDate }; }
/// <summary> /// This method fires when the data source for the classroom DevExpress GridView is selecting /// and it handles the select /// </summary> /// <param name="sender">The efClassroomDataSource control</param> /// <param name="e">The LinqServerModeDataSourceSelectEventArgs event</param> protected void efClassroomDataSource_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { //Set the key expression e.KeyExpression = "ClassroomPK"; //Set the source to a LINQ query PyramidContext context = new PyramidContext(); e.QueryableSource = context.Classroom.AsNoTracking().Include(c => c.Program) .Where(c => currentProgramRole.ProgramFKs.Contains(c.ProgramFK)); }
/// <summary> /// this code is used with LinqServerModeDataSource_Selecting so we can run in server mode /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void LinqServerModeSearchTop_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { try { Int32 _companyid = -1; //after testing default to empty string Int32 _userid = -1; //company id: always add as a search param as user must be logged in //if (Page.Session["user"] != null) if (Page.Session["user"] != null) { _companyid = (Int32)((UserClass)Page.Session["user"]).CompanyId; _userid = (Int32)((UserClass)Page.Session["user"]).UserId; } //dynamic queries using system.Linq.dynamic + Dynamic.cs library //20/10/2010 we have build a unique index (OrderIx) from OrderId, TitleId, ContainerSubId as usual primary keys are not going to //be unique in the view. aspxgrid only works properly when it has a unique key e.KeyExpression = "qry_ID"; //"OrderID"; //a key expression is required if (_companyid > 0 && _userid > 0) { //var _query = new linq_classesDataContext().view_orders.Where(_filter); var _nquery = new linq.linq_query_logDataContext().db_query_logs.Where(c => c.by_contactID == _userid).OrderByDescending(c => c.qry_ID).Take(10); //c => c.CompanyID == 7 e.QueryableSource = _nquery; //Int32 _count = _nquery.Count(); } else if (_companyid == -1 && _userid > 0) //internal user { //var _nquery = new linq_classesDataContext().view_orders.OrderBy("orderix").OrderByDescending(c => c.OrderIx).Take(10); //.Where(c => c.OrderNumber == -1 ); var _nquery = new linq.linq_query_logDataContext().db_query_logs.Where(c => c.by_employeeID == _userid).OrderByDescending(c => c.qry_ID).Take(10); //c => c.CompanyID == 7 //_count = _nquery.Count(); e.QueryableSource = _nquery; } else //they should never reach this as must be logged in to get dashboard but just a precaution { var _nquery = new linq.linq_query_logDataContext().db_query_logs.Where(c => c.qry_ID == -1); //_count = _nquery.Count(); e.QueryableSource = _nquery; } } catch (Exception ex) { this.dxlblerr5.Text = ex.Message.ToString(); this.dxlblerr5.Visible = true; } }
protected void WaybillListDS_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { e.QueryableSource = _db.WaybillList.Where(w => w.WType == -16 && w.KaId == _ka_id).Select(s => new MyOrdersView { WbillId = s.WbillId, Checked = s.Checked, Num = s.Num, OnDate = s.OnDate, SummAll = s.SummAll, Reason = s.Reason, Notes = s.Notes }); e.KeyExpression = "WbillId"; }
protected void PriceListDetDS_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { e.QueryableSource = _db.PriceListDet.Where(w => w.PlId == 205).Select(s => new { s.PlDetId, s.Num, s.Price, s.Discount, MatMeasures = _db.Materials.FirstOrDefault(w => w.MatId == s.MatId).Measures.ShortName, MatName = _db.Materials.FirstOrDefault(w => w.MatId == s.MatId).Name, MatImg = _db.Materials.FirstOrDefault(w => w.MatId == s.MatId).BMP, MatNotes = _db.Materials.FirstOrDefault(w => w.MatId == s.MatId).Notes, }); e.KeyExpression = "PlDetId"; }
protected void WaybillListDS_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { e.QueryableSource = _db.WaybillList.Where(w => w.WType == -1 && w.KaId == _ka_id).Select(s => new { s.WbillId, s.Checked, s.Num, s.OnDate, s.SummAll, s.Reason, s.Notes, PersonName = s.Kagent.Name }); e.KeyExpression = "WbillId"; }
/// <summary> /// This method fires when the data source for the cohort DevExpress GridView is selecting /// and it handles the select /// </summary> /// <param name="sender">The efCohortDataSource control</param> /// <param name="e">The LinqServerModeDataSourceSelectEventArgs event</param> protected void efCohortDataSource_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { //Set the primary key e.KeyExpression = "CohortPK"; //Set the source to a LINQ query PyramidContext context = new PyramidContext(); if (currentProgramRole.RoleFK.Value != (int)Utilities.ProgramRoleFKs.SUPER_ADMIN) { e.QueryableSource = context.Cohort.Include(h => h.Program).Where(h => h.StateFK == currentProgramRole.StateFK).AsNoTracking(); } else { e.QueryableSource = context.Cohort.Include(h => h.Program).AsNoTracking(); } }
/// <summary> /// this code is used with LinqServerModeDataSource_Selecting so we can run in server mode /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void linqservermodesearch_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { ParameterCollection _params = new ParameterCollection(); if (Session["user"] != null) { UserClass _thisuser = (UserClass)Session["user"]; Int32 _id = _thisuser.UserId; Parameter _p = new Parameter(); //dynamic queries using system.Linq.dynamic + Dynamic.cs library e.KeyExpression = "qry_ID"; //a key expression is required if (_thisuser.CompanyId != -1) //external company not WWI so search in bycontactid { //var _query = new linq_classesDataContext().view_orders.Where(_filter); var _nquery = new linq.linq_query_logDataContext().db_query_logs.Where(c => c.by_contactID == _id); //.Where(c => c.qry_source == "OV"); restrict by query source too? //_count = _query.Count(); e.QueryableSource = _nquery; } else //search in byemployeeid { //var _query = new linq_classesDataContext().view_orders.Where(_filter); var _nquery = new linq.linq_query_logDataContext().db_query_logs.Where(c => c.by_employeeID == _id); //.Where(c => c.qry_source=="OV"); //_count = _query.Count(); e.QueryableSource = _nquery; } } else { string _userip = userRequestingIP(); e.KeyExpression = "qry_ID"; //a key expression is required //var _query = new linq_classesDataContext().view_orders.Where(_filter); //09/09/2011 don't load by IP, not very secure, just default to 0 records //var _nquery = new linq_classesDataContext().db_query_logs.Where(c => c.log_ip == _userip).Where(c => c.by_employeeID == 0).Where(c => c.by_contactID == 0); //.Where(c => c.qry_source == "OV"); var _nquery = new linq.linq_query_logDataContext().db_query_logs.Where(c => c.by_employeeID == -1).Where(c => c.by_contactID == -1); //.Where(c => c.qry_source == "OV"); //_count = _query.Count(); e.QueryableSource = _nquery; } }//end linq server mode
/// <summary> /// this code is used with LinqServerModeDataSource_Selecting so we can run in server mode /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void linqservermodesearch_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { ParameterCollection _params = new ParameterCollection(); if (Session["user"] != null) { UserClass _thisuser = (UserClass)Session["user"]; Int32 _cid = _thisuser.CompanyId; Int32 _uid = _thisuser.UserId; Parameter _p = new Parameter(); //dynamic queries using system.Linq.dynamic + Dynamic.cs library e.KeyExpression = "cargoupdateid"; //a key expression is required if (_cid != -1) //external company not WWI so search in by company id { //var _query = new linq_classesDataContext().view_orders.Where(_filter); var _nquery = new linq.Linq_Classes_CargoDataContext().view_cargo_updates.Where(c => c.companyid == _cid); //_count = _query.Count(); e.QueryableSource = _nquery; } else //search in byemployeeid { //var _query = new linq_classesDataContext().view_orders.Where(_filter); var _nquery = new linq.Linq_Classes_CargoDataContext().view_cargo_updates.Where(c => c.userid == _uid); //_count = _query.Count(); e.QueryableSource = _nquery; } } else { e.KeyExpression = "cargoupdateid"; //a key expression is required //var _query = new linq_classesDataContext().view_orders.Where(_filter); var _nquery = new linq.Linq_Classes_CargoDataContext().view_cargo_updates.Where(c => c.cargoupdateid == 0); //_count = _query.Count(); e.QueryableSource = _nquery; } }//end linq server mode
/// <summary> /// This method fires when the data source for the program DevExpress GridView is selecting /// and it handles the select /// </summary> /// <param name="sender">The efProgramDataSource control</param> /// <param name="e">The LinqServerModeDataSourceSelectEventArgs event</param> protected void efProgramDataSource_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { //Set the primary key e.KeyExpression = "ProgramPK"; //Set the source to a LINQ query PyramidContext context = new PyramidContext(); if (currentProgramRole.RoleFK.Value != (int)Utilities.ProgramRoleFKs.SUPER_ADMIN) { e.QueryableSource = from p in context.Program join pt in context.ProgramType on p.ProgramPK equals pt.ProgramFK into types where p.StateFK == currentProgramRole.StateFK select new { p.ProgramPK, p.ProgramName, p.Location, HubName = p.Hub.Name, CohortName = p.Cohort.CohortName, StateName = p.State.Name, ProgramTypes = from pt in types select pt.CodeProgramType.Description }; } else { e.QueryableSource = from p in context.Program join pt in context.ProgramType on p.ProgramPK equals pt.ProgramFK into types select new { p.ProgramPK, p.ProgramName, p.Location, HubName = p.Hub.Name, CohortName = p.Cohort.CohortName, StateName = p.State.Name, ProgramTypes = from pt in types select pt.CodeProgramType.Description }; } }
/// <summary> /// This method fires when the data source for the CoachingLog DevExpress GridView is selecting /// and it handles the select /// </summary> /// <param name="sender">The efCoachingLogDataSource control</param> /// <param name="e">The LinqServerModeDataSourceSelectEventArgs event</param> protected void efCoachingLogDataSource_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { //Set the key expression e.KeyExpression = "CoachingLogPK"; //Set the source to a LINQ query PyramidContext context = new PyramidContext(); e.QueryableSource = context.CoachingLog.AsNoTracking() .Include(cl => cl.Program) .Include(cl => cl.Coach) .Include(cl => cl.Teacher) .Where(cl => currentProgramRole.ProgramFKs.Contains(cl.ProgramFK)) .Select(cl => new { cl.CoachingLogPK, cl.LogDate, cl.DurationMinutes, CoachName = cl.Coach.FirstName + " " + cl.Coach.LastName, TeacherName = cl.Teacher.FirstName + " " + cl.Teacher.LastName, ProgramName = cl.Program.ProgramName }); }
/// <summary> /// This method fires when the data source for the TPITOS DevExpress GridView is selecting /// and it handles the select /// </summary> /// <param name="sender">The efTPITOSDataSource control</param> /// <param name="e">The LinqServerModeDataSourceSelectEventArgs event</param> protected void efTPITOSDataSource_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { //Set the key expression e.KeyExpression = "TPITOSPK"; //Set the source to a LINQ query PyramidContext context = new PyramidContext(); e.QueryableSource = context.TPITOS.AsNoTracking() .Include(t => t.Classroom) .Include(t => t.Classroom.Program) .Where(t => currentProgramRole.ProgramFKs.Contains(t.Classroom.ProgramFK)) .OrderByDescending(t => t.ObservationStartDateTime) .Select(t => new { t.TPITOSPK, t.ObservationStartDateTime, t.ObservationEndDateTime, t.IsValid, IsValidText = (t.IsValid ? "Valid" : "Invalid"), ClassroomIdAndName = "(" + t.Classroom.ProgramSpecificID + ") " + t.Classroom.Name, ProgramName = t.Classroom.Program.ProgramName }); }
/// <summary> /// This method fires when the data source for the employee DevExpress GridView is selecting /// and it handles the select /// </summary> /// <param name="sender">The efEmployeeDataSource control</param> /// <param name="e">The LinqServerModeDataSourceSelectEventArgs event</param> protected void efEmployeeDataSource_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { //Set the key expression e.KeyExpression = "ProgramEmployeePK"; //Set the source to a LINQ query PyramidContext context = new PyramidContext(); e.QueryableSource = (from pe in context.ProgramEmployee.AsNoTracking().Include(pe => pe.CodeTermReason).Include(pe => pe.Program) join jf in context.JobFunction on pe.ProgramEmployeePK equals jf.ProgramEmployeeFK into jobFunctions where currentProgramRole.ProgramFKs.Contains(pe.ProgramFK) select new { pe.ProgramEmployeePK, Name = pe.FirstName + " " + pe.LastName, pe.EmailAddress, JobFunctions = from jf in jobFunctions where jf.EndDate.HasValue == false select jf.CodeJobType.Description, pe.HireDate, pe.TermDate, TermReason = pe.CodeTermReason.Description + " " + (pe.TermReasonSpecify == null ? "" : "(" + pe.TermReasonSpecify + ")"), pe.ProgramFK, pe.Program.ProgramName }); }
//end pallet ids #endregion #region grid databinding /// <summary> /// this code is used with LinqServerModeDataSource_Selecting so we can run in server mode /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void linqdsContainer_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { ParameterCollection _params = new ParameterCollection(); string _query = ""; //**************** //get starting ETS to match container drill down for deliveries from xml file (which makes it easy to change if necessary) DateTime?_ets = wwi_func.vdatetime(wwi_func.lookup_xml_string("xml\\parameters.xml", "name", "pearsonstartETS", "value")); //using exworks //DateTime? _exworks = wwi_func.vdatetime(wwi_func.lookup_xml_string("xml\\parameters.xml", "name", "startExWorks", "value")); //**************** //get container id //26/02/15 we aren't passing containerid any more just use containernumber string _containerno = this.dxcboContainer.Text != null?this.dxcboContainer.Text.ToString() : ""; if (Page.Session["containeritemsloaded"] != null) { //09/04/15 don't need to add as a param, using a new linq query with containernumber included in it //if(!string.IsNullOrEmpty(_containerno)) //{ // _params.Add("ContainerNumber", "\"" + _containerno + "\""); //} //*************** string _contactid = ((UserClass)Page.Session["user"]).UserId.ToString(); //09.04.2015 Paul Edwards check which clients are are visible for this company IList <string> _clientids = null; _clientids = wwi_func.array_from_xml("xml\\contact_iso.xml", "contactlist/contact[id='" + _contactid + "']/clientids/clientid/value"); if (_clientids.Count > 0) { //don't use sql IN(n) as linq won't parse the statement string _clients = "(CompanyID ==" + string.Join(" OR CompanyID ==", _clientids.Select(i => i.ToString()).ToArray()) + ")"; _params.Add("NULL", _clients); } //21.10.2014 Paul Edwards for delivery tracking check which DeliveryID's are visible for this company IList <string> _deliveryids = null; _deliveryids = wwi_func.array_from_xml("xml\\contact_iso.xml", "contactlist/contact[id='" + _contactid + "']/deliveryids/deliveryid/value"); if (_deliveryids.Count > 0) { //don't use sql IN(n) as linq won't parse the statement string _deliveries = "(DeliveryAddress==" + string.Join(" OR DeliveryAddress==", _deliveryids.Select(i => i.ToString()).ToArray()) + ")"; _params.Add("NULL", _deliveries); //select for this company off list } //**************** //now rebuild query with additional parameters AFTER page is loaded string _f = ""; if (_params.Count > 0) { foreach (Parameter p in _params) { string _pname = p.Name.ToString(); string _op = "AND"; string[] _check = _pname.Split("_".ToCharArray()); _pname = _check[0].ToString(); _op = _check.Length > 1 ? _check[1].ToString() : _op; string _a = _f != "" ? " " + _op + " " : ""; _f += _pname != "NULL" ? _a + "(" + _pname + "==" + p.DefaultValue.ToString() + ")" : _a + "(" + p.DefaultValue.ToString() + ")"; } if (_query != "") { _query = _f + " AND " + _query; } else { _query = _f; } } //end params } //end if //important! need a key id or error=key expression is undefined e.KeyExpression = "OrderIx";//"SubDeliveryID"; //"OrderIx"; //"OrderID"; //a key expression is required if (!string.IsNullOrEmpty(_query)) { //make sure using System.Linq.Dynamic; and using System.Linq.Expressions or can't use a query string; var _nquery = new linq.linq_container_contentsDataContext().view_container_contents(_ets, _containerno).Where(_query); e.QueryableSource = _nquery; } else if (!string.IsNullOrEmpty(_containerno)) { var _nquery = new linq.linq_container_contentsDataContext().view_container_contents(_ets, _containerno).Where(c => c.ContainerNumber != null); e.QueryableSource = _nquery; } else //default to display nothing until page is loaded { var _nquery = new linq.linq_container_contentsDataContext().view_container_contents(_ets, _containerno).Where(c => c.OrderNumber == -1); //c => c.CompanyID == 7 //_count = _nquery.Count(); e.QueryableSource = _nquery; } }
protected void esmdsRegisteredCompanies_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { e.KeyExpression = "CacNumber"; }