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