public List <GLFourQDTModel> GetPaynterChartDetial(GLFourQParamModel paramModel) { var result = GL_DownTimeRecordRepository.GetPaynterChartDetial(paramModel); foreach (var Item in result) { var total = result.Where(x => x.DtMon == Item.DtMon).Sum(x => x.DTTime); if (total != 0) { Item.DTTime_p = Math.Round((double)((double)Item.DTTime / (double)total) * 100, 2); } else { Item.DTTime_p = 0; } if (paramModel.ReportType == "Month") { Item.DtMon = Item.DtMon + "M"; } else if (paramModel.ReportType == "Week") { Item.DtMon = Item.DtMon + "W"; } else { Item.DtMon = Item.DtMon; } } return(result); }
/// <summary> /// 抓取該周期最後一筆資料(依部門分類) /// </summary> /// <param name="paramModel"></param> /// <returns></returns> public List <GLFourQDTModel> GetFourQDTTypeDetail(GLFourQParamModel paramModel) { var result = GL_DownTimeRecordRepository.GetFourQDTTypeDetail(paramModel); var resultList = new List <GLFourQDTModel>(); int totalTime = 0; foreach (var item in result) { totalTime += item.DTTime; } foreach (var item in result) { GLFourQDTModel model = new GLFourQDTModel(); model.DTTime = item.DTTime; if (totalTime != 0) { model.DTTime_p = (item.DTTime / totalTime) * 100; } else { model.DTTime_p = 0; } model.DTName = item.DTName; resultList.Add(model); } return(resultList); }
/// <summary> /// 根据报表类型获取DT的时间 /// </summary> /// <param name="paramModel"></param> /// <returns></returns> public List <GLFourQDTModel> GetDownTimeRecord(GLFourQParamModel paramModel) { var result = GL_DownTimeRecordRepository.GetDownTimeRecord(paramModel); var resultList = new List <GLFourQDTModel>(); int totalTime = 0; foreach (var item in result) { totalTime += item.DTTime; } foreach (var item in result) { GLFourQDTModel model = new GLFourQDTModel(); model.DTTime = item.DTTime; if (totalTime != 0) { model.DTTime_p = (item.DTTime / totalTime) * 100; } else { model.DTTime_p = 0; } if (paramModel.ReportType == "Month") { model.DTName = item.DTName + "M"; } else if (paramModel.ReportType == "Week") { model.DTName = item.DTName + "W"; } else { model.DTName = item.DTName; } resultList.Add(model); } return(resultList); }
public PagedListModel <GL_ActionTaskDTO> QueryActionInfoByCreateDate(GLFourQParamModel paramModel, Page page) { var dtResult = actionTaskRepository.QueryActionInfoByCreateDate(paramModel, page); return(dtResult); }
public PagedListModel <GL_ActionTaskDTO> QueryActionInfoByCreateDate(GLFourQParamModel serchModel, Page page) { var query = from GL_ActionTasker in DataContext.GL_ActionTasker where GL_ActionTasker.Plant_Organization_UID == serchModel.Plant_Organization_UID && GL_ActionTasker.BG_Organization_UID == serchModel.BG_Organization_UID && GL_ActionTasker.StationID == serchModel.StationID select new GL_ActionTaskDTO { ActionTasker_UID = GL_ActionTasker.ActionTasker_UID , Plant_Organization_UID = GL_ActionTasker.Plant_Organization_UID , Plant_Organization_Name = GL_ActionTasker.System_Organization2.Organization_Name , BG_Organization_UID = GL_ActionTasker.BG_Organization_UID , BG_Organization_Name = GL_ActionTasker.System_Organization1.Organization_Name, FunPlant_Organization_UID = GL_ActionTasker.FunPlant_Organization_UID , FunPlant_Organization_Name = GL_ActionTasker.System_Organization.Organization_Name, Project_UID = GL_ActionTasker.Project_UID , ProjectName = GL_ActionTasker.GL_Line.System_Project.Project_Name, LineID = GL_ActionTasker.LineID , LineName = GL_ActionTasker.GL_Line.LineName, StationID = GL_ActionTasker.StationID , StationName = GL_ActionTasker.GL_Station.StationName , MeetingType_UID = GL_ActionTasker.MeetingType_UID , ActionTasker_ID = GL_ActionTasker.ActionTasker_ID , ActionTasker_Name = GL_ActionTasker.ActionTasker_Name , Audience = GL_ActionTasker.Audience , Responsible = GL_ActionTasker.Responsible , Status = GL_ActionTasker.Status , Commit_Date = GL_ActionTasker.Commit_Date , Due_Date = GL_ActionTasker.Due_Date , Close_Date = GL_ActionTasker.Close_Date , Created_UID = GL_ActionTasker.Created_UID , Created_Date = GL_ActionTasker.Created_Date , Problem_Description = GL_ActionTasker.Problem_Description , Root_Cause = GL_ActionTasker.Root_Cause , Action_Description = GL_ActionTasker.Action_Description , Comment = GL_ActionTasker.Comment , Modified_UID = GL_ActionTasker.Modified_UID , Modified_Date = GL_ActionTasker.Modified_Date , Attachment1 = GL_ActionTasker.Attachment1 , Attachment6 = GL_ActionTasker.Attachment6 , Attachment2 = GL_ActionTasker.Attachment2 , Attachment4 = GL_ActionTasker.Attachment4 , Attachment5 = GL_ActionTasker.Attachment5 , Remarks = GL_ActionTasker.Remarks , Department = GL_ActionTasker.Department , CloseByUser = GL_ActionTasker.CloseByUser , DirDueDate = GL_ActionTasker.DirDueDate }; var startDate = Convert.ToDateTime(serchModel.ActionCreateData.ToString("yyyy-MM-dd 00:00:00")); var endDate = Convert.ToDateTime(serchModel.ActionCreateData.ToString("yyyy-MM-dd 23:59:59")); query = query.Where(p => p.Created_Date >= startDate && p.Created_Date <= endDate); var totalCount = query.Count(); query = query.OrderByDescending(m => m.Modified_Date).GetPage(page); return(new PagedListModel <GL_ActionTaskDTO>(totalCount, query.ToList())); }
public List <GLFourQDTModel> GetPaynterChartDetial(GLFourQParamModel searchModel) { string sql = ""; #region Daily SQL if (searchModel.ReportType == "Daily") { sql += string.Format(@"Declare @mindate date Declare @maxdate date set @mindate = DATEADD(day, -13, '{0}') set @maxdate = '{0}' ;with temptab(date) as ( select @mindate union all select dateadd(d,1,temptab.date) as date from temptab where dateadd(d,1,temptab.date)<=@maxdate ) select f.WorkDate,CONVERT(varchar(10), f.WorkDate)DTMon,isnull(dt2.DTTime,0)DTTime,isnull(dt2.DTName,'')DTName from (select Exception_Dept_Name as DTName,WorkDate,sum(downtime) as DTTime from ( SELECT [Exception_Record_UID] ,[Origin_UID] ,c.LineName ,[SubOrigin_UID] ,b.StationName ,a.[Exception_Code_UID] ,d.Exception_Dept_UID ,e.Exception_Dept_Name ,e.Plant_Organization_UID ,e.BG_Organization_UID ,e.Funplant_Organization_UID ,[Project_UID] ,[WorkDate] ,[ShiftTimeID] ,[Status] ,[End_Date] ,[Note] ,a.[Created_UID] ,a.[Created_Date] ,a.[Modified_UID] ,a.[Modified_Date] ,[Contact_Person] ,[Contact_Phone] ,case when End_Date is null then DATEDIFF(MINUTE, a.Created_Date,GETDATE() ) else DATEDIFF(Hour, a.Created_Date,End_Date ) end as downtime ,MONTH(WorkDate)mon FROM [dbo].[Exception_Record] a left join GL_Station b on a.SubOrigin_UID=b.StationID left join GL_Line c on a.Origin_UID = c.LineID left join Exception_Code d on a.Exception_Code_UID = d.Exception_Code_UID left join Exception_Dept e on d.Exception_Dept_UID = e.Exception_Dept_UID", searchModel.EndTime.ToString("yyyy-MM-dd")); } #endregion #region Month SQL if (searchModel.ReportType == "Month") { sql += string.Format(@"Declare @mindate date Declare @maxdate date set @mindate = DATEADD(MONTH, -11, dateadd(dd,-datepart(dd,'{0}')+1,'{0}')) set @maxdate = dateadd(dd,-datepart(dd,'{0}')+1,'{0}') ;with temptab(date) as ( select @mindate union all select dateadd(MONTH,1,temptab.date) as date from temptab where dateadd(MONTH,1,temptab.date)<=@maxdate ) select f.WorkDate,CONVERT(varchar(10), f.mon)DTMon,isnull(dt2.DTTime,0)DTTime,isnull(dt2.DTName,'')DTName from (select Exception_Dept_Name as DTName,mon,sum(downtime) as DTTime from ( SELECT [Exception_Record_UID] ,[Origin_UID] ,c.LineName ,[SubOrigin_UID] ,b.StationName ,a.[Exception_Code_UID] ,d.Exception_Dept_UID ,e.Exception_Dept_Name ,e.Plant_Organization_UID ,e.BG_Organization_UID ,e.Funplant_Organization_UID ,[Project_UID] ,[WorkDate] ,[ShiftTimeID] ,[Status] ,[End_Date] ,[Note] ,a.[Created_UID] ,a.[Created_Date] ,a.[Modified_UID] ,a.[Modified_Date] ,[Contact_Person] ,[Contact_Phone] ,case when End_Date is null then DATEDIFF(MINUTE, a.Created_Date,GETDATE() ) else DATEDIFF(Hour, a.Created_Date,End_Date ) end as downtime ,MONTH(WorkDate)mon FROM [dbo].[Exception_Record] a left join GL_Station b on a.SubOrigin_UID=b.StationID left join GL_Line c on a.Origin_UID = c.LineID left join Exception_Code d on a.Exception_Code_UID = d.Exception_Code_UID left join Exception_Dept e on d.Exception_Dept_UID = e.Exception_Dept_UID where 1=1 ", searchModel.EndTime.ToString("yyyy-MM-dd")); } #endregion #region Week SQL if (searchModel.ReportType == "Week") { sql += string.Format(@"Declare @mindate date Declare @maxdate date set @mindate = DATEADD(day, -84, '{0}') set @maxdate = '{0}' ;with temptab(date) as ( select @mindate union all select dateadd(week,1,temptab.date) as date from temptab where dateadd(week,1,temptab.date)<=@maxdate ) select f.WorkDate,CONVERT(varchar(10), f.week_num)DTMon,isnull(dt2.DTTime,0)DTTime,isnull(dt2.DTName,'')DTName from (select Exception_Dept_Name as DTName,week_num,sum(downtime) as DTTime from ( SELECT [Exception_Record_UID] ,[Origin_UID] ,c.LineName ,[SubOrigin_UID] ,b.StationName ,a.[Exception_Code_UID] ,d.Exception_Dept_UID ,e.Exception_Dept_Name ,e.Plant_Organization_UID ,e.BG_Organization_UID ,e.Funplant_Organization_UID ,[Project_UID] ,[WorkDate] ,[ShiftTimeID] ,[Status] ,[End_Date] ,[Note] ,a.[Created_UID] ,a.[Created_Date] ,a.[Modified_UID] ,a.[Modified_Date] ,[Contact_Person] ,[Contact_Phone] ,case when End_Date is null then DATEDIFF(MINUTE, a.Created_Date,GETDATE() ) else DATEDIFF(Hour, a.Created_Date,End_Date ) end as downtime ,DATEPART(WEEK,WorkDate) AS week_num FROM [dbo].[Exception_Record] a left join GL_Station b on a.SubOrigin_UID=b.StationID left join GL_Line c on a.Origin_UID = c.LineID left join Exception_Code d on a.Exception_Code_UID = d.Exception_Code_UID left join Exception_Dept e on d.Exception_Dept_UID = e.Exception_Dept_UID where 1=1 ", searchModel.EndTime.ToString("yyyy-MM-dd")); } #endregion if (searchModel.ReportType == "Daily") { sql += string.Format(@" and a.WorkDate >= DATEADD(day, -13, '{0}') and a.WorkDate <= '{0}' ", searchModel.EndTime.ToString("yyyy-MM-dd")); } if (searchModel.ReportType == "Month") { sql += string.Format(@" and a.WorkDate >= dateadd(dd,-datepart(dd, DATEADD(day, -365, '{0}'))+1,DATEADD(day, -365, '{0}')) and a.WorkDate <= dateadd(dd,-datepart(dd,'{0}') ,dateadd(mm,1,'{0}'))", searchModel.EndTime.ToString("yyyy-MM-dd")); } if (searchModel.ReportType == "Week") { sql += string.Format(" and a.WorkDate >= dateadd(wk, datediff(wk,0,DATEADD(day, -84, '{0}') ), 0) and a.WorkDate<=dateadd(wk, datediff(wk,0,'{0}'), 6)", searchModel.EndTime.ToString("yyyy-MM-dd")); } if (searchModel.Plant_Organization_UID != 0) { sql += string.Format("and e.Plant_Organization_UID={0}", searchModel.Plant_Organization_UID); } if (searchModel.BG_Organization_UID != 0) { sql += string.Format("and e.BG_Organization_UID={0}", searchModel.BG_Organization_UID); } if (searchModel.LineID != 0) { sql += string.Format("and Origin_UID={0}", searchModel.LineID); } if (searchModel.StationID != 0) { sql += string.Format("and SubOrigin_UID={0}", searchModel.StationID); } if (searchModel.CustomerID != 0) { sql += string.Format("and Project_UID={0}", searchModel.CustomerID); } //全天 if (searchModel.ShiftTimeID != -1) { sql += string.Format("and ShiftTimeID={0}", searchModel.ShiftTimeID); } if (searchModel.ReportType == "Daily") { sql += @")dt group by Exception_Dept_Name,WorkDate)dt2 right join (select date as WorkDate,0 dt_one from temptab)f on f.WorkDate=dt2.WorkDate order by f.WorkDate"; } if (searchModel.ReportType == "Month") { sql += @")dt group by Exception_Dept_Name,mon)dt2 right join (select date as WorkDate,month(date) as mon,0 dt_one from temptab)f on f.mon=dt2.mon order by f.WorkDate"; } if (searchModel.ReportType == "Week") { sql += @")dt group by Exception_Dept_Name,week_num)dt2 right join (select date as WorkDate,DATEPART(WEEK,date) as week_num,0 dt_one from temptab)f on f.week_num=dt2.week_num order by f.WorkDate"; } var dblist = DataContext.Database.SqlQuery <GLFourQDTModel>(sql).ToList(); return(dblist); }
public List <GLFourQDTModel> GetFourQDTTypeDetail(GLFourQParamModel searchModel) { string sql = ""; sql = string.Format(@"select Exception_Dept_Name as DTName,sum(downtime) as DTTime from ( SELECT [Exception_Record_UID] ,[Origin_UID] ,c.LineName ,[SubOrigin_UID] ,b.StationName ,a.[Exception_Code_UID] ,d.Exception_Dept_UID ,e.Exception_Dept_Name ,e.Plant_Organization_UID ,e.BG_Organization_UID ,e.Funplant_Organization_UID ,[Project_UID] ,[WorkDate] ,[ShiftTimeID] ,[Status] ,[End_Date] ,[Note] ,a.[Created_UID] ,a.[Created_Date] ,a.[Modified_UID] ,a.[Modified_Date] ,[Contact_Person] ,[Contact_Phone] ,case when End_Date is null then DATEDIFF(MINUTE, a.Created_Date,GETDATE() ) else DATEDIFF(Hour, a.Created_Date,End_Date ) end as downtime FROM [dbo].[Exception_Record] a left join GL_Station b on a.SubOrigin_UID=b.StationID left join GL_Line c on a.Origin_UID = c.LineID left join Exception_Code d on a.Exception_Code_UID = d.Exception_Code_UID left join Exception_Dept e on d.Exception_Dept_UID = e.Exception_Dept_UID where 1=1 "); if (searchModel.ReportType == "Daily") { sql += string.Format(@"and a.WorkDate = '{0}'", searchModel.Param_Name); } if (searchModel.ReportType == "Month") { sql += string.Format(@"and a.WorkDate >= dateadd(dd,-datepart(dd,'{0}')+1,'{0}') and a.WorkDate <= dateadd(dd,-datepart(dd,'{0}') ,dateadd(mm,1,'{0}'))", searchModel.Param_Name); } if (searchModel.ReportType == "Week") { sql += string.Format(@"and a.WorkDate >= dateadd(wk, datediff(wk,0,'{0}'), 0) and a.WorkDate <= dateadd(wk, datediff(wk,0,'{0}'), 6)", searchModel.Param_Name); } if (searchModel.Plant_Organization_UID != 0) { sql += string.Format("and e.Plant_Organization_UID={0}", searchModel.Plant_Organization_UID); } if (searchModel.BG_Organization_UID != 0) { sql += string.Format("and e.BG_Organization_UID={0}", searchModel.BG_Organization_UID); } if (searchModel.LineID != 0) { sql += string.Format("and Origin_UID={0}", searchModel.LineID); } if (searchModel.StationID != 0) { sql += string.Format("and SubOrigin_UID={0}", searchModel.StationID); } if (searchModel.CustomerID != 0) { sql += string.Format("and Project_UID={0}", searchModel.CustomerID); } //全天 if (searchModel.ShiftTimeID != -1) { sql += string.Format("and ShiftTimeID={0}", searchModel.ShiftTimeID); } sql += " )dt group by Exception_Dept_Name order by DTTime desc"; var dblist = DataContext.Database.SqlQuery <GLFourQDTModel>(sql).ToList(); return(dblist); }