/// <summary>
/// Method to import Purchase History from CSV
/// </summary>
public void ImportPurchaseHistory()
{
SaleItemDetails salesItemDetailsCsv;
bool headerFlag = false;
try
{
using (var reader = new StreamReader(_filePath))
{
while (!reader.EndOfStream)
{
salesItemDetailsCsv = new SaleItemDetails();
var dataLine = reader.ReadLine();
if (!headerFlag)
{
headerFlag = true;
continue;
}
List <string> dataValue = dataLine.Split(',').ToList();
for (int ind = 0; ind < dataValue.Count; ind++)
{
if (!string.IsNullOrEmpty(dataValue[ind]))
{
object valuedata = dataValue[ind];
object dopData = dataValue[1];
switch (ind)
{
case 2:
DateTime dateOfPurchase1;
bool dopFlag1 = DateTime.TryParse(dopData.ToString(), out dateOfPurchase1);
if (!Convert.ToString(valuedata).Equals(string.Empty) && dopFlag1)
{
salesItemDetailsCsv.SalesRecord = this.salesRecord.FirstOrDefault(rec =>
((rec.Customer.Name == Convert.ToString(valuedata)) &&
rec.DateOfPurchase == dateOfPurchase1));
if (salesItemDetailsCsv.SalesRecord == null)
{
salesItemDetailsCsv.SalesRecord = new SalesRecord();
salesItemDetailsCsv.SalesRecord.Customer = this.customers.FirstOrDefault(cust => cust.Name == Convert.ToString(valuedata));
salesItemDetailsCsv.SalesRecord.DateOfPurchase = dateOfPurchase1;
this.salesRecord.Add(salesItemDetailsCsv.SalesRecord);
}
}
break;
case 3:
DateTime dateOfPurchase2;
bool dopFlag2 = DateTime.TryParse(dopData.ToString(), out dateOfPurchase2);
if (!Convert.ToString(valuedata).Equals(string.Empty) && dopFlag2)
{
salesItemDetailsCsv.SalesRecord = this.salesRecord.FirstOrDefault(rec =>
((rec.Customer.ContactNumber == Convert.ToString(valuedata)) &&
rec.DateOfPurchase == dateOfPurchase2));
if (salesItemDetailsCsv.SalesRecord == null)
{
salesItemDetailsCsv.SalesRecord = new SalesRecord();
salesItemDetailsCsv.SalesRecord.Customer = this.customers.FirstOrDefault(cust => cust.ContactNumber == Convert.ToString(valuedata));
salesItemDetailsCsv.SalesRecord.DateOfPurchase = dateOfPurchase2;
this.salesRecord.Add(salesItemDetailsCsv.SalesRecord);
}
}
break;
case 4:
salesItemDetailsCsv.Book = books.FirstOrDefault(bk => bk.Name == Convert.ToString(valuedata));
break;
case 5:
salesItemDetailsCsv.Quantity = Convert.ToInt32(valuedata);
salesItemDetailsCsv.SalesRecord.NoOfItemsPurchased += salesItemDetailsCsv.Quantity;
break;
case 6:
salesItemDetailsCsv.TotalCost = Convert.ToDouble(valuedata);
salesItemDetailsCsv.SalesRecord.TotalCostBeforeDiscount += salesItemDetailsCsv.TotalCost;
break;
case 7:
Double feePaid;
if (Double.TryParse(valuedata.ToString(), out feePaid))
{
salesItemDetailsCsv.SalesRecord.MembershipFeesPaid = feePaid;
}
break;
default:
break;
}
}
}
salesItemDetailsCsv.SalesRecord.TotalCostAfterDiscount = salesItemDetailsCsv.SalesRecord.TotalCostBeforeDiscount -
(salesItemDetailsCsv.SalesRecord.TotalCostBeforeDiscount * salesItemDetailsCsv.SalesRecord.DiscountPercent ?? 0);
this.saleItemDetails.Add(salesItemDetailsCsv);
}
}
}
catch (IOException ioe)
{
MessageBox.Show("Failed to Import purchase history data", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception ex)
{
MessageBox.Show("Failed to Import purchase history data", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
this._tbsRepository.AddPurchaseHistory(this.saleItemDetails);
}
/// <summary>
/// This method is responsible for Importing sample data from Excel input file
/// </summary>
/// <param name="filePath"></param>
public override void ImportData(string filePath)
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath);
Excel.Sheets xlWorksheets = xlWorkbook.Sheets;
Excel.Range xlRange = null;
try
{
Customer customerXL;
Book bookXL;
SaleItemDetails salesItemDetailsXL;
foreach (Excel.Worksheet workSheet in xlWorksheets)
{
xlRange = workSheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
switch (workSheet.Name)
{
case "Customers":
{
for (int i = 2; i <= rowCount; i++)
{
customerXL = new Customer();
for (int j = 1; j <= colCount; j++)
{
if (((Excel.Range)xlRange.Cells[i, j]) != null && ((Excel.Range)xlRange.Cells[i, j]).Value != null)
{
object valuedata = ((Excel.Range)xlRange.Cells[i, j]).Value;
switch (j)
{
case 1:
//customer.ID = Convert.ToInt32(valuedata);
break;
case 2:
customerXL.Name = Convert.ToString(valuedata);
break;
case 3:
customerXL.Address = Convert.ToString(valuedata);
break;
case 4:
customerXL.ContactNumber = Convert.ToString(valuedata);
break;
case 5:
DateTime dob;
if (DateTime.TryParse(valuedata.ToString(), out dob))
{
customerXL.DateOfBirth = dob;
}
break;
case 6:
DateTime memSince;
if (DateTime.TryParse(valuedata.ToString(), out memSince))
{
customerXL.MemberSince = memSince;
}
break;
case 7:
DateTime validity;
if (DateTime.TryParse(valuedata.ToString(), out validity))
{
customerXL.ValidityExpiryDate = validity;
}
break;
default:
break;
}
}
}
this.customers.Add(customerXL);
}
}
break;
case "Books":
{
for (int i = 2; i <= rowCount; i++)
{
bookXL = new Book();
for (int j = 1; j <= colCount; j++)
{
if (((Excel.Range)xlRange.Cells[i, j]) != null && ((Excel.Range)xlRange.Cells[i, j]).Value != null)
{
object valuedata = ((Excel.Range)xlRange.Cells[i, j]).Value;
switch (j)
{
case 1:
//book.ID = Convert.ToInt32(valuedata);
break;
case 2:
bookXL.Name = Convert.ToString(valuedata);
break;
case 3:
bookXL.Author = Convert.ToString(valuedata);
break;
case 4:
bookXL.Genre = Convert.ToString(valuedata);
break;
case 5:
bookXL.Price = Convert.ToDouble(valuedata);
break;
case 6:
bookXL.AvailableCount = Convert.ToInt32(valuedata);
break;
default:
break;
}
}
}
this.books.Add(bookXL);
}
}
break;
case "PurchaseHistory":
{
int saleItemsIDCounter = 1;
for (int i = 2; i <= rowCount; i++)
{
salesItemDetailsXL = new SaleItemDetails();
salesItemDetailsXL.ID = saleItemsIDCounter++;
for (int j = 2; j <= colCount; j++)
{
if (((Excel.Range)xlRange.Cells[i, j]) != null && ((Excel.Range)xlRange.Cells[i, j]).Value != null)
{
object valuedata = ((Excel.Range)xlRange.Cells[i, j]).Value;
object dopData = ((Excel.Range)xlRange.Cells[i, 2]).Value;
switch (j)
{
case 2:
break;
case 3:
DateTime dateOfPurchase1;
bool dopFlag1 = DateTime.TryParse(dopData.ToString(), out dateOfPurchase1);
if (!Convert.ToString(valuedata).Equals(string.Empty) && dopFlag1)
{
salesItemDetailsXL.SalesRecord = this.salesRecord.FirstOrDefault(rec =>
((rec.Customer.Name == Convert.ToString(valuedata)) &&
rec.DateOfPurchase == dateOfPurchase1));
if (salesItemDetailsXL.SalesRecord == null)
{
salesItemDetailsXL.SalesRecord = new SalesRecord();
salesItemDetailsXL.SalesRecord.Customer = this.customers.FirstOrDefault(cust => cust.Name == Convert.ToString(valuedata));
salesItemDetailsXL.SalesRecord.DateOfPurchase = dateOfPurchase1;
this.salesRecord.Add(salesItemDetailsXL.SalesRecord);
}
}
break;
case 4:
DateTime dateOfPurchase2;
bool dopFlag2 = DateTime.TryParse(dopData.ToString(), out dateOfPurchase2);
if (!Convert.ToString(valuedata).Equals(string.Empty) && dopFlag2)
{
salesItemDetailsXL.SalesRecord = this.salesRecord.FirstOrDefault(rec =>
((rec.Customer.ContactNumber == Convert.ToString(valuedata)) &&
rec.DateOfPurchase == dateOfPurchase2));
if (salesItemDetailsXL.SalesRecord == null)
{
salesItemDetailsXL.SalesRecord = new SalesRecord();
salesItemDetailsXL.SalesRecord.Customer = this.customers.FirstOrDefault(cust => cust.ContactNumber == Convert.ToString(valuedata));
salesItemDetailsXL.SalesRecord.DateOfPurchase = dateOfPurchase2;
this.salesRecord.Add(salesItemDetailsXL.SalesRecord);
}
}
break;
case 5:
salesItemDetailsXL.Book = books.FirstOrDefault(bk => bk.Name == Convert.ToString(valuedata));
break;
case 6:
salesItemDetailsXL.Quantity = Convert.ToInt32(valuedata);
salesItemDetailsXL.SalesRecord.NoOfItemsPurchased += salesItemDetailsXL.Quantity;
break;
case 7:
salesItemDetailsXL.TotalCost = Convert.ToDouble(valuedata);
salesItemDetailsXL.SalesRecord.TotalCostBeforeDiscount += salesItemDetailsXL.TotalCost;
break;
case 8:
Double feePaid;
if (Double.TryParse(valuedata.ToString(), out feePaid))
{
salesItemDetailsXL.SalesRecord.MembershipFeesPaid = feePaid;
}
break;
default:
break;
}
}
}
salesItemDetailsXL.SalesRecord.TotalCostAfterDiscount = salesItemDetailsXL.SalesRecord.TotalCostBeforeDiscount -
(salesItemDetailsXL.SalesRecord.TotalCostBeforeDiscount * salesItemDetailsXL.SalesRecord.DiscountPercent ?? 0);
this.saleItemDetails.Add(salesItemDetailsXL);
}
}
break;
default:
break;
}
}
this._tbsRepository.AddCustomers(this.customers);
this._tbsRepository.AddBooks(this.books);
this._tbsRepository.AddPurchaseHistory(this.saleItemDetails);
}
catch (Exception ex)
{
MessageBox.Show("Failed to Import data from excel file", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheets);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
}
