public async Task <PagedList <MaterialMainViewModel> > SearchByModel(PaginationParams param, FilterMaterialParam filterParam)
{
var listMaterialView = await _repoMaterialView.FindAll().ToListAsync();
var materialPurchaseList = await _repoPurchase.FindAll().
Select(x => new {
Purchase_No = x.Purchase_No,
Status = x.Status,
Missing_No = "",
Confirm_Delivery = x.Confirm_Delivery
}).Distinct().ToListAsync();
var materialMissingList = await _repoMissing.GetAll()
.Select(x => new {
Purchase_No = x.Purchase_No,
Status = x.Status,
Missing_No = x.Missing_No,
Confirm_Delivery = x.Confirm_Delivery
}).Distinct().ToListAsync();
foreach (var item in materialMissingList)
{
materialPurchaseList.Add(item);
}
// Nếu purchase đó có 1 batch là N thì status show ra sẽ là N. Còn Y hết thì hiển thị Y
// Tạo ra 1 mảng đối tượng mới
var materialPurchaseListConvert = new List <PurchaseConvert>();
foreach (var item in materialPurchaseList)
{
var item1 = new PurchaseConvert {
Purchase_No = item.Purchase_No,
Status = item.Status,
Missing_No = item.Missing_No,
Confirm_Delivery = item.Confirm_Delivery
};
materialPurchaseListConvert.Add(item1);
}
foreach (var item in materialPurchaseListConvert)
{
if (item.Status.Trim() == "N")
{
foreach (var item1 in materialPurchaseListConvert)
{
if (item1.Purchase_No.Trim() == item.Purchase_No.Trim() && item1.Missing_No == item.Missing_No)
{
item1.Status = "N";
}
}
}
}
// Distinct lại mảng.Do ko xài Distinct ở trong câu lệnh 1 list Object được.
var listData = materialPurchaseListConvert.GroupBy(x => new{ x.Purchase_No, x.Missing_No }).Select(y => y.First());
// --------------------------------------------------------------------------
var listMaterial = (from a in listData join b in listMaterialView
on a.Purchase_No.Trim() equals b.Purchase_No.Trim()
select new MaterialMainViewModel {
Status = a.Status,
Material_ID = b.Mat_,
Material_Name = b.Mat__Name,
Missing_No = a.Missing_No == null? "" : a.Missing_No,
MO_No = b.Plan_No,
Confirm_Delivery = a.Confirm_Delivery,
Purchase_No = a.Purchase_No,
Model_No = b.Model_No,
Model_Name = b.Model_Name,
Article = b.Article,
Custmoer_Name = b.Custmoer_Name,
Supplier_ID = b.Supplier_No,
Supplier_Name = b.Supplier_Name,
Subcon_No = b.Subcon_No,
Subcon_Name = b.Subcon_Name,
T3_Supplier = b.T3_Supplier,
T3_Supplier_Name = b.T3_Supplier_Name
}).ToList();
if (filterParam.From_Date != null && filterParam.To_Date != null)
{
listMaterial = listMaterial.Where(x => x.Confirm_Delivery >= Convert.ToDateTime(filterParam.From_Date + " 00:00:00.000") &&
x.Confirm_Delivery <= Convert.ToDateTime(filterParam.To_Date + " 23:59:59.997")).ToList();
}
if (filterParam.MO_No != null && filterParam.MO_No != string.Empty)
{
listMaterial = listMaterial.Where(x => x.MO_No.Trim() == filterParam.MO_No.Trim()).ToList();
}
if (filterParam.Supplier_ID != null && filterParam.Supplier_ID != string.Empty)
{
listMaterial = listMaterial.Where(x => x.Supplier_ID.Trim() == filterParam.Supplier_ID.Trim()).ToList();
}
if (filterParam.Status != "all")
{
listMaterial = listMaterial.Where(x => x.Status.Trim() == filterParam.Status.Trim()).ToList();
}
return(PagedList <MaterialMainViewModel> .Create(listMaterial, param.PageNumber, param.PageSize));
}
public async Task <IActionResult> SearchByModel([FromQuery] PaginationParams param, FilterMaterialParam filterParam)
{
var result = await _service.SearchByModel(param, filterParam);
Response.AddPagination(result.CurrentPage, result.PageSize, result.TotalCount, result.TotalPages);
return(Ok(result));
}
public async Task <PagedList <MaterialMainViewModel> > SearchByModel(PaginationParams param, FilterMaterialParam filterParam)
{
var pred_Material_Purchase = PredicateBuilder.New <WMSB_Material_Purchase>(true);
var pred_Material_Missing = PredicateBuilder.New <WMSB_Material_Missing>(true);
var pred_VM_Material_Purchase = PredicateBuilder.New <VM_WMSB_Material_Purchase>(true);
// Ở kho có Type = 1
pred_Material_Purchase.And(x => x.Type.Trim() == "1" && x.Biz_Tflag != "D");
if (filterParam.From_Date != null && filterParam.To_Date != null)
{
pred_Material_Purchase.And(x => x.Confirm_Delivery >= Convert.ToDateTime(filterParam.From_Date + " 00:00:00.000"));
pred_Material_Purchase.And(x => x.Confirm_Delivery <= Convert.ToDateTime(filterParam.To_Date + " 23:59:59.997"));
pred_Material_Missing.And(x => x.Confirm_Delivery >= Convert.ToDateTime(filterParam.From_Date + " 00:00:00.000"));
pred_Material_Missing.And(x => x.Confirm_Delivery <= Convert.ToDateTime(filterParam.To_Date + " 23:59:59.997"));
}
if (!String.IsNullOrEmpty(filterParam.MO_No))
{
pred_Material_Purchase.And(x => x.MO_No.Trim() == filterParam.MO_No.Trim());
pred_Material_Missing.And(x => x.MO_No.Trim() == filterParam.MO_No.Trim());
pred_VM_Material_Purchase.And(x => x.Plan_No.Trim() == filterParam.MO_No.Trim());
}
if (filterParam.Supplier_ID != null && filterParam.Supplier_ID != "All")
{
pred_Material_Purchase.And(x => x.Supplier_ID.Trim() == filterParam.Supplier_ID.Trim());
pred_Material_Missing.And(x => x.Supplier_ID.Trim() == filterParam.Supplier_ID.Trim());
pred_VM_Material_Purchase.And(x => x.Supplier_No.Trim() == filterParam.Supplier_ID.Trim());
}
if (filterParam.Status != "all")
{
pred_Material_Purchase.And(x => x.Status.Trim() == filterParam.Status.Trim());
pred_Material_Missing.And(x => x.Status.Trim() == filterParam.Status.Trim());
}
var materialPurchaseList = await _repoPurchase.FindAll(pred_Material_Purchase)
.Select(x => new PurchaseConvert()
{
Purchase_No = x.Purchase_No,
Status = x.Status,
Missing_No = "",
}).Distinct().ToListAsync();
var materialMissingList = await _repoMissing.FindAll(pred_Material_Missing)
.Select(x => new PurchaseConvert()
{
Purchase_No = x.Purchase_No,
Status = x.Status,
Missing_No = x.Missing_No,
}).Distinct().ToListAsync();
foreach (var item in materialMissingList)
{
materialPurchaseList.Add(item);
}
// Nếu purchase đó có 1 batch là N thì status show ra sẽ là N. Còn Y hết thì hiển thị Y
foreach (var item in materialPurchaseList)
{
if (item.Status.Trim() == "N")
{
foreach (var item1 in materialPurchaseList)
{
if (item1.Purchase_No.Trim() == item.Purchase_No.Trim() && item1.Missing_No == item.Missing_No)
{
item1.Status = "N";
}
}
}
}
// Distinct lại mảng.Do ko xài Distinct ở trong câu lệnh 1 list Object được.
var listData = materialPurchaseList.GroupBy(x => new { x.Purchase_No, x.Missing_No }).Select(y => y.First());
// ------------------------------------------------------------------------------------------
var listData_Purchase_No = listData.Select(x => x.Purchase_No.Trim()).Distinct().ToList();
pred_VM_Material_Purchase.And(x => listData_Purchase_No.Contains(x.Purchase_No));
// var conn = await _database.CreateConnectionAsync();
// var parameter = new {listData_Purchase_No = listData_Purchase_No};
// var listMaterialView = conn.Query
// ("Select Distinct Mat# as Mat_,Mat#_Name as Mat__Name,Plan_No,Model_No,Model_No,Model_Name,Article,Custmoer_Name, Purchase_No, Supplier_No,Supplier_Name,Subcon_No,Subcon_Name,T3_Supplier,T3_Supplier_Name from VM_WMSB_Material_Purchase where Purchase_No IN @listData_Purchase_No",parameter, commandTimeout: 1000).ToList();
var listMaterialView = _repoMaterialView.FindAll(pred_VM_Material_Purchase)
.Select(x => new {
x.Mat_, x.Mat__Name, x.Plan_No, x.Model_No, x.Model_Name, x.Article, x.Custmoer_Name, x.Purchase_No,
x.Supplier_No, x.Supplier_Name, x.Subcon_No, x.Subcon_Name, x.T3_Supplier, x.T3_Supplier_Name,
}).Distinct();
var listMaterial = (from a in listData
join b in listMaterialView
on a.Purchase_No.Trim() equals b.Purchase_No.Trim()
select new MaterialMainViewModel
{
Status = a.Status,
Material_ID = b.Mat_,
Material_Name = b.Mat__Name,
Missing_No = a.Missing_No == null ? "" : a.Missing_No,
MO_No = b.Plan_No,
Purchase_No = a.Purchase_No,
Model_No = b.Model_No,
Model_Name = b.Model_Name,
Article = b.Article,
Custmoer_Name = b.Custmoer_Name,
Supplier_ID = b.Supplier_No,
Supplier_Name = b.Supplier_Name,
Subcon_No = b.Subcon_No,
Subcon_Name = b.Subcon_Name,
T3_Supplier = b.T3_Supplier,
T3_Supplier_Name = b.T3_Supplier_Name
}).ToList();
listMaterial = listMaterial.OrderBy(x => x.Status).ThenBy(x => x.MO_No).ThenBy(x => x.Purchase_No).ToList();
return(PagedList <MaterialMainViewModel> .Create(listMaterial, param.PageNumber, param.PageSize, false));
}
