//
public static bool Insert(LaminationMaterialModel model)
{
var @OrderNoId = new SqlParameter("@OrderNoId", model.OrderNoId);
var @OrderNo = new SqlParameter("@OrderNo", model.OrderNo);
var @ArticleNo = new SqlParameter("@ArticleNo", model.ArticleNo);
var @ShoeName = new SqlParameter("@ShoeName", model.ShoeName);
var @PatternNo = new SqlParameter("@PatternNo", model.PatternNo);
var @ProductNoList = new SqlParameter("@ProductNoList", model.ProductNoList);
var @Position = new SqlParameter("@Position", model.Position);
var @MaterialPart = new SqlParameter("@MaterialPart", model.MaterialPart);
var @MaterialName = new SqlParameter("@MaterialName", model.MaterialName);
var @Unit = new SqlParameter("@Unit", model.Unit);
var @POQuantity = new SqlParameter("@POQuantity", model.POQuantity);
var @SendQuantity = new SqlParameter("@SendQuantity", model.SendQuantity);
var @DeliveryDate = new SqlParameter("@DeliveryDate", model.DeliveryDate);
var @PurchaseDate = new SqlParameter("@PurchaseDate", model.PurchaseDate);
var @SupplierName = new SqlParameter("@SupplierName", model.SupplierName);
var @Remarks = new SqlParameter("@Remarks", model.Remarks);
using (var db = new SaovietMasterScheduleEntities())
{
if (db.ExecuteStoreCommand("EXEC spm_InsertLaminationMaterial @OrderNoId, @OrderNo, @ArticleNo, @ShoeName, @PatternNo, @ProductNoList, @Position, @MaterialPart, @MaterialName, @Unit, @POQuantity, @SendQuantity, @DeliveryDate, @PurchaseDate, @SupplierName, @Remarks",
@OrderNoId, @OrderNo, @ArticleNo, @ShoeName, @PatternNo, @ProductNoList, @Position, @MaterialPart, @MaterialName, @Unit, @POQuantity, @SendQuantity, @DeliveryDate, @PurchaseDate, @SupplierName, @Remarks) > 0)
{
return true;
}
return false;
}
}
private void BwReadExcel_DoWork(object sender, DoWorkEventArgs e)
{
Dispatcher.Invoke(new Action(() =>
{
txtStatus.Text = "Reading ...";
prgStatus.Maximum = filePathArray.Count();
}));
int filePathIndex = 1;
foreach (string filePath in filePathArray)
{
Excel.Application excelApplication = new Excel.Application();
Excel.Workbook excelWorkbook = excelApplication.Workbooks.Open(filePath);
Excel.Worksheet excelWorksheet;
Excel.Range excelRange;
try
{
excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets[1];
excelRange = excelWorksheet.UsedRange;
var orderNoCell = excelRange[4, 1].Value2;
string orderNo = orderNoCell != null?orderNoCell.ToString() : "";
if (orderNo.Contains("."))
{
var cellSplit = orderNo.Split('.');
orderNo = cellSplit[1].Trim().ToString();
}
// Looking for purchasedate, delivery date
string purchaseDate = "", deliveryDate = "";
for (int colPur = 2; colPur < 100; colPur++)
{
var dateCell = excelRange[4, colPur].Value2;
if (dateCell != null)
{
string dateString = dateCell.ToString();
if (dateString.ToUpper().Contains("PURCHASE"))
{
if (dateString.Contains(":"))
{
purchaseDate = dateString.Split(':')[1].ToUpper().ToString();
}
}
else if (dateString.ToUpper().Contains("DELIVERY"))
{
if (dateString.Contains(":"))
{
deliveryDate = dateString.Split(':')[1].ToUpper().ToString();
}
break;
}
}
}
var articleCell = excelRange[5, 1].Value2;
string articleNo = articleCell != null?articleCell.ToString() : "";
if (articleNo.Contains(":"))
{
var cellSplit = articleNo.Split(':');
articleNo = cellSplit[1].Trim().ToString();
}
// Looking for productNo cell x,y
int colPO = 0;
for (int colPOCheck = 2; colPOCheck < 100; colPOCheck++)
{
var poCheckCell = excelRange[5, colPOCheck].Value2;
if (poCheckCell != null)
{
string poCheck = poCheckCell.ToString();
if (poCheck.ToUpper().Contains("PROD. NO.:"))
{
colPO = colPOCheck + 1;
break;
}
}
}
// Get ProductNoList
List <String> productNoList = new List <string>();
int rowNumberOfMaterial = 0;
for (int c = colPO; c < 100; c++)
{
var poColumnValueCheck = excelRange[5, c].Value2;
if (poColumnValueCheck == null)
{
continue;
}
for (int r = 5; r < 100; r++)
{
var cellBreak = excelRange[r, 1].Value2;
if (cellBreak != null)
{
string cellBreakString = cellBreak.ToString();
if (cellBreakString.ToUpper().Equals("NO."))
{
rowNumberOfMaterial = r + 1;
break;
}
}
var poCell = excelRange[r, c].Value2;
if (poCell == null)
{
continue;
}
string po = poCell.ToString();
if (!String.IsNullOrEmpty(po))
{
if (po.Contains("("))
{
var cellSplit = po.Split('(');
po = cellSplit[0].Trim().ToString();
}
productNoList.Add(po);
}
}
}
var shoeNameCell = excelRange[6, 1].Value2;
string shoeName = shoeNameCell != null?shoeNameCell.ToString() : "";
if (shoeName.Contains(":"))
{
var cellSplit = shoeName.Split(':');
shoeName = cellSplit[1].Trim().ToString();
}
var patternCell = excelRange[7, 1].Value2;
string patternNo = patternCell != null?patternCell.ToString() : "";
if (patternNo.Contains(":"))
{
var cellSplit = patternNo.Split(':');
patternNo = cellSplit[1].Trim().ToString();
}
//var purchaseDateCell = excelRange[4, 6].Value2;
//string purchaseDate = purchaseDateCell != null ? purchaseDateCell.ToString() : "";
//if (purchaseDate.Contains(":"))
//{
// var cellSplit = purchaseDate.Split(':');
// purchaseDate = cellSplit[1].Trim().ToString();
//}
//var deliveryDateCell = excelRange[4, 12].Value2;
//string deliveryDate = deliveryDateCell != null ? deliveryDateCell.ToString() : "";
//if (deliveryDate.Contains(":"))
//{
// var cellSplit = deliveryDate.Split(':');
// deliveryDate = cellSplit[1].Trim().ToString();
//}
int prodQty = 0, sendQty = 0;
string position = "", part = "", materialName = "", unit = "";//, prodQtyString = "", sendQtyString = "";
// check by part
int colPart = 2;
var cellPartCheck = excelRange[rowNumberOfMaterial, colPart].Value2;
while (cellPartCheck == null || (cellPartCheck == null && cellPartCheck.ToString().ToUpper() != "PART"))
{
colPart++;
cellPartCheck = excelRange[rowNumberOfMaterial, colPart].Value2;
}
if (cellPartCheck != null)
{
int colPosition = 0, colMaterial = 0, colUnit = 0, colProdQty = 0, colSendQty = 0;
for (int col = 1; col < 100; col++)
{
// row position, prodQty, sendQty
var cellHeaderCheck = excelRange[rowNumberOfMaterial - 1, col].Value2;
// the same row with part cell
var cellHeader_1Check = excelRange[rowNumberOfMaterial, col].Value2;
// Position
if (cellHeaderCheck != null &&
cellHeaderCheck.ToString().ToUpper() == "POSITION" &&
colPosition == 0)
{
colPosition = col;
}
if (cellHeader_1Check != null &&
cellHeader_1Check.ToString().ToUpper() == "MATERIAL" &&
colMaterial == 0)
{
colMaterial = col;
}
if (cellHeader_1Check != null &&
cellHeader_1Check.ToString().ToUpper() == "UNIT" &&
colUnit == 0)
{
colUnit = col;
}
if (cellHeaderCheck != null &&
cellHeaderCheck.ToString().ToUpper() == "PROD. QTY" &&
colProdQty == 0)
{
colProdQty = col;
}
if (cellHeaderCheck != null &&
cellHeaderCheck.ToString().ToUpper() == "SEND QTY" &&
colSendQty == 0)
{
colSendQty = col;
break;
}
}
int id = 1;
for (int r = rowNumberOfMaterial + 1; r < excelRange.Rows.Count; r++)
{
string orderId = "";
var cellPart = excelRange[r, colPart].Value2;
if (cellPart != null && !String.IsNullOrEmpty(cellPart.ToString()))
{
part = cellPart.ToString();
orderId = string.Format("{0}-{1}", orderNo, id);
var latestModel = laminationMatsList.OrderBy(o => o.Id).LastOrDefault();
// POSITION
var cellPosition = excelRange[r, colPosition].Value2;
if (cellPosition != null)
{
position = cellPosition.ToString();
}
else if (latestModel != null)
{
position = latestModel.Position;
}
// Material
var cellMaterial = excelRange[r, colMaterial].Value2;
if (cellMaterial != null)
{
materialName = cellMaterial.ToString();
}
else if (latestModel != null)
{
materialName = latestModel.MaterialName;
}
// UNIT
var cellUnit = excelRange[r, colUnit].Value2;
if (cellUnit != null)
{
unit = cellUnit.ToString();
}
else if (latestModel != null)
{
unit = latestModel.Unit;
}
// PROD.QTY
var cellProdQty = excelRange[r, colProdQty].Value2;
if (cellProdQty != null)
{
Int32.TryParse(cellProdQty.ToString(), out prodQty);
}
else if (latestModel != null)
{
prodQty = latestModel.POQuantity;
}
// SEND QTY
var cellSendQty = excelRange[r, colSendQty].Value2;
if (cellProdQty != null)
{
Int32.TryParse(cellSendQty.ToString(), out sendQty);
}
else if (latestModel != null)
{
sendQty = latestModel.SendQuantity;
}
var laminationModel = new LaminationMaterialModel
{
Id = id,
OrderNoId = orderId,
OrderNo = orderNo,
ProductNoList = String.Join("; ", productNoList),
ArticleNo = articleNo,
ShoeName = shoeName,
PatternNo = patternNo,
MaterialName = materialName,
MaterialPart = part,
Position = position,
Unit = unit,
POQuantity = prodQty,
SendQuantity = sendQty,
DeliveryDate = deliveryDate,
PurchaseDate = purchaseDate,
SupplierName = "",
Remarks = "",
};
laminationMatsList.Add(laminationModel);
id++;
}
}
}
//for (int r = rowNumberOfMaterial; r < excelRange.Rows.Count; r++)
//{
// int rowHeader = rowNumberOfMaterial - 1;
// var cellCheck = excelRange[r, 1].Value2;
// string orderId = "";
// if (cellCheck != null)
// {
// string noOfMaterial = cellCheck.ToString();
// int checkNumber = 0;
// Int32.TryParse(noOfMaterial, out checkNumber);
// if (checkNumber == 0)
// break;
// orderId = String.Format("{0}-{1}", orderNo, checkNumber);
// for (int colHeader = 2; colHeader < 100; colHeader++)
// {
// var headerCell = excelRange[rowHeader, colHeader].Value2;
// if (headerCell != null)
// {
// string header = headerCell.ToString();
// header = header.ToUpper();
// if (header.Equals("POSITION"))
// {
// var positionCell = excelRange[r, colHeader].Value2;
// position = positionCell != null ? positionCell.ToString() : "";
// }
// else if (header.Equals("MATERIAL DESCRIPTION"))
// {
// for (int colHeaderDetail = colHeader; colHeaderDetail < 100; colHeaderDetail++)
// {
// var headerDetailCell = excelRange[rowHeader + 1, colHeaderDetail].Value2;
// if (headerDetailCell != null)
// {
// string headerDetail = headerDetailCell.ToString();
// headerDetail = headerDetail.ToUpper();
// if (headerDetail.Equals("PART"))
// {
// var partCell = excelRange[r, colHeaderDetail].Value2;
// part = partCell != null ? partCell.ToString() : "";
// }
// else if (headerDetail.Equals("MATERIAL"))
// {
// var materialNameCell = excelRange[r, colHeaderDetail].Value2;
// materialName = materialNameCell != null ? materialNameCell.ToString() : "";
// }
// else if (headerDetail.Equals("UNIT"))
// {
// var unitCell = excelRange[r, colHeaderDetail].Value2;
// unit = unitCell != null ? unitCell.ToString() : "";
// break;
// }
// }
// }
// }
// else if (header.Equals("PROD. QTY"))
// {
// var prodQtyCell = excelRange[r, colHeader].Value2;
// prodQtyString = prodQtyCell != null ? prodQtyCell.ToString() : "";
// Int32.TryParse(prodQtyString, out prodQty);
// }
// else if (header.Equals("SEND QTY"))
// {
// var sendQtyCell = excelRange[r, colHeader].Value2;
// sendQtyString = sendQtyCell != null ? sendQtyCell.ToString() : "";
// Int32.TryParse(sendQtyString, out sendQty);
// break;
// }
// }
// }
// var laminationModel = new LaminationMaterialModel
// {
// OrderNoId = orderId,
// OrderNo = orderNo,
// ProductNoList = String.Join("; ", productNoList),
// ArticleNo = articleNo,
// ShoeName = shoeName,
// PatternNo = patternNo,
// MaterialName = materialName,
// MaterialPart = part,
// Position = position,
// Unit = unit,
// POQuantity = prodQty,
// SendQuantity = sendQty,
// DeliveryDate = deliveryDate,
// PurchaseDate = purchaseDate,
// SupplierName = "",
// Remarks = "",
// };
// laminationMatsList.Add(laminationModel);
// }
//}
Dispatcher.Invoke(new Action(() =>
{
txtStatus.Text = String.Format("Reading OrderNo: {0} total file: {1}/{2}", orderNo, filePathIndex, filePathArray.Count());
prgStatus.Value = filePathIndex;
}));
}
catch
{
}
finally
{
excelWorkbook.Close(false, Missing.Value, Missing.Value);
excelApplication.Quit();
}
filePathIndex++;
}
}
