/// <summary> /// Query system users and order by account_id asc /// </summary> /// <param name="search">search model</param> /// <param name="page">page info</param> /// <param name="count">number of total records</param> /// <returns></returns> public IQueryable <System_Plant> QueryPlants(PlantModelSearch search, Page page, out int count) { var query = from plant in DataContext.System_Plant.Include("System_Users") select plant; if (string.IsNullOrEmpty(search.ExportUIds)) { #region Query_Types if (search.query_types != null && search.Reference_Date != null) { EnumValidity queryType = (EnumValidity)Enum.ToObject(typeof(EnumValidity), search.query_types); switch (queryType) { case EnumValidity.Valid: query = query.Where(p => p.Begin_Date <= search.Reference_Date && (p.End_Date >= search.Reference_Date || p.End_Date == null)); break; case EnumValidity.Invalid: query = query.Where(p => p.Begin_Date > search.Reference_Date || (p.End_Date < search.Reference_Date && p.End_Date != null)); break; default: break; } } #endregion #region Modified_Date if (search.Modified_Date_From != null) { query = query.Where(m => m.Modified_Date >= search.Modified_Date_From); } if (search.Modified_Date_End != null) { var endDate = ((DateTime)search.Modified_Date_End).AddDays(1); query = query.Where(m => m.Modified_Date < endDate); } #endregion #region 查询Modified_NTID if (!string.IsNullOrWhiteSpace(search.Modified_By_NTID)) { query = query.Where(q => q.System_Users.User_NTID == search.Modified_By_NTID); } #endregion if (!string.IsNullOrWhiteSpace(search.Plant)) { query = query.Where(p => p.Plant == search.Plant); } if (!string.IsNullOrWhiteSpace(search.Location)) { query = query.Where(p => p.Location.Contains(search.Location)); } //Name_0,公司代碼。 if (!string.IsNullOrWhiteSpace(search.Name_0)) { query = query.Where(p => p.Name_0 == search.Name_0); } //Name_1,公司中文名稱模糊查詢。 if (!string.IsNullOrWhiteSpace(search.Name_1)) { query = query.Where(p => p.Name_1.Contains(search.Name_1)); } if (!string.IsNullOrWhiteSpace(search.PlantManager_Name)) { query = query.Where(p => p.PlantManager_Name.Contains(search.PlantManager_Name)); } if (!string.IsNullOrWhiteSpace(search.Type)) { query = query.Where(p => p.Type.Contains(search.Type)); } count = query.Count(); return(query.OrderBy(o => o.System_Plant_UID).GetPage(page)); } else { //for export data var array = Array.ConvertAll(search.ExportUIds.Split(','), s => int.Parse(s)); query = query.Where(p => array.Contains(p.System_Plant_UID)); count = 0; return(query.OrderBy(o => o.Plant)); } }
/// <summary> /// Query system users and order by account_id asc /// </summary> /// <param name="search">search model</param> /// <param name="page">page info</param> /// <param name="count">number of total records</param> /// <returns></returns> public IQueryable <SystemOrgAndBomDTO> QueryOrgBoms(OrgBomModelSearch search, Page page, out int count) { var query = from OrgBom in DataContext.System_OrganizationBOM.Include("System_Users") join childOrg in DataContext.System_Organization on OrgBom.ChildOrg_UID equals childOrg.Organization_UID join parentOrg in DataContext.System_Organization on OrgBom.ParentOrg_UID equals parentOrg.Organization_UID into g1 from g2 in g1.DefaultIfEmpty() select new SystemOrgAndBomDTO { OrganizationBOM_UID = OrgBom.OrganizationBOM_UID, ParentOrg_ID = g2.Organization_ID ?? string.Empty, Parent_Organization_Name = g2.Organization_Name ?? string.Empty, ChildOrg_ID = childOrg.Organization_ID, Child_Organization_Name = childOrg.Organization_Name, Begin_Date = OrgBom.Begin_Date, End_Date = OrgBom.End_Date, Order_Index = OrgBom.Order_Index, Modified_Date = OrgBom.Modified_Date, Modified_UserName = OrgBom.System_Users.User_Name, Modified_UserNTID = OrgBom.System_Users.User_NTID }; if (string.IsNullOrEmpty(search.ExportUIds)) { if (!string.IsNullOrWhiteSpace(search.ParentOrg_ID)) { query = query.Where(p => p.ParentOrg_ID == search.ParentOrg_ID); } if (!string.IsNullOrWhiteSpace(search.ParentOrg_Name)) { query = query.Where(p => p.Parent_Organization_Name.Contains(search.ParentOrg_Name)); } if (!string.IsNullOrWhiteSpace(search.ChildOrg_ID)) { query = query.Where(p => p.ChildOrg_ID == search.ChildOrg_ID); } if (!string.IsNullOrWhiteSpace(search.ChildOrg_Name)) { query = query.Where(p => p.Child_Organization_Name.Contains(search.ChildOrg_Name)); } #region Query_Types if (search.query_types != null && search.Reference_Date != null) { EnumValidity queryType = (EnumValidity)Enum.ToObject(typeof(EnumValidity), search.query_types); switch (queryType) { case EnumValidity.Valid: query = query.Where(p => p.Begin_Date <= search.Reference_Date && (p.End_Date >= search.Reference_Date || p.End_Date == null)); break; case EnumValidity.Invalid: query = query.Where(p => p.Begin_Date > search.Reference_Date || (p.End_Date < search.Reference_Date && p.End_Date != null)); break; default: break; } } #endregion #region Modified_Date if (search.Modified_Date_From != null) { query = query.Where(m => SqlFunctions.DateDiff("dd", m.Modified_Date, search.Modified_Date_From) <= 0); } if (search.Modified_Date_End != null) { query = query.Where(m => SqlFunctions.DateDiff("dd", m.Modified_Date, search.Modified_Date_End) >= 0); } #endregion if (!string.IsNullOrWhiteSpace(search.Modified_By_NTID)) { query = query.Where(p => p.Modified_UserNTID == search.Modified_By_NTID); } count = query.Count(); return(query.OrderBy(o => o.ParentOrg_ID).ThenBy(o => o.Order_Index).ThenBy(o => o.ChildOrg_ID).GetPage(page)); } else { //for export data var array = Array.ConvertAll(search.ExportUIds.Split(','), s => int.Parse(s)); query = query.Where(p => array.Contains(p.OrganizationBOM_UID)); count = 0; return(query.OrderBy(o => o.ParentOrg_ID).ThenBy(o => o.Order_Index).ThenBy(o => o.ChildOrg_ID)); } }
/// <summary> /// Query system users and order by account_id asc /// </summary> /// <param name="search">search model</param> /// <param name="page">page info</param> /// <param name="count">number of total records</param> /// <returns></returns> public IQueryable <SystemOrgDTO> QueryOrgs(OrgModelSearch search, Page page, out int count) { var query = from Org in DataContext.System_Organization.Include("System_Users") select new SystemOrgDTO { Organization_UID = Org.Organization_UID, Organization_ID = Org.Organization_ID, Organization_Name = Org.Organization_Name, Organization_Desc = Org.Organization_Desc, Begin_Date = Org.Begin_Date, End_Date = Org.End_Date, OrgManager_Name = Org.OrgManager_Name, OrgManager_Tel = Org.OrgManager_Tel, OrgManager_Email = Org.OrgManager_Email, Cost_Center = Org.Cost_Center, Modified_Date = Org.Modified_Date, Modified_UserName = Org.System_Users.User_Name, Modified_UserNTID = Org.System_Users.User_NTID }; if (string.IsNullOrEmpty(search.ExportUIds)) { #region Query_Types if (search.query_types != null && search.Reference_Date != null) { EnumValidity queryType = (EnumValidity)Enum.ToObject(typeof(EnumValidity), search.query_types); switch (queryType) { case EnumValidity.Valid: query = query.Where(p => p.Begin_Date <= search.Reference_Date && (p.End_Date >= search.Reference_Date || p.End_Date == null)); break; case EnumValidity.Invalid: query = query.Where(p => p.Begin_Date > search.Reference_Date || (p.End_Date < search.Reference_Date && p.End_Date != null)); break; default: break; } } #endregion #region Modified_Date if (search.Modified_Date_From != null) { query = query.Where(m => SqlFunctions.DateDiff("dd", m.Modified_Date, search.Modified_Date_From) <= 0); } if (search.Modified_Date_End != null) { query = query.Where(m => SqlFunctions.DateDiff("dd", m.Modified_Date, search.Modified_Date_End) >= 0); } #endregion if (!string.IsNullOrWhiteSpace(search.Organization_ID)) { query = query.Where(p => p.Organization_ID == search.Organization_ID); } if (!string.IsNullOrWhiteSpace(search.Organization_Name)) { query = query.Where(p => p.Organization_Name.Contains(search.Organization_Name)); } if (!string.IsNullOrWhiteSpace(search.Organization_Desc)) { query = query.Where(p => p.Organization_Desc.Contains(search.Organization_Desc)); } if (!string.IsNullOrWhiteSpace(search.OrgManager_Name)) { query = query.Where(p => p.OrgManager_Name.Contains(search.OrgManager_Name)); } if (!string.IsNullOrWhiteSpace(search.Cost_Center)) { query = query.Where(p => p.Cost_Center == search.Cost_Center); } if (!string.IsNullOrWhiteSpace(search.Modified_By_NTID)) { query = query.Where(q => q.Modified_UserNTID == search.Modified_By_NTID); } count = query.Count(); return(query.OrderBy(o => o.Organization_ID).GetPage(page)); } else { //for export data var array = Array.ConvertAll(search.ExportUIds.Split(','), s => int.Parse(s)); query = query.Where(p => array.Contains(p.Organization_UID)); count = 0; return(query.OrderBy(o => o.Organization_ID)); } }
public IQueryable <UserPlantItem> QueryUserPlants(UserPlantModelSearch search, Page page, out int count) { var query = from userplant in DataContext.System_User_Plant join users in DataContext.System_Users on userplant.Account_UID equals users.Account_UID join modifyusers in DataContext.System_Users on userplant.Modified_UID equals modifyusers.Account_UID join plant in DataContext.System_Plant on userplant.System_Plant_UID equals plant.System_Plant_UID select new UserPlantItem { Account_UID = userplant.Account_UID, User_Plant_UID = userplant.System_User_Plant_UID, User_NTID = users.User_NTID, User_Name = users.User_Name, Plant = plant.Plant, Location = plant.Location, Type = plant.Type, Plant_Code = plant.Name_0, Begin_Date = userplant.Begin_Date, End_Date = userplant.End_Date, Modified_UID = userplant.Modified_UID, Modified_Date = userplant.Modified_Date, Modified_UserName = modifyusers.User_Name, Modified_UserNTID = modifyusers.User_NTID }; if (string.IsNullOrEmpty(search.ExportUIds)) { #region Query_Types if (search.query_types != null && search.Reference_Date != null) { EnumValidity queryType = (EnumValidity)Enum.ToObject(typeof(EnumValidity), search.query_types); switch (queryType) { case EnumValidity.Valid: query = query.Where(p => p.Begin_Date <= search.Reference_Date && (p.End_Date >= search.Reference_Date || p.End_Date == null)); break; case EnumValidity.Invalid: query = query.Where(p => p.Begin_Date > search.Reference_Date || (p.End_Date < search.Reference_Date && p.End_Date != null)); break; default: break; } } #endregion #region Modified_Date if (search.Modified_Date_From != null) { query = query.Where(m => m.Modified_Date >= search.Modified_Date_From); } if (search.Modified_Date_End != null) { var endDate = ((DateTime)search.Modified_Date_End).AddDays(1); query = query.Where(m => m.Modified_Date < endDate); } #endregion if (!string.IsNullOrWhiteSpace(search.User_NTID)) { query = query.Where(p => p.User_NTID == search.User_NTID); } if (!string.IsNullOrWhiteSpace(search.User_Name)) { query = query.Where(p => p.User_Name.Contains(search.User_Name)); } if (!string.IsNullOrWhiteSpace(search.Plant)) { query = query.Where(p => p.Plant == search.Plant); } if (!string.IsNullOrWhiteSpace(search.Location)) { query = query.Where(p => p.Location.Contains(search.Location)); } if (!string.IsNullOrWhiteSpace(search.Type)) { query = query.Where(p => p.Type.Contains(search.Type)); } if (!string.IsNullOrWhiteSpace(search.Plant_Code)) { query = query.Where(p => p.Plant_Code.Contains(search.Plant_Code)); } if (!string.IsNullOrWhiteSpace(search.Modified_By_NTID)) { query = query.Where(p => p.Modified_UserNTID == search.Modified_By_NTID); } count = query.Count(); return(query.OrderBy(o => o.User_Plant_UID).GetPage(page)); } else { //for export data var array = Array.ConvertAll(search.ExportUIds.Split(','), s => int.Parse(s)); query = query.Where(p => array.Contains(p.User_Plant_UID)); count = 0; return(query.OrderByDescending(o => o.Modified_Date)); } }
public IQueryable <UserOrgItem> QueryUserOrgs(UserOrgModelSearch search, Page page, out int count) { var query = from userOrg in DataContext.System_UserOrg.Include("System_Users") join users in DataContext.System_Users on userOrg.Account_UID equals users.Account_UID join modifyusers in DataContext.System_Users on userOrg.Modified_UID equals modifyusers.Account_UID join Org in DataContext.System_Organization on userOrg.Organization_UID equals Org.Organization_UID select new UserOrgItem { System_UserOrgUID = userOrg.System_UserOrgUID, User_NTID = users.User_NTID, User_Name = users.User_Name, Organization_ID = Org.Organization_ID, Organization_Name = Org.Organization_Name, Begin_Date = userOrg.Begin_Date, End_Date = userOrg.End_Date, Account_UID = userOrg.Account_UID, Modified_UID = userOrg.Modified_UID, Modified_Date = userOrg.Modified_Date, Modified_UserName = modifyusers.User_Name, Modified_UserNTID = modifyusers.User_NTID }; if (string.IsNullOrEmpty(search.ExportUIds)) { #region Query_Types if (search.query_types != null && search.Reference_Date != null) { EnumValidity queryType = (EnumValidity)Enum.ToObject(typeof(EnumValidity), search.query_types); switch (queryType) { case EnumValidity.Valid: query = query.Where(p => p.Begin_Date <= search.Reference_Date && (p.End_Date >= search.Reference_Date || p.End_Date == null)); break; case EnumValidity.Invalid: query = query.Where(p => p.Begin_Date > search.Reference_Date || (p.End_Date < search.Reference_Date && p.End_Date != null)); break; default: break; } } #endregion #region Modified_Date if (search.Modified_Date_From != null) { query = query.Where(m => SqlFunctions.DateDiff("dd", m.Modified_Date, search.Modified_Date_From) <= 0); } if (search.Modified_Date_End != null) { query = query.Where(m => SqlFunctions.DateDiff("dd", m.Modified_Date, search.Modified_Date_End) >= 0); } #endregion if (!string.IsNullOrWhiteSpace(search.User_NTID)) { query = query.Where(p => p.User_NTID == search.User_NTID); } if (!string.IsNullOrWhiteSpace(search.User_Name)) { query = query.Where(p => p.User_Name.Contains(search.User_Name)); } if (!string.IsNullOrWhiteSpace(search.Organization_ID)) { query = query.Where(p => p.Organization_ID == search.Organization_ID); } if (!string.IsNullOrWhiteSpace(search.Organization_Name)) { query = query.Where(p => p.Organization_Name.Contains(search.Organization_Name)); } if (!string.IsNullOrWhiteSpace(search.Modified_By_NTID)) { query = query.Where(q => q.Modified_UserNTID == search.Modified_By_NTID); } count = query.Count(); return(query.OrderBy(o => o.User_NTID).ThenBy(o => o.Organization_ID).GetPage(page)); } else { //for export data var array = Array.ConvertAll(search.ExportUIds.Split(','), s => int.Parse(s)); query = query.Where(p => array.Contains(p.System_UserOrgUID)); count = 0; return(query.OrderByDescending(o => o.Modified_Date)); } }