public async Task <TransferLocation_Dto> GetByQrCodeId(object qrCodeId)
{
TransferLocation_Dto model = new TransferLocation_Dto();
// Lấy ra TransactionMain cùng QRCode_ID và Can_Move == "Y" và QRCode_Version mới nhất
var transactionModel = await _repoTransactionMain.FindAll(x => x.QRCode_ID.Trim() == qrCodeId.ToString().Trim() && x.Can_Move == "Y" && (x.Transac_Type.Trim() == "I" || x.Transac_Type.Trim() == "M" || x.Transac_Type.Trim() == "R")).OrderByDescending(x => x.QRCode_Version).FirstOrDefaultAsync();
var qrCodeModel = await _repoQRCodeMain.GetByQRCodeID(qrCodeId);
if (transactionModel != null)
{
var packingListModel = await _repoPackingList.FindAll().Where(x => x.MO_No.Trim() == transactionModel.MO_No.Trim()).FirstOrDefaultAsync();
var materialPurchaseModel = await _repoMaterialView.FindAll().Where(x => x.Plan_No.Trim() == transactionModel.MO_No.Trim() && x.Purchase_No.Trim() == transactionModel.Purchase_No.Trim() && x.Mat_.Trim() == transactionModel.Material_ID.Trim()).FirstOrDefaultAsync();
model.Id = transactionModel.ID;
model.QrCodeId = transactionModel.QRCode_ID.Trim();
model.TransferNo = "TB" + DateTime.Now.ToString("yyyyMMdd") + "001";
model.PlanNo = transactionModel.MO_No.Trim();
model.ReceiveNo = qrCodeModel.Receive_No.Trim();
model.Batch = transactionModel.MO_Seq;
model.MatId = transactionModel.Material_ID.Trim();
model.MatName = transactionModel.Material_Name.Trim();
model.FromLocation = transactionModel.Rack_Location.Trim();
model.Qty = _repoTransactionDetail.GetQtyByTransacNo(transactionModel.Transac_No);
model.UpdateBy = "Emma";
model.TransacTime = DateTime.Now;
model.ModelName = packingListModel.Model_Name;
model.ModelNo = packingListModel.Model_No;
model.Article = packingListModel.Article;
model.CustmoerPart = materialPurchaseModel.Custmoer_Part;
model.CustmoerName = materialPurchaseModel.Custmoer_Name;
}
return(model);
}
public async Task<MissingPrint_Dto> GetMaterialPrint(string missingNo)
{
var materialMissingModel = await _repoMaterialMissing.FindAll(x => x.Missing_No.Trim() == missingNo.Trim()).ProjectTo<Material_Dto>(_configMapper).FirstOrDefaultAsync();
var transactionMainModel = _repoTransactionMain.FindSingle(x => x.Missing_No.Trim() == missingNo.Trim() && x.Transac_Type.Trim() == "I");
var transactionDetailByMissingNo = await _repoTransactionDetail.FindAll(x => x.Transac_No.Trim() == transactionMainModel.Transac_No.Trim()).ProjectTo<TransferLocationDetail_Dto>(_configMapper).OrderBy(x => x.Tool_Size).ToListAsync();
var materialPurchaseModel = await _repoMaterialView
.FindAll( x => x.Plan_No.Trim() == materialMissingModel.MO_No.Trim() &&
x.Purchase_No.Trim() == materialMissingModel.Purchase_No.Trim() &&
x.Mat_.Trim() == materialMissingModel.Material_ID.Trim())
.FirstOrDefaultAsync();
// nếu materialPurchaseModel rỗng thì Custmoer_Name gán bằng rỗng
materialMissingModel.Custmoer_Name = materialPurchaseModel == null ? "" : materialPurchaseModel.Custmoer_Name;
// Lấy ra những thuộc tính cần in
MissingPrint_Dto result = new MissingPrint_Dto();
result.MaterialMissing = materialMissingModel;
result.TransactionDetailByMissingNo = transactionDetailByMissingNo;
return result;
}
public async Task <List <QRCodeMainViewModel> > GetListQrCodeMainView(List <QrCodeIDVersion> data)
{
var listQrCode = data.Select(x => x.QRCode_ID).ToList();
var listBatch = data.Select(x => x.MO_Seq).ToList();
var packingList = _repoPackingList.FindAll();
var listMono = data.Select(x => x.MO_No.Trim()).ToList();
var listQrCodeMain = _repoQrcode
.FindAll(x => listQrCode.Contains(x.QRCode_ID));
var viewMaterialPurchase = _repoMaterialView.FindAll(x => listBatch.Contains(x.MO_Seq) &&
listMono.Contains(x.Plan_No.Trim()));
var listQrCodeModel = await(from x in listQrCodeMain join y in packingList
on x.Receive_No.Trim() equals y.Receive_No.Trim()
join z in viewMaterialPurchase on
new { Purchase_No = y.Purchase_No.Trim(), PlanNo = y.MO_No.Trim() } equals new
{ Purchase_No = z.Purchase_No.Trim(), PlanNo = z.Plan_No.Trim() }
select new QRCodeMainViewModel()
{
QRCode_ID = x.QRCode_ID,
MO_No = y.MO_No,
Receive_No = x.Receive_No,
Receive_Date = y.Receive_Date,
Supplier_ID = y.Supplier_ID,
Supplier_Name = y.Supplier_Name,
T3_Supplier = y.T3_Supplier,
T3_Supplier_Name = y.T3_Supplier_Name,
Subcon_ID = y.Subcon_ID,
Subcon_Name = y.Subcon_Name,
Model_Name = y.Model_Name,
Model_No = y.Model_No,
Article = y.Article,
MO_Seq = z.MO_Seq,
Material_ID = y.Material_ID,
Material_Name = y.Material_Name,
Stockfiting_Date = z.Stockfiting_Date,
Assembly_Date = z.Assembly_Date,
CRD = z.CRD,
Line_ASY = z.Line_ASY,
Custmoer_Part = z.Custmoer_Part,
Custmoer_Name = z.Custmoer_Name
}).ToListAsync();
return(listQrCodeModel);
}
public async Task <QRCodeMainViewModel> GetQrCodeInfor(string qrCodeId, string moSeq)
{
var packingListQuery = _repoPackingList.FindAll();
var qrCodeMainQuery = _repoQRCodeMain.FindAll();
var viewMaterialPurchaseQuery = _repoMaterialView.FindAll();
var qrCodeModel = await(from x in qrCodeMainQuery
join y in packingListQuery
on x.Receive_No.Trim() equals y.Receive_No.Trim()
join z in viewMaterialPurchaseQuery on
new { Purchase_No = y.Purchase_No.Trim(), PlanNo = y.MO_No.Trim() } equals new
{ Purchase_No = z.Purchase_No.Trim(), PlanNo = z.Plan_No.Trim() }
select new QRCodeMainViewModel()
{
QRCode_ID = x.QRCode_ID,
MO_No = y.MO_No,
Receive_No = x.Receive_No,
Receive_Date = y.Receive_Date,
Supplier_ID = y.Supplier_ID,
Supplier_Name = y.Supplier_Name,
T3_Supplier = y.T3_Supplier,
T3_Supplier_Name = y.T3_Supplier_Name,
Subcon_ID = y.Subcon_ID,
Subcon_Name = y.Subcon_Name,
Model_Name = y.Model_Name,
Model_No = y.Model_No,
Article = y.Article,
MO_Seq = y.MO_Seq,
Material_ID = y.Material_ID,
Material_Name = y.Material_Name,
Stockfiting_Date = z.Stockfiting_Date,
Assembly_Date = z.Assembly_Date,
CRD = z.CRD,
Line_ASY = z.Line_ASY,
Custmoer_Part = z.Custmoer_Part,
Custmoer_Name = z.Custmoer_Name
}).Where(x => x.QRCode_ID.Trim() == qrCodeId.Trim() && x.MO_Seq == moSeq).FirstOrDefaultAsync();
return(qrCodeModel);
}
public async Task <PagedList <ReceivingMaterialMainModel> > SearchByModel(PaginationParams param, FilterReceivingMateParam filterParam)
{
var pred_Material_Purchase = PredicateBuilder.New <WMSB_Material_Purchase>(true);
var pred_VM_Material_Purchase = PredicateBuilder.New <VM_WMSB_Material_Purchase>(true);
// Ở kho có Type = 2
pred_Material_Purchase.And(x => x.Type.Trim() == "2" && 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"));
}
if (!String.IsNullOrEmpty(filterParam.MO_No))
{
pred_Material_Purchase.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 (!String.IsNullOrEmpty(filterParam.Purchase_No))
{
pred_Material_Purchase.And(x => x.Purchase_No.Trim() == filterParam.Purchase_No.Trim());
}
if (filterParam.Status != "all")
{
pred_Material_Purchase.And(x => x.Status.Trim() == filterParam.Status.Trim());
}
var materialPurchases = await _repoPurchase.FindAll(pred_Material_Purchase).ToListAsync();
var materialPurchaseList = materialPurchases
.Select(x => new PurchaseConvert()
{
Purchase_No = x.Purchase_No.Trim(), Status = x.Status, MO_No = x.MO_No, Material_ID = x.Material_ID
}).GroupBy(x => new { x.Purchase_No, x.Status, x.MO_No, x.Material_ID }).Select(x => x.FirstOrDefault()).ToList();
var purchaseContainQty = materialPurchases.Select(x => new {
Purchase_No = x.Purchase_No.Trim(), MO_No = x.MO_No.Trim(), Purchase_Qty = x.Purchase_Qty, Material_ID = x.Material_ID.Trim()
}).GroupBy(x => new { x.Purchase_No, x.MO_No, x.Material_ID }).Select(x => new {
Purchase_No = x.FirstOrDefault().Purchase_No,
MO_No = x.FirstOrDefault().MO_No,
Material_ID = x.FirstOrDefault().Material_ID,
Qty = x.Sum(cl => cl.Purchase_Qty)
}).ToList();
// 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.MO_No, x.Material_ID }).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 listMaterialView = await _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.Purchase_No,
x.Supplier_No, x.Supplier_Name, x.Subcon_No, x.Subcon_Name, x.T3_Supplier, x.T3_Supplier_Name }).Distinct().ToListAsync();
listMaterialView = listMaterialView.Distinct().ToList();
var result = (from a in listData
join c in purchaseContainQty
on new { Purchase_No = a.Purchase_No.Trim(), MO_No = a.MO_No.Trim(), MaterialID = a.Material_ID.Trim() }
equals new { Purchase_No = c.Purchase_No.Trim(), MO_No = c.MO_No.Trim(), MaterialID = c.Material_ID.Trim() }
join b in listMaterialView
on new { Purchase_No = a.Purchase_No.Trim(), MO_No = a.MO_No.Trim(), MaterialID = a.Material_ID.Trim() }
equals new { Purchase_No = b.Purchase_No, MO_No = b.Plan_No.Trim(), MaterialID = b.Mat_.Trim() }
select new ReceivingMaterialMainModel {
Status = a.Status,
Material_ID = b.Mat_,
Material_Name = b.Mat__Name,
MO_No = b.Plan_No,
Purchase_No = a.Purchase_No,
Model_No = b.Model_No,
Model_Name = b.Model_Name,
Article = b.Article,
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,
Qty = c.Qty
}).ToList();
result = result.OrderBy(x => x.Status).ThenBy(x => x.MO_No).ThenBy(x => x.Purchase_No).ToList();
return(PagedList <ReceivingMaterialMainModel> .Create(result, param.PageNumber, param.PageSize, false));
}
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 <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));
}