public List <ItemWithQty> ConvertToItemWithQtyList(List <WCF_ItemWithQty> list)
{
List <ItemWithQty> list1 = new List <ItemWithQty>();
foreach (WCF_ItemWithQty i in list)
{
Item item = new Item();
item = cpoc.GetItemByItem_ID(i.Item_ID);
ItemWithQty it = new ItemWithQty();
it.Item_ID = item.Item_ID;
it.Category_ID = item.Category_ID;
it.Item_Name = item.Item_Name;
it.Reorder_Level = item.Reorder_Level;
it.Reorder_Qty = item.Reorder_Qty;
it.UOM = item.UOM;
it.Bin_ID = item.Bin_ID;
it.FirstSupplier_ID = item.FirstSupplier_ID;
it.SecondSupplier_ID = item.SecondSupplier_ID;
it.ThirdSupplier_ID = item.ThirdSupplier_ID;
it.Inventory = item.Inventory;
it.Status = item.Status;
it.Qty = i.Qty;
list1.Add(it);
}
return(list1);
}
public static Task <string> FromExcel(FileInfo excelFile, IDataService dataService)
{
List <Item> items = null;
dataService.GetItems(((itemNumbers, materials, exception) =>
{
if (exception != null)
{
throw exception;
}
items = itemNumbers.ToList();
}));
var cocs = new List <COC>();
//try
//{
using (var excel = new ExcelPackage(excelFile))
{
var stopwatch = new Stopwatch();
stopwatch.Start();
var worksheet = excel.Workbook.Worksheets[1];
var endRow = worksheet.Dimension.End.Row;
var qty = 0;
for (var i = 1; i <= endRow; i++)
{
var qtyParse = int.TryParse(worksheet.Cells[i, 3].Value.ToString(), out var cellQty);
if (qtyParse == false)
{
continue;
}
if (i != endRow && worksheet.Cells[i, 1].Value.ToString() == worksheet.Cells[i + 1, 1].Value.ToString())
{
if (qty == 0)
{
qty = cellQty;
}
var qtyBelowParse = int.TryParse(worksheet.Cells[i, 3].Value.ToString(), out var qtyBelow);
if (qtyBelowParse == false)
{
continue;
}
qty += qtyBelow;
continue;
}
if (qty == 0)
{
qty = cellQty;
}
var cocNumberParse = int.TryParse(worksheet.Cells[i, 1].Value.ToString(), out var cocNumber);
if (cocNumberParse == false)
{
qty = 0;
continue;
}
var dateParse =
DateTime.TryParse(worksheet.Cells[i, 4].Value.ToString(), out var inventoryDate);
if (dateParse == false)
{
inventoryDate = DateTime.Now;
}
var itemNumber = worksheet.Cells[i, 2].Value.ToString();
var newItem = items.FirstOrDefault(itm => string.Equals(itm.ItemName,
itemNumber, StringComparison.CurrentCultureIgnoreCase));
ItemWithQty itemWithQty;
if (newItem == null)
{
newItem = new Item
{
ItemName = itemNumber
};
dataService.Add(newItem);
items.Add(newItem);
itemWithQty = new ItemWithQty
{
Item = newItem,
Quantity = qty
};
}
else
{
itemWithQty = new ItemWithQty
{
Item = newItem,
Quantity = qty
};
}
var coc = new COC
{
Item = itemWithQty,
CocNumber = cocNumber,
InventoryDate = inventoryDate
};
qty = 0;
cocs.Add(coc);
}
dataService.BulkAdd(cocs);
stopwatch.Stop();
Console.WriteLine(stopwatch.Elapsed);
var message = $"Success";
dataService.SaveData();
return(Task.FromResult(result: message));
}
//}
//catch (NullReferenceException exception)
//{
// Console.WriteLine(exception);
// throw;
// Task.FromResult(result: Malformed excel file + Environment.NewLine exception.Message);
//}
}
private static IEnumerable <ItemWithQty> GetItems(ICollection <Item> items, List <PacklisteData> packlisteData, IDataService dataService)
{
if (items == null)
{
throw new ArgumentNullException(nameof(items));
}
var packlisteItems = new List <ItemWithQty>();
var quantityColumn = packlisteData.Find(c => c.Data != null &&
(c.Data.IndexOf("qty", StringComparison.OrdinalIgnoreCase) >= 0 ||
c.Data.IndexOf("antal", StringComparison.OrdinalIgnoreCase) >= 0)).ColumnNumber;
var itemData = packlisteData.Where(d =>
d.ColumnNumber == 3 && (d.Data?.IndexOf("industri", StringComparison.OrdinalIgnoreCase) < 0 &&
d.Data.IndexOf("total", StringComparison.OrdinalIgnoreCase) < 0 &&
d.Data.IndexOf("item", StringComparison.OrdinalIgnoreCase) < 0 &&
d.Data.IndexOf("id", StringComparison.OrdinalIgnoreCase) < 0 &&
d.Data.IndexOf("varenum", StringComparison.OrdinalIgnoreCase) < 0));
foreach (var item in itemData)
{
var newItem = items.FirstOrDefault(itm => string.Equals(itm.ItemName, item.Data, StringComparison.CurrentCultureIgnoreCase));
ItemWithQty itemWithQty;
float quantityValue;
var quantityResult =
float.TryParse(
packlisteData.Find(f => f.RowNumber == item.RowNumber && f.ColumnNumber == quantityColumn)?
.Data, out quantityValue);
if (quantityResult)
{
var floatResult = float.TryParse(quantityValue.ToString(), out quantityValue);
if (!floatResult)
{
quantityValue = 0f;
}
}
else
{
quantityValue = 0f;
}
if (newItem == null)
{
newItem = new Item
{
ItemName = item.Data
};
dataService.Add(newItem);
items.Add(newItem);
itemWithQty = new ItemWithQty
{
Item = newItem,
Quantity = quantityValue
};
}
else
{
itemWithQty = new ItemWithQty
{
Item = newItem,
Quantity = quantityValue
};
}
packlisteItems.Add(itemWithQty);
}
return(packlisteItems);
}
