/// <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";
}
