public object GetReq(string year, string month, string cat, byte?floor)
{
var query = new StringBuilder();
query.Append(" SELECT ISNULL(YMFRD.RequisitionDateID,0) RequisitionDateID, YMFRD.RequisitionNo, ");
query.Append(" CONVERT(NVARCHAR(12), YMFRD.RequiredDate,106) RequiredDate, YMFRD.RequisitionStatus, ");
query.Append(" YMFRD.RecordStatus, YM.ConcernStore IssueFrom, YM.ProductionFloor IssueTo");
query.Append(" FROM PRD_YearMonth YM ");
query.Append(" LEFT JOIN PRD_YearMonthSchedule YMS ON YMS.YearMonID=YM.YearMonID ");
query.Append(" LEFT JOIN PRD_YearMonthFinishReqDate YMFRD ON YMFRD.ScheduleID=YMS.ScheduleID ");
query.Append(" WHERE YM.ScheduleFor = 'CRR' AND YMFRD.RequisitionDateID != 0 ");
if (month != "0")
{
query.Append(" AND YM.ScheduleMonth='" + month + "' ");
}
if (year != "0")
{
query.Append(" AND YM.ScheduleYear='" + year + "' ");
}
if (floor != 0)
{
query.Append(" AND YM.ProductionFloor=" + floor);
}
//"SELECT YMFRD.RequisitionDateID, YMFRD.RequisitionNo, YMFRD.RequiredDate, YMFRD.RequisitionStatus, YMFRD.RecordStatus " +
//"FROM PRD_YearMonth YM " +
//"LEFT JOIN PRD_YearMonthSchedule YMS ON YMS.YearMonID=YM.YearMonID " +
//"LEFT JOIN PRD_YearMonthFinishReqDate YMFRD ON YMFRD.ScheduleID=YMS.ScheduleID " +
//"WHERE YM.ScheduleMonth=" + month + " AND YM.ScheduleYear=" + year + " AND YM.ScheduleFor='" + cat + "' AND YM.ProductionFloor=" + floor;
var items = _context.Database.SqlQuery <RequisitionVM>(query.ToString()).ToList();
var results = items.Select(item => new
{
item.RequisitionDateID,
item.RequisitionNo,
RequiredDate = string.Format("{0:dd/MM/yyyy}", item.RequiredDate),
RequisitionStatus = DalCommon.ReturnRequisitionStatus(item.RequisitionStatus),
RecordStatus = DalCommon.ReturnRecordStatus(item.RecordStatus),
item.IssueFrom,
item.IssueTo
}).ToList();
return(results.OrderByDescending(ob => ob.RequisitionDateID));
}