public async Task <List <string> > GetAuditType1BySixs()
{
// 6S là giá trị fix cứng
var auditType1 = await _auditTypeMRepository.FindAll(x => x.Audit_Kind.Trim() == "6S").Select(x => x.Audit_Type1).Distinct().ToListAsync();
return(auditType1);
}
public async Task <List <string> > GetAuditType1BySME()
{
//SME la gia tri fix cung
var auditType1 = await _auditTypeMRepository.FindAll(x => x.Audit_Kind.Trim() == "SME").Select(x => x.Audit_Type1).Distinct().ToListAsync();
return(auditType1);
}
public async Task <List <ScoreRecordQuesDto> > GetListQuesScoreRecord(string auditType2, string auditType1)
{
MES_Audit_Type_M auditTypeID;
if (auditType1 == "精實系統/WS")
{
auditTypeID = _auditTypeMRepository.FindAll().Where(x => x.Audit_Type1 == auditType1).FirstOrDefault();
}
else
{
auditTypeID = _auditTypeMRepository.FindAll().Where(x => x.Audit_Type1 == auditType1 && x.Audit_Type2 == auditType2).FirstOrDefault();
}
List <ScoreRecordQuesDto> data = new List <ScoreRecordQuesDto>();
if (auditTypeID != null)
{
var queryAudiiTypeD = _auditTypeDRepository.FindAll().Where(x => x.Audit_Type_ID.Trim() == auditTypeID.Audit_Type_ID.Trim());
data = await queryAudiiTypeD.Select(x => new ScoreRecordQuesDto
{
Audit_Type_ID = x.Audit_Type_ID,
Audit_Item_ID = x.Audit_Item_ID,
Quesion = x.Audit_Item_LL,
QuesionLL = x.Audit_Item_LL,
QuesionEN = x.Audit_Item_EN,
QuesionZW = x.Audit_Item_ZW
}).ToListAsync();
}
return(data);
}
public async Task <List <int> > GetVersion(string audittypeID)
{
var brand = _repoAuditMType.FindAll().Where(x => x.Audit_Type_ID == audittypeID).FirstOrDefault();
var data = await _repoAuditMType.FindAll().Where(x => x.Brand == brand.Brand &&
x.Audit_Type1 == brand.Audit_Type1 && x.Audit_Type2 == brand.Audit_Type2).
GroupBy(x => x.Version).Select(y => y.Key).ToListAsync();
return(data);
}
public async Task <PagedList <SixsScoreRecordDto> > GetListSixsScoreRecord(PaginationParams paginationParams, ScoreRecordParam scoreRecordParam, bool isPaging = true)
{
// 6S là giá trị fix cứng
var paramAuditTypeBySixs = await _auditTypeMRepository.FindAll(x => x.Audit_Kind.Trim() == "6S").Select(x => x.Audit_Type_ID).ToListAsync();
var queryAuditRateM = _auditRateMRepository.FindAll(x => paramAuditTypeBySixs.Contains(x.Audit_Type_ID));
var queryAuditRateD = _auditRateDRepository.FindAll();
var listAuditMes = _mesAuditOrg.FindAll(x => x.Status == 1);
if (scoreRecordParam.PDC != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.PDC.Trim() == scoreRecordParam.PDC);
}
if (scoreRecordParam.Building != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Building.Trim() == scoreRecordParam.Building);
}
if (scoreRecordParam.Line != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Line.Trim() == scoreRecordParam.Line);
}
if (scoreRecordParam.AuditType1 != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Audit_Type1.Trim() == scoreRecordParam.AuditType1);
}
if (scoreRecordParam.AuditType2 != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Audit_Type2.Trim() == scoreRecordParam.AuditType2);
}
if (scoreRecordParam.FromDate != "" && scoreRecordParam.ToDate != "")
{
DateTime d1 = Convert.ToDateTime(scoreRecordParam.FromDate + " 00:00:00");
DateTime d2 = Convert.ToDateTime(scoreRecordParam.ToDate + " 23:59:59");
queryAuditRateM = queryAuditRateM.Where(x => x.Record_Date >= d1 && x.Record_Date <= d2);
}
var data = queryAuditRateM.Join(listAuditMes, x => x.Line, t => t.Line_ID_2, (x, t)
=> new SixsScoreRecordDto
{
RecordId = x.Record_ID,
AuditDate = x.Record_Date,
AuditType = x.Audit_Type1,
AuditType2 = x.Audit_Type2,
LineId = x.Line,
Line_Name = t.Line_ID_2_Name,
UpdateBy = x.Updated_By,
UpdateTime = x.Updated_Time,
Rating0 = queryAuditRateD.Where(y => y.Record_ID == x.Record_ID).Sum(z => z.Rating_0),
Rating1 = queryAuditRateD.Where(y => y.Record_ID == x.Record_ID).Sum(z => z.Rating_1),
RatingNa = queryAuditRateD.Where(y => y.Record_ID == x.Record_ID).Sum(z => z.Rate_NA) == null ? 0 : queryAuditRateD.Where(y => y.Record_ID == x.Record_ID).Sum(z => z.Rate_NA),
CheckAnswerAllYet = queryAuditRateD.Where(y => y.Record_ID == x.Record_ID && y.Rate_NA == 0 && y.Rating_0 == 0 && y.Rating_1 == 0 && y.Rating_2 == 0).Count() > 0 ? false : true
}).Distinct().OrderByDescending(x => x.UpdateTime);
return(await PagedList <SixsScoreRecordDto> .CreateAsync(data, paginationParams.PageNumber, paginationParams.PageSize, isPaging));
}
public async Task <PagedList <WaterSpiderScoreRecordDto> > GetLisWaterSpiderScoreRecord(PaginationParams paginationParams, ScoreRecordParam scoreRecordParam, bool isPaging = true)
{
// WS là giá trị fix cứng
var paramAuditTypeIdByWaterSpider = await _auditTypeMRepository.FindAll(x => x.Audit_Kind.Trim() == "WS").Select(x => x.Audit_Type_ID).ToListAsync();
var queryAuditRateM = _auditRateMRepository.FindAll().Where(x => paramAuditTypeIdByWaterSpider.Contains(x.Audit_Type_ID));
var queryAuditRateD = _auditRateDRepository.FindAll();
var listAuditMes = _mesAuditOrg.FindAll(x => x.Status == 1);
if (scoreRecordParam.PDC != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.PDC.Trim() == scoreRecordParam.PDC);
}
if (scoreRecordParam.Building != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Building.Trim() == scoreRecordParam.Building);
}
if (scoreRecordParam.Line != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Line.Trim() == scoreRecordParam.Line);
}
if (scoreRecordParam.AuditType1 != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Audit_Type1.Trim() == scoreRecordParam.AuditType1);
}
if (scoreRecordParam.AuditType2 != "")
{
queryAuditRateM = queryAuditRateM.Where(x => x.Audit_Type2.Trim() == scoreRecordParam.AuditType2);
}
if (scoreRecordParam.FromDate != "" && scoreRecordParam.ToDate != "")
{
DateTime d1 = Convert.ToDateTime(scoreRecordParam.FromDate + " 00:00:00");
DateTime d2 = Convert.ToDateTime(scoreRecordParam.ToDate + " 23:59:59");
queryAuditRateM = queryAuditRateM.Where(x => x.Record_Date >= d1 && x.Record_Date <= d2);
}
var data = queryAuditRateM.Join(listAuditMes, x => x.Line, t => t.Line_ID_2, (x, t) =>
new WaterSpiderScoreRecordDto
{
RecordId = x.Record_ID,
AuditType = x.Audit_Type1,
AuditDate = x.Record_Date,
LineId = x.Line,
Line_Name = t.Line_ID_2_Name,
Score = _auditRateDRepository.SumEachRating1InAuditTypeDAndAuditRateD(x.Record_ID),
Loss = _auditRateDRepository.SumEachRating0InAuditTypeDAndAuditRateD(x.Record_ID),
NA = _auditRateDRepository.SumEachRatingNAInAuditTypeDAndAuditRateD(x.Record_ID),
CheckAnswerAllYet = queryAuditRateD.Where(y => y.Record_ID == x.Record_ID && y.Rate_NA == 0 && y.Rating_0 == 0 && y.Rating_1 == 0 && y.Rating_2 == 0).Count() > 0 ? false : true
});
var result = data.Distinct().ToList();
result = result.OrderByDescending(x => x.AuditDate).ToList();
return(PagedList <WaterSpiderScoreRecordDto> .Create(result, paginationParams.PageNumber, paginationParams.PageSize, isPaging));
}
public async Task <List <ScoreRecordQuesDto> > GetListQuesScoreRecord(string auditTypeId)
{
MES_Audit_Type_M auditType;
auditType = _repoTypeM.FindAll().Where(x => x.Audit_Type_ID.Trim() == auditTypeId.Trim()).FirstOrDefault();
List <ScoreRecordQuesDto> data = new List <ScoreRecordQuesDto>();
if (auditType != null)
{
var queryAudiiTypeD = _repoTypeD.FindAll().Where(x => x.Audit_Type_ID.Trim() == auditType.Audit_Type_ID.Trim() && x.Visible == true);
data = await queryAudiiTypeD.Select(x => new ScoreRecordQuesDto
{
Audit_Type_ID = x.Audit_Type_ID,
Audit_Item_ID = x.Audit_Item_ID,
Quesion = x.Audit_Item_LL,
QuesionLL = x.Audit_Item_LL,
QuesionEN = x.Audit_Item_EN,
QuesionZW = x.Audit_Item_ZW,
TypeDrating0 = x.Rating_0,
TypeDrating1 = x.Rating_1,
TypeDrating2 = x.Rating_2,
orderby = 0
}).OrderBy(x => x.Audit_Item_ID).ToListAsync();
data = data.Select(x =>
{
x.orderby = (x.Audit_Item_ID.Split(".")).Count() == 1 ? 0 : x.Audit_Item_ID.Split(".")[1].ToInt();
return(x);
}).ToList();
data = data.OrderBy(x => x.Audit_Item_ID.Split(".")[0].ToInt()).ThenBy(x => x.orderby).ToList();
}
return(data);
}
public async Task <PagedList <AuditType_D_Dto> > SearchByAuditType(PaginationParams param, string audit_Type1, string audit_Type2)
{
MES_Audit_Type_M auditTypeID = null;
var lists = _repoAuditDType.FindAll().ProjectTo <AuditType_D_Dto>(_configMapper);
if (audit_Type1 != "all")
{
if (audit_Type2 != null)
{
auditTypeID = await _repoAuditMType.FindAll().Where(x => x.Audit_Type1.Trim() == audit_Type1.Trim() && x.Audit_Type2.Trim() == audit_Type2).FirstOrDefaultAsync();
}
else
{
auditTypeID = await _repoAuditMType.FindAll().Where(x => x.Audit_Type1.Trim() == audit_Type1.Trim()).FirstOrDefaultAsync();
}
lists = _repoAuditDType.FindAll().ProjectTo <AuditType_D_Dto>(_configMapper).Where(x => x.Audit_Type_ID.Trim() == auditTypeID.Audit_Type_ID.Trim()).OrderByDescending(x => x.Updated_Time);
}
return(await PagedList <AuditType_D_Dto> .CreateAsync(lists, param.PageNumber, param.PageSize));
}
public async Task <object> getAllAuditTypeM()
{
var result = await _repoauditType.FindAll().ToListAsync();
var data = from a in result
select new
{
audit_type_ID = result.Where(y => y.Audit_Kind == a.Audit_Kind && y.Audit_Type1 == a.Audit_Type1 &&
y.Audit_Type2 == a.Audit_Type2 && y.Brand == a.Brand &&
y.Version == result.Where(x => x.Audit_Kind == a.Audit_Kind &&
x.Audit_Type1 == a.Audit_Type1 &&
x.Audit_Type2 == a.Audit_Type2 &&
x.Brand == a.Brand).OrderByDescending(x => x.Version).FirstOrDefault().Version)
.OrderByDescending(x => x.Audit_Type_ID).FirstOrDefault().Audit_Type_ID,
Audit_Kind = a.Audit_Kind == null ? "" : a.Audit_Kind,
Audit_Type1 = a.Audit_Type1 == null ? "" : a.Audit_Type1,
Audit_Type2 = a.Audit_Type2 == null ? "" : a.Audit_Type2,
Version = result.Where(x => x.Audit_Kind == a.Audit_Kind && x.Audit_Type1 == a.Audit_Type1 &&
x.Audit_Type2 == a.Audit_Type2 && x.Brand == a.Brand).OrderByDescending(x => x.Version).FirstOrDefault().Version
};
return(data.Distinct());
}
public int SumEachRating1InAuditTypeDAndAuditRateD(string recordId)
{
var auditTypeM = _auditTypeMRepository.FindAll().Where(x => x.Audit_Type1.Trim() == "精實系統/WS").FirstOrDefault();
var listAuditTypeD = _auditTypeDRepository.FindAll().Where(x => x.Audit_Type_ID.Trim() == auditTypeM.Audit_Type_ID.Trim()).ToList();
var listAuditRateD = _auditRateDRepository.FindAll().Where(x => x.Record_ID == recordId).ToList();
// biến lưu tổng của tích từng phần tử
int result = 0;
foreach (var i in listAuditTypeD)
{
foreach (var j in listAuditRateD)
{
if (i.Audit_Item_ID == j.Audit_Item_ID)
{
result += i.Rating_1 * j.Rating_1;
}
}
}
return(result);
}
public async Task <PagedList <AuditRecDto> > SearchByModel(PaginationParams param, AuditRecSearch model)
{
var listAuditRecM = _repoAuditRecM.FindAll();
var listAuditRecD = _repoAuditRecD.FindAll();
var listAuditRecDto = listAuditRecD.Join(listAuditRecM, x => x.Record_ID, y => y.Record_ID, (x, y) => new AuditRecDto
{
Record_ID = x.Record_ID,
Record_Time = y.Record_Time,
After_Picture = x.After_Picture,
Audit_Item = x.Audit_Item,
Audit_Type_ID = x.Audit_Type_ID,
Audit_Type = _repoAuditTypeM.FindById(x.Audit_Type_ID).Audit_Type1 + "-" + _repoAuditTypeM.FindById(x.Audit_Type_ID).Audit_Type2,
Before_Picture = x.Before_Picture,
Finished_Date = x.Finished_Date,
ERCS = x.ERCS,
Implement_Time = x.Implement_Time,
Implement_User = x.Implement_User,
Issue_EN = x.Issue_EN,
Issue_LL = x.Issue_LL,
Issue_ZW = x.Issue_ZW,
PDC = y.PDC,
Line = y.Line,
Building = y.Building,
ME_PIC = x.ME_PIC,
Model_Name = y.Model_Name,
Model_No = y.Model_No,
Chief = y.Chief,
Recorder = y.Recorder,
Attendees = y.Attendees,
PD_PIC = x.PD_PIC,
PD_RESP = x.PD_RESP,
Remark = x.Remark,
Status = x.Status,
Item_no = x.Item_no,
Updated_By = x.Updated_By,
Updated_Time = x.Updated_Time
});
listAuditRecDto = listAuditRecDto.Where(x => x.Status.Trim() == model.Status.Trim() &&
x.Building.Trim() == model.Building.Trim() &&
x.Line.Trim() == model.Line.Trim() &&
x.PDC.Trim() == model.PDC.Trim() &&
x.Record_Time >= Convert.ToDateTime(model.From_Date + " 00:00") &&
x.Record_Time <= Convert.ToDateTime(model.To_Date + " 00:00"));
if (model.Model_No != "all")
{
listAuditRecDto = listAuditRecDto.Where(x => x.Model_No.Trim() == model.Model_No.Trim());
}
if (model.Model_Name != "" && model.Model_Name != string.Empty && model.Model_Name != null)
{
listAuditRecDto = listAuditRecDto.Where(x => x.Model_Name.Contains(model.Model_Name));
}
if (model.Audit_Type_1 != "all")
{
var auditTypeMFind = await _repoAuditTypeM.FindAll().Where(x => x.Audit_Type1.Trim() == model.Audit_Type_1 &&
x.Audit_Type2.Trim() == model.Audit_Type_2).FirstOrDefaultAsync();
listAuditRecDto = listAuditRecDto.Where(x => x.Audit_Type_ID.Trim() == auditTypeMFind.Audit_Type_ID);
}
return(await PagedList <AuditRecDto> .CreateAsync(listAuditRecDto, param.PageNumber, param.PageSize));
}
public async Task <PagedList <AuditRecDto> > SearchByModel(PaginationParams param, AuditRecSearch model)
{
var listAuditRecM = _repoAuditRecM.FindAll();
var listAuditRecD = _repoAuditRecD.FindAll();
var listAuditMes = _mesAuditOrg.FindAll().Where(x => x.Status == 1);
var listAuditRecDto = listAuditRecD.Join(listAuditRecM, x => x.Record_ID, y => y.Record_ID, (x, y) => new { x, y }).Join(listAuditMes, z => z.y.Line, t => t.Line_ID_2, (z, t) => new AuditRecDto
{
Record_ID = z.x.Record_ID,
Record_Time = z.y.Record_Time,
After_Picture = z.x.After_Picture,
Audit_Item = z.x.Audit_Item,
Audit_Type_ID = z.x.Audit_Type_ID,
Audit_Type = z.x.Audit_Type_ID == null || z.x.Audit_Type_ID == "" ? "" : _repoAuditTypeM.FindById(z.x.Audit_Type_ID).Audit_Type1 + "-" + _repoAuditTypeM.FindById(z.x.Audit_Type_ID).Audit_Type2,
Before_Picture = z.x.Before_Picture,
Finished_Date = z.x.Finished_Date,
ERCS = z.x.ERCS,
Implement_Time = z.x.Implement_Time,
Implement_User = z.x.Implement_User,
Issue_EN = z.x.Issue_EN,
Issue_LL = z.x.Issue_LL,
Issue_ZW = z.x.Issue_ZW,
Building = z.y.Building,
PDC = z.y.PDC,
Line = z.y.Line,
Line_Name = t.Line_ID_2_Name,
ME_PIC = z.x.ME_PIC,
Model_Name = z.y.Model_Name,
Model_No = z.y.Model_No,
Chief = z.y.Chief,
Recorder = z.y.Recorder,
Attendees = z.y.Attendees,
PD_PIC = z.x.PD_PIC,
PD_Department = z.x.PD_Department,
PD_Building = z.x.PD_Building,
Remark = z.x.Remark,
Status = z.x.Status,
Item_no = z.x.Item_no,
Updated_By = z.x.Updated_By,
Updated_Time = z.x.Updated_Time
}).ToList();
if (model.Status != "")
{
listAuditRecDto = listAuditRecDto.Where(x => x.Status.Trim() == model.Status.Trim()).ToList();
}
if (model.Building != "")
{
listAuditRecDto = listAuditRecDto.Where(x => x.Building.Trim() == model.Building.Trim()).ToList();
}
if (model.Line != "")
{
listAuditRecDto = listAuditRecDto.Where(x => x.Line.Trim() == model.Line.Trim()).ToList();
}
if (model.PDC != "")
{
listAuditRecDto = listAuditRecDto.Where(x => x.PDC.Trim() == model.PDC.Trim()).ToList();
}
if (model.Model_No != "")
{
listAuditRecDto = listAuditRecDto.Where(x => x.Model_No.Trim() == model.Model_No.Trim()).ToList();
}
if (model.Model_Name != "")
{
listAuditRecDto = listAuditRecDto.Where(x => x.Model_Name.Trim() == model.Model_Name.Trim()).ToList();
}
if (model.Audit_Type_1 != "")
{
var auditTypeMFind = await _repoAuditTypeM.FindAll(x => x.Audit_Type1.Trim() == model.Audit_Type_1.Trim() && x.Audit_Type2.Trim() == model.Audit_Type_2.Trim()).FirstOrDefaultAsync();
listAuditRecDto = listAuditRecDto.Where(x => x.Audit_Type_ID.Trim() == auditTypeMFind.Audit_Type_ID).ToList();
}
listAuditRecDto = listAuditRecDto.Select(
x =>
{
x.ME_PIC_Name = GetMePicByID(x.ME_PIC);
x.PD_PIC = GetPdPicByID(x.PD_PIC);
return(x);
}).OrderByDescending(x => x.Record_Time).ThenBy(x => x.Item_no).ToList();
return(PagedList <AuditRecDto> .Create(listAuditRecDto, param.PageNumber, param.PageSize));
}
public async Task <PagedList <AuditTypeDto> > GetWithPaginations(PaginationParams param)
{
var lists = _repoAuditType.FindAll().ProjectTo <AuditTypeDto>(_configMapper).OrderByDescending(x => x.Updated_Time);
return(await PagedList <AuditTypeDto> .CreateAsync(lists, param.PageNumber, param.PageSize));
}
