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