private void bwLoadData_DoWork(object sender, DoWorkEventArgs e)
{
materialTypeList = MaterialTypeController.Select().Where(w => w.MaterialGroup.Contains("UPPER")).ToList();
orderList = OrdersController.Select();
rawMaterialList = RawMaterialController.Select();
sewingMasterList = SewingMasterController.SelectByProductNo();
DataTable dt = new DelayDataSet().Tables["DelayTable"];
List <String> productNoList = rawMaterialList.Select(r => r.ProductNo).Distinct().ToList();
var materialUpperIdList = materialTypeList.Select(s => s.MaterialTypeId).Distinct().ToList();
foreach (string productNo in productNoList)
{
OrdersModel order = orderList.Where(o => o.ProductNo == productNo).FirstOrDefault();
List <Int32> materialTypeIdList = rawMaterialList.Where(w => materialUpperIdList.Contains(w.MaterialTypeId)).Select(r => r.MaterialTypeId).Distinct().ToList();
// get assemblyline from productNo
//AssemblyMasterModel assemblyMaster = assemblyMasterList.Where(o => o.ProductNo == productNo).FirstOrDefault();
// get sewingLing from productNo
SewingMasterModel sewingMaster = sewingMasterList.Where(o => o.ProductNo == productNo).FirstOrDefault();
//materialTypeIdList.Remove(6);
//materialTypeIdList.Remove(11);
//materialTypeIdList.Remove(12);
//materialTypeIdList.Remove(13);
//materialTypeIdList.Remove(14);
foreach (int materialTypeId in materialTypeIdList)
{
RawMaterialModel rawMaterial = rawMaterialList.Where(r => r.ProductNo == productNo && r.MaterialTypeId == materialTypeId).FirstOrDefault();
if (rawMaterial != null && rawMaterial.ETD.Date != new DateTime(2000, 1, 1) && rawMaterial.ETD.Date < DateTime.Now.Date &&
rawMaterial.ActualDate.Date == new DateTime(2000, 1, 1))
{
DataRow dr = dt.NewRow();
// binding data to reportviewer
dr["SewingLine"] = sewingMaster.SewingLine;
dr["ProductNo"] = productNo;
if (order != null)
{
dr["ArticleNo"] = order.ArticleNo;
dr["ShoeName"] = order.ShoeName;
dr["Quantity"] = order.Quantity;
dr["ETD"] = order.ETD;
}
dr["SupplierETD"] = rawMaterial.ETD;
dr["Remarks"] = rawMaterial.Remarks;
MaterialTypeModel materialType = materialTypeList.Where(m => m.MaterialTypeId == rawMaterial.MaterialTypeId).FirstOrDefault();
if (materialType != null)
{
dr["Supplier"] = materialType.Name;
}
dt.Rows.Add(dr);
}
}
}
e.Result = dt;
}
private void bwLoadData_DoWork(object sender, DoWorkEventArgs e)
{
//materialTypeList = MaterialTypeController.Select();
materialTypeList = MaterialTypeController.Select().Where(w => w.MaterialGroup.Contains("UPPER")).ToList();
orderList = OrdersController.Select();
rawMaterialList = RawMaterialController.Select();
}
void threadLoad_DoWork(object sender, DoWorkEventArgs e)
{
materialTypeList = MaterialTypeController.Select();
orderList = OrdersController.Select();
rawMaterialList = RawMaterialController.Select();
List <String> productNoList = rawMaterialList.Select(r => r.ProductNo).Distinct().ToList();
foreach (string productNo in productNoList)
{
OrdersModel order = orderList.Where(o => o.ProductNo == productNo).FirstOrDefault();
List <Int32> materialTypeIdList = rawMaterialList.Select(r => r.MaterialTypeId).Distinct().ToList();
//Red!
materialTypeIdList.Remove(6);
materialTypeIdList.Remove(11);
materialTypeIdList.Remove(12);
materialTypeIdList.Remove(13);
foreach (int materialTypeId in materialTypeIdList)
{
RawMaterialModel rawMaterial = rawMaterialList.Where(r => r.ProductNo == productNo && r.MaterialTypeId == materialTypeId).FirstOrDefault();
if (rawMaterial != null && rawMaterial.ETD.Date != dtDefault
//&& rawMaterial.ActualDate.Date == dtDefault
)
{
DeliveryStatusViewModel deliveryStatusView = new DeliveryStatusViewModel();
deliveryStatusView.ProductNo = productNo;
if (order != null)
{
deliveryStatusView.Country = order.Country;
deliveryStatusView.ArticleNo = order.ArticleNo;
deliveryStatusView.ShoeName = order.ShoeName;
deliveryStatusView.Quantity = order.Quantity;
deliveryStatusView.ETD = order.ETD;
}
MaterialTypeModel materialType = materialTypeList.Where(m => m.MaterialTypeId == rawMaterial.MaterialTypeId).FirstOrDefault();
if (materialType != null)
{
deliveryStatusView.Supplier = materialType.Name;
}
deliveryStatusView.SupplierETD = rawMaterial.ETD;
if (rawMaterial.ActualDate != dtDefault)
{
deliveryStatusView.Actual = string.Format("{0:MM/dd/yyyy}", rawMaterial.ActualDate);
deliveryStatusView.IsFinished = true;
}
else
{
deliveryStatusView.Actual = rawMaterial.Remarks;
deliveryStatusView.IsFinished = false;
}
deliveryStatusViewList.Add(deliveryStatusView);
}
}
}
}
private void bwLoadData_DoWork(object sender, DoWorkEventArgs e)
{
materialTypeList = MaterialTypeController.Select();
orderList = OrdersController.Select();
rawMaterialList = RawMaterialController.Select();
}
private void bwLoad_DoWork(object sender, DoWorkEventArgs e)
{
materialTypeList = MaterialTypeController.Select();
Excel.Application excelApplication = new Excel.Application();
Excel.Workbook excelWorkbook = excelApplication.Workbooks.Open(filePath);
//excelApplication.Visible = true;
Excel.Worksheet excelWorksheet;
Excel.Range excelRange;
try
{
excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets[1];
excelRange = excelWorksheet.UsedRange;
progressBar.Dispatcher.Invoke((Action)(() => progressBar.Maximum = excelRange.Rows.Count));
for (int i = 3; i <= excelRange.Rows.Count; i++)
{
var productNoValue = (excelRange.Cells[i, 1] as Excel.Range).Value2;
if (productNoValue != null)
{
// column 2,3 = TAIWAN (10)
var taiwanMaterial = new RawMaterialModel();
taiwanMaterial.ProductNo = productNoValue.ToString();
taiwanMaterial.MaterialTypeId = 10;
taiwanMaterial.MaterialTypeName = materialTypeList.FirstOrDefault(w => w.MaterialTypeId == 10) != null?materialTypeList.FirstOrDefault(w => w.MaterialTypeId == 10).Name : "";
taiwanMaterial.ETD = dtDefault;
taiwanMaterial.ActualDate = dtDefault;
var efdTaiwan = (excelRange.Cells[i, 2] as Excel.Range).Value2;
if (efdTaiwan != null)
{
double efd = 0;
Double.TryParse(efdTaiwan.ToString(), out efd);
taiwanMaterial.ETD = DateTime.FromOADate(efd);
}
var actualTaiwan = (excelRange.Cells[i, 3] as Excel.Range).Value2;
if (actualTaiwan != null)
{
double actual = 0;
Double.TryParse(actualTaiwan.ToString(), out actual);
taiwanMaterial.ActualDate = DateTime.FromOADate(actual);
}
if (efdTaiwan != null || actualTaiwan != null)
{
upperMaterialFromExcelList.Add(taiwanMaterial);
}
// Column 4,5 = LAMINATION (1)
var laminationMaterial = new RawMaterialModel();
laminationMaterial.ProductNo = productNoValue.ToString();
laminationMaterial.MaterialTypeId = 1;
laminationMaterial.MaterialTypeName = materialTypeList.FirstOrDefault(w => w.MaterialTypeId == 1) != null?materialTypeList.FirstOrDefault(w => w.MaterialTypeId == 1).Name : "";
laminationMaterial.ETD = dtDefault;
laminationMaterial.ActualDate = dtDefault;
var efdLamination = (excelRange.Cells[i, 4] as Excel.Range).Value2;
if (efdLamination != null)
{
double efd = 0;
Double.TryParse(efdLamination.ToString(), out efd);
laminationMaterial.ETD = DateTime.FromOADate(efd);
}
var actualLamination = (excelRange.Cells[i, 5] as Excel.Range).Value2;
if (actualLamination != null)
{
double actual = 0;
Double.TryParse(actualLamination.ToString(), out actual);
laminationMaterial.ActualDate = DateTime.FromOADate(actual);
}
if (efdLamination != null || actualLamination != null)
{
upperMaterialFromExcelList.Add(laminationMaterial);
}
// Column 6,7 = CUTTING (1)
var cuttingMaterial = new RawMaterialModel();
cuttingMaterial.ProductNo = productNoValue.ToString();
cuttingMaterial.MaterialTypeId = 2;
cuttingMaterial.MaterialTypeName = materialTypeList.FirstOrDefault(w => w.MaterialTypeId == 2) != null?materialTypeList.FirstOrDefault(w => w.MaterialTypeId == 2).Name : "";
cuttingMaterial.ETD = dtDefault;
cuttingMaterial.ActualDate = dtDefault;
var efdCutting = (excelRange.Cells[i, 6] as Excel.Range).Value2;
if (efdCutting != null)
{
double efd = 0;
Double.TryParse(efdCutting.ToString(), out efd);
cuttingMaterial.ETD = DateTime.FromOADate(efd);
}
var actualCutting = (excelRange.Cells[i, 7] as Excel.Range).Value2;
if (actualCutting != null)
{
double actual = 0;
Double.TryParse(actualCutting.ToString(), out actual);
cuttingMaterial.ActualDate = DateTime.FromOADate(actual);
}
if (efdCutting != null || actualCutting != null)
{
upperMaterialFromExcelList.Add(cuttingMaterial);
}
// Column 8,9 = LEATHER (1)
var leatherMaterial = new RawMaterialModel();
leatherMaterial.ProductNo = productNoValue.ToString();
leatherMaterial.MaterialTypeId = 3;
leatherMaterial.MaterialTypeName = materialTypeList.FirstOrDefault(w => w.MaterialTypeId == 3) != null?materialTypeList.FirstOrDefault(w => w.MaterialTypeId == 3).Name : "";
leatherMaterial.ETD = dtDefault;
leatherMaterial.ActualDate = dtDefault;
var efdLeather = (excelRange.Cells[i, 8] as Excel.Range).Value2;
if (efdLeather != null)
{
double efd = 0;
Double.TryParse(efdLeather.ToString(), out efd);
leatherMaterial.ETD = DateTime.FromOADate(efd);
}
var actualLeather = (excelRange.Cells[i, 9] as Excel.Range).Value2;
if (actualLeather != null)
{
double actual = 0;
Double.TryParse(actualLeather.ToString(), out actual);
leatherMaterial.ActualDate = DateTime.FromOADate(actual);
}
if (efdLeather != null || actualLeather != null)
{
upperMaterialFromExcelList.Add(leatherMaterial);
}
// Column 10, 11 = SEMIPROCESS (1)
var semiprocessMaterial = new RawMaterialModel();
semiprocessMaterial.ProductNo = productNoValue.ToString();
semiprocessMaterial.MaterialTypeId = 4;
semiprocessMaterial.MaterialTypeName = materialTypeList.FirstOrDefault(w => w.MaterialTypeId == 4) != null?materialTypeList.FirstOrDefault(w => w.MaterialTypeId == 4).Name : "";
semiprocessMaterial.ETD = dtDefault;
semiprocessMaterial.ActualDate = dtDefault;
var efdSemiprocess = (excelRange.Cells[i, 10] as Excel.Range).Value2;
if (efdSemiprocess != null)
{
double efd = 0;
Double.TryParse(efdSemiprocess.ToString(), out efd);
semiprocessMaterial.ETD = DateTime.FromOADate(efd);
}
var actualSemiprocess = (excelRange.Cells[i, 11] as Excel.Range).Value2;
if (actualSemiprocess != null)
{
double actual = 0;
Double.TryParse(actualSemiprocess.ToString(), out actual);
semiprocessMaterial.ActualDate = DateTime.FromOADate(actual);
}
if (efdSemiprocess != null || actualSemiprocess != null)
{
upperMaterialFromExcelList.Add(semiprocessMaterial);
}
// Column 12, 13 = SEWING (1)
var sewingMaterial = new RawMaterialModel();
sewingMaterial.ProductNo = productNoValue.ToString();
sewingMaterial.MaterialTypeId = 5;
sewingMaterial.MaterialTypeName = materialTypeList.FirstOrDefault(w => w.MaterialTypeId == 5) != null?materialTypeList.FirstOrDefault(w => w.MaterialTypeId == 5).Name : "";
sewingMaterial.ETD = dtDefault;
sewingMaterial.ActualDate = dtDefault;
var efdSewing = (excelRange.Cells[i, 12] as Excel.Range).Value2;
if (efdSewing != null)
{
double efd = 0;
Double.TryParse(efdSewing.ToString(), out efd);
sewingMaterial.ETD = DateTime.FromOADate(efd);
}
var actualSewing = (excelRange.Cells[i, 13] as Excel.Range).Value2;
if (actualSewing != null)
{
double actual = 0;
Double.TryParse(actualSewing.ToString(), out actual);
sewingMaterial.ActualDate = DateTime.FromOADate(actual);
}
if (efdSewing != null || actualSewing != null)
{
upperMaterialFromExcelList.Add(sewingMaterial);
}
// Column 14,15 = SECURITY LABEL (1)
var securitylabelMaterial = new RawMaterialModel();
securitylabelMaterial.ProductNo = productNoValue.ToString();
securitylabelMaterial.MaterialTypeId = 7;
securitylabelMaterial.MaterialTypeName = materialTypeList.FirstOrDefault(w => w.MaterialTypeId == 7) != null?materialTypeList.FirstOrDefault(w => w.MaterialTypeId == 7).Name : "";
securitylabelMaterial.ETD = dtDefault;
securitylabelMaterial.ActualDate = dtDefault;
var efdSecuritylabel = (excelRange.Cells[i, 14] as Excel.Range).Value2;
if (efdSecuritylabel != null)
{
double efd = 0;
Double.TryParse(efdSecuritylabel.ToString(), out efd);
securitylabelMaterial.ETD = DateTime.FromOADate(efd);
}
var actualSecuritylabel = (excelRange.Cells[i, 15] as Excel.Range).Value2;
if (actualSecuritylabel != null)
{
double actual = 0;
Double.TryParse(actualSecuritylabel.ToString(), out actual);
securitylabelMaterial.ActualDate = DateTime.FromOADate(actual);
}
if (efdSecuritylabel != null || actualSecuritylabel != null)
{
upperMaterialFromExcelList.Add(securitylabelMaterial);
}
// Column 16,17 = ASSEMBLY (1)
var assemblyMaterial = new RawMaterialModel();
assemblyMaterial.ProductNo = productNoValue.ToString();
assemblyMaterial.MaterialTypeId = 8;
assemblyMaterial.MaterialTypeName = materialTypeList.FirstOrDefault(w => w.MaterialTypeId == 8) != null?materialTypeList.FirstOrDefault(w => w.MaterialTypeId == 8).Name : "";
assemblyMaterial.ETD = dtDefault;
assemblyMaterial.ActualDate = dtDefault;
var efdAssembly = (excelRange.Cells[i, 16] as Excel.Range).Value2;
if (efdAssembly != null)
{
double efd = 0;
Double.TryParse(efdAssembly.ToString(), out efd);
assemblyMaterial.ETD = DateTime.FromOADate(efd);
}
var actualAssembly = (excelRange.Cells[i, 17] as Excel.Range).Value2;
if (actualAssembly != null)
{
double actual = 0;
Double.TryParse(actualAssembly.ToString(), out actual);
assemblyMaterial.ActualDate = DateTime.FromOADate(actual);
}
if (efdAssembly != null || actualAssembly != null)
{
upperMaterialFromExcelList.Add(assemblyMaterial);
}
// Column 18,19 = SOCKLINING (1)
var sockliningMaterial = new RawMaterialModel();
sockliningMaterial.ProductNo = productNoValue.ToString();
sockliningMaterial.MaterialTypeId = 9;
sockliningMaterial.MaterialTypeName = materialTypeList.FirstOrDefault(w => w.MaterialTypeId == 9) != null?materialTypeList.FirstOrDefault(w => w.MaterialTypeId == 9).Name : "";
sockliningMaterial.ETD = dtDefault;
sockliningMaterial.ActualDate = dtDefault;
var efdSocklining = (excelRange.Cells[i, 18] as Excel.Range).Value2;
if (efdSocklining != null)
{
double efd = 0;
Double.TryParse(efdSocklining.ToString(), out efd);
sockliningMaterial.ETD = DateTime.FromOADate(efd);
}
var actualSocklining = (excelRange.Cells[i, 19] as Excel.Range).Value2;
if (actualSocklining != null)
{
double actual = 0;
Double.TryParse(actualSocklining.ToString(), out actual);
sockliningMaterial.ActualDate = DateTime.FromOADate(actual);
}
if (efdSocklining != null || actualSocklining != null)
{
upperMaterialFromExcelList.Add(sockliningMaterial);
}
//upperMaterialFromExcelList.Add(taiwanMaterial);
//upperMaterialFromExcelList.Add(laminationMaterial);
//upperMaterialFromExcelList.Add(cuttingMaterial);
//upperMaterialFromExcelList.Add(leatherMaterial);
//upperMaterialFromExcelList.Add(semiprocessMaterial);
//upperMaterialFromExcelList.Add(sewingMaterial);
//upperMaterialFromExcelList.Add(securitylabelMaterial);
//upperMaterialFromExcelList.Add(assemblyMaterial);
//upperMaterialFromExcelList.Add(sockliningMaterial);
progressBar.Dispatcher.Invoke((Action)(() => progressBar.Value = i - 2));
}
}
}
catch
{
upperMaterialFromExcelList.Clear();
}
finally
{
excelWorkbook.Close(false, Missing.Value, Missing.Value);
excelApplication.Quit();
}
}