public IList <DailyStock> ExtractData()
{
var dailyList = new List <DailyStock>();
using (var excelPackage = new ExcelPackage(new FileInfo(@"Data\stocks-ITX.xlsx")))
{
var selectSheet = excelPackage.Workbook.Worksheets.First();
var totalRows = selectSheet.Dimension.End.Row;
var totalColumns = selectSheet.Dimension.End.Column;
var cultureInfo = new CultureInfo("es-US");
while (totalRows >= 2)
{
var row = selectSheet.Cells[totalRows, 1, totalRows, totalColumns].Select(c => c.Value == null ? string.Empty : c.Value.ToString());
var line = row.ElementAt(0);
var data = line.Split(';');
var dailyStock = new DailyStock
{
DateTime = DateTime.Parse(data[0], cultureInfo),
CloseDay = decimal.Parse(data[1], CultureInfo.InvariantCulture),
OpenDay = decimal.Parse(data[2], CultureInfo.InvariantCulture)
};
dailyList.Add(dailyStock);
totalRows--;
}
}
return(dailyList);
}
private DateTime GetLastThursday(DailyStock daily)
{
var lastDayOfMonth = new DateTime(daily.DateTime.Year, daily.DateTime.Month, DateTime.DaysInMonth(daily.DateTime.Year, daily.DateTime.Month));
while (lastDayOfMonth.DayOfWeek != DayOfWeek.Thursday)
{
lastDayOfMonth = lastDayOfMonth.AddDays(-1);
}
return(lastDayOfMonth);
}
public IActionResult Create(DailyStock dailyStock, string message)
{
var existingData = _context.Products.FirstOrDefault(x => x.Id == dailyStock.ProductId).ProductName;
var check = _context.DailyStocks.FirstOrDefault(x => x.Product.ProductName.Equals(existingData));
if (existingData != null)
{
_client.AddToastNotification("Data Already Exist", NotificationType.success, null);
}
else if (message.Equals("Update"))
{
_context.DailyStocks.Update(dailyStock);
_context.SaveChanges();
_client.AddToastNotification("SuccessFully Updated", NotificationType.success, null);
}
_context.DailyStocks.Add(dailyStock);
_context.SaveChanges();
_client.AddToastNotification("SuccessFully Added", NotificationType.success, null);
return(RedirectToAction("New"));
}
public static bool StartInsert(string path, string finame)
{
bool result = true;
DailyStock daily = new DailyStock();
List<DailyStock> stocks = new List<DailyStock>();
string line;
try
{
//使用FileStream讀取檔案
FileStream fileStream = File.OpenRead(path);
StreamReader reader = new StreamReader(fileStream, Encoding.UTF8);
string filename = Path.GetFileName(finame).Substring(0, 3);
int i = 0;
line = reader.ReadLine();
Common.WriteLog("切割字串");
//Console.WriteLine("切割字串");
//start = DateTime.Now;
while (line != null )
{
line = new Regex("[\\s]+").Replace(line, " ");
if (line == " " || line == "")
{
line = reader.ReadLine();
continue;
}
daily = ChangString(filename, line);
stocks.Add(daily);
//Read the next line
line = reader.ReadLine();
i++;
}
//end = DateTime.Now;
//Console.WriteLine(start.ToString("hh:mm:ss"));
//Console.WriteLine(end.ToString("hh:mm:ss"));
reader.Close();
Common.WriteLog("新增DB");
result = InsertToSql(stocks);
Common.WriteLog("新增 " + finame + " 成功，一共 " + i + " 筆");
return result;
}
catch (Exception ex)
{
Common.WriteLog(ex.ToString());
result = false;
return result;
}
}
private static DailyStock ChangString(string filename, string line)
{
DailyStock dailSales = new DailyStock();
try
{
//Common.WriteLog("切割字串 " + line);
//去除多空白
line = new Regex("[\\s]+").Replace(line, " ");
//空白切割
string[] words = line.Split(' ');
string words22 = "";
if (words[22].Length==8)
{
words22 = words[22].Substring(4, 4);
}
dailSales = new DailyStock
{
StoreCode = filename,
DepartmentCode = words[0].Substring(0, 2),
ItemCode = words[0].Substring(2, 6),
SubCode = words[0].Substring(8, 3),
TransactionDate = words[0].Substring(11, 10),
SalesStatus = words[0].Substring(21, 1),
BalanceQty = words[1],
Normal_OrderQty = words[2],
Promotion_OrderQty = words[3],
Normal_FreeGoods = words[4],
Promotion_FreeGoods = words[5],
Normal_ReceivedQty = words[6],
Promotion_ReceivedQty = words[7],
Normal_ReturnedQty = words[8],
Promotion_ReturnedQty = words[9],
SalesQty = words[10],
SalesAmount = words[11],
PurchaseAmount = words[12],
AdjustedQty = words[13],
BalanceForward = words[14],
Balance_Forward_OrderNotYetReceivedBasicgoods = words[15],
Balance_Forward_OrderNotYetReceivedFreegoods = words[16],
Rebate = words[17],
AvgCost = words[18],
AvgAmount = words[19],
BFAvgAmount = words[20],
PurchasePrice = words[21],
MainSuppliercode = words[22].Substring(0, 4),
DSSuppliercode = words22,
StopSubmonth = " ",
StopSubyear = " ",
StopSubreason = " "
};
return dailSales;
}
catch (Exception ex)
{
Common.WriteLog("切割字串 " + line + " 失敗");
Common.WriteLog(ex.ToString());
return dailSales;
}
}
