/// <summary>
/// 取得ME資料
/// </summary>
/// <param name="search">查詢條件集合</param>
/// <param name="page">分頁參數</param>
/// <returns></returns>
public PagedListModel <RP_ME_VM> QueryMEs(RP_MESearch search, Page page)
{
var totalCount = 0;
var MEs = rP_Flowchart_MasterRepository.QueryMEs(search, page, out totalCount);
return(new PagedListModel <RP_ME_VM>(totalCount, MEs));
}
public ActionResult QueryMEs(RP_MESearch search, Page page)
{
search.Account_UID = this.CurrentUser.AccountUId;
var apiUrl = string.Format("ProductionResourcePlan/QueryMEsAPI");
HttpResponseMessage responMessage = APIHelper.APIPostAsync(search, page, apiUrl);
var result = responMessage.Content.ReadAsStringAsync().Result;
return(Content(result, "application/json"));
}
/// <summary>
/// 取得ME清單
/// </summary>
/// <param name="search">查詢條件集合</param>
/// <param name="page">分頁參數</param>
/// <param name="count">筆數</param>
/// <returns></returns>
public List <RP_ME_VM> QueryMEs(RP_MESearch search, Page page, out int count)
{
List <RP_ME_VM> query = new List <RP_ME_VM>();
var Plant_Organization_UID = new SqlParameter("@Plant_Organization_UID", search.Plant_Organization_UID);
var BG_Organization_UID = new SqlParameter("@BG_Organization_UID", search.BG_Organization_UID);
var Project_Name = new SqlParameter("@Project_Name", search.Project_Name == null ? string.Empty : search.Project_Name.Trim());
var BU = new SqlParameter("@BU", search.BU == null ? string.Empty : search.BU.Trim());
var Part_Types = new SqlParameter("@Part_Types", search.Part_Types == null ? string.Empty : search.Part_Types.Trim());
var Product_Phase = new SqlParameter("@Product_Phase", search.Product_Phase == null ? string.Empty : search.Product_Phase.Trim());
var Is_Closed = new SqlParameter("@Is_Closed", search.Is_Closed == null ? false : search.Is_Closed);
var Start_Date = new SqlParameter("@Start_Date", search.Start_Date == null ? string.Empty : search.Start_Date);
var End_Date = new SqlParameter("@End_Date", search.End_Date == null ? string.Empty : search.End_Date);
object[] parameter = new object[] { Plant_Organization_UID, BG_Organization_UID, Project_Name, BU, Part_Types, Product_Phase, Is_Closed, Start_Date, End_Date };
using (var context = new SPPContext())
{
var sql_str = string.Empty;
sql_str += @"
--DECLARE @Plant_Organization_UID int = 1,
-- @BG_Organization_UID int = 3,
-- @Project_Name nvarchar(50) = '',
-- @BU nvarchar(50) = '',
-- @Part_Types nvarchar(50) = '',
-- @Product_Phase nvarchar(50) = '',
-- @Is_Closed bit = null,
-- @Start_Date nvarchar(50) = '',
-- @End_Date nvarchar(50) = ''
--;
SELECT a.[RP_Flowchart_Master_UID],
h.BU_Name + '-' +e.BU_D_Name as BU,
d.[Project_UID],
d.[Project_Name],
a.[Part_Types],
a.[Product_Phase],
a.[Plant_Organization_UID],
b.[Organization_Name] as Plant_Organization_Name,
a.[BG_Organization_UID],
c.[Organization_Name] as [BG_Organization_Name],
a.[Daily_Targetoutput],
a.[FPY],
a.[FlowChart_Version],
a.[FlowChart_Version_Comment],
a.[Created_UID],
f.[User_Name] as [Created_UserName],
a.[Created_Date],
a.[Modified_UID],
g.[User_Name] as [Modified_UserName],
a.[Modified_Date]
FROM [dbo].[RP_Flowchart_Master] a
LEFT JOIN [dbo].[System_Organization] b ON a.[Plant_Organization_UID] = b.[Organization_UID]
LEFT JOIN [dbo].[System_Organization] c ON a.[BG_Organization_UID] = c.[Organization_UID]
INNER JOIN [dbo].[System_Project] d ON a.[Project_UID] = d.[Project_UID]
INNER JOIN [dbo].[System_BU_D] e ON d.[BU_D_UID] = e.[BU_D_UID]
INNER JOIN [dbo].[System_Users] f ON a.[Created_UID] = f.[Account_UID]
INNER JOIN [dbo].[System_Users] g ON a.[Modified_UID] = g.[Account_UID]
INNER JOIN [dbo].[System_BU_M] h ON e.[BU_M_UID] = h.[BU_M_UID]
WHERE [Is_Latest] = 1 AND [Is_Closed] = 0
AND (a.[Plant_Organization_UID] = @Plant_Organization_UID OR @Plant_Organization_UID = 0)
AND (a.[BG_Organization_UID] = @BG_Organization_UID OR @BG_Organization_UID = 0)
AND (d.[Project_Name] like '%'+ @Project_Name +'%' OR ISNULL(@Project_Name,'') = '')
AND (h.BU_Name like N'%'+ @BU +'%' OR e.BU_D_Name like N'%'+ @BU +'%' OR ISNULL(@BU,'') = '')
AND (a.[Part_Types] like '%'+ @Part_Types +'%' OR ISNULL(@Part_Types,'') = '')
AND (a.[Product_Phase] like N'%'+ @Product_Phase +'%' OR ISNULL(@Product_Phase,'') = '')
AND (a.[Is_Closed] = @Is_Closed OR ISNULL(@Is_Closed,'') = '')
AND ((@Start_Date <= CONVERT(char(10),a.[Modified_Date],126) OR ISNULL(@Start_Date,'') = '') and
(@End_Date >= CONVERT(char(10),a.[Modified_Date],126) OR ISNULL(@End_Date,'') = ''))
";
query = context.Database.SqlQuery <RP_ME_VM>(sql_str, parameter).ToList();
}
count = query.Count();
return(query);
}
