public ValidationResponse BulkCopy(string filePath)
{
var response = new ValidationResponse();
response = ValidateExcelFile(filePath);
var builder = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
.AddEnvironmentVariables();
builder.AddEnvironmentVariables();
Configuration = builder.Build();
string connectionstring = Configuration["ConnectionStrings:ProductionConnection"];
// var filePath = @"D:/GcDetails.xlsx";
FileInfo file = new FileInfo(filePath);
using (ExcelPackage package = new ExcelPackage(file))
{
StringBuilder sb = new StringBuilder();
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int rowCount = worksheet.Dimension.Rows;
int ColCount = worksheet.Dimension.Columns;
var rawText = string.Empty;
List <string> records = new List <string>();
//validate column headers
response = ValidateColumnHeaders(response, ColCount, worksheet);
for (int row = 2; row <= rowCount; row++)
{
for (int col = 1; col <= ColCount; col++)
{
if (col == 1 && worksheet.Cells[row, col].Value == null)
{
break;
}
if (worksheet.Cells[row, col].Value != null)
{
rawText += worksheet.Cells[row, col].Value.ToString() + "|";
}
else
{
rawText += "|";
}
}
rawText += "\n";
}
records = new List <string>(rawText.Split('\n'));
var gcList = new List <GiftCertDto>();
for (int i = 0; i < records.Count; i++)
{
var record = records[i];
var lineNumber = i + 2;
if (string.IsNullOrEmpty(record))
{
break;
}
var gc = new GiftCertDto();
var gcTypeItems = new List <string> {
"regular gc", "promotional gc", "corporate gc"
};
string[] textpart = record.Split('|');
if (textpart[0] != string.Empty && IsCharDigit(textpart[0].ToString()))
{
gc.GiftCertNo = Convert.ToInt32(textpart[0]);
}
if (gcTypeItems.Contains(textpart[1].ToLower().Trim()))
{
gc.GcTypeName = textpart[1].Trim();
}
if (textpart[2] != string.Empty && IsCharDigit(textpart[2].ToString()))
{
gc.Value = Convert.ToDecimal(textpart[2]);
}
if (textpart[3] != string.Empty && IsValidDateFormat(textpart[3]))
{
gc.IssuanceDate = Convert.ToDateTime(textpart[3]);
}
gc.Note = textpart[5];
gc.DtiPermitNo = textpart[6];
if (textpart[7] != string.Empty && IsValidDateFormat(textpart[7]))
{
gc.ExpirationDate = Convert.ToDateTime(textpart[7]);
}
var outletList = new List <OutletDto>();
string[] outletRecords = textpart[8].Split(';');
foreach (string outletRecord in outletRecords)
{
var outlet = new OutletDto();
outlet.Name = outletRecord;
outletList.Add(outlet);
}
var servicesList = new List <ServicesTypeDto>();
string[] servicesRecords = textpart[4].Split(';');
foreach (string servicesRecord in servicesRecords)
{
var servicesType = new ServicesTypeDto();
servicesType.Name = ServiceSetVar(servicesRecord, gc, outletList);
servicesList.Add(servicesType);
}
//Outlets - Promotional GC:
var outletItems = new List <string> {
"café marco", "wellness zone spa", "rooms"
};
if (outletList.Where(o => !string.IsNullOrEmpty(o.Name)).Select(o => o.Name.ToLower().Trim()).Except(outletItems).Any())
{
response.ErrorMsg.Add(String.Format("Line {0}: Promotional GC should contains only Café Marco, Wellness Zone Spa and Rooms.", lineNumber));
}
gc.Services = servicesList;
gc.Outlets = outletList;
gcList.Add(gc);
}
//validate sequential gcno
var isSequential = IsSequential(gcList.Select(m => m.GiftCertNo).ToArray());
if (!isSequential)
{
response.ErrorMsg.Add("Column A: GC Number should be sequenced.");
}
if (response.ErrorMsg.Count > 0)
{
_toastNotification.AddErrorToastMessage("Your Import has failed.");
return(response);
}
//sql entity
var giftCerts = new List <GiftCert>();
var gcOutlets = new List <GcOutlet>();
var services = new List <ServicesType>();
foreach (var gc in gcList)
{
var giftCert = new GiftCert();
if (gc.GcTypeName.Replace(" ", "").ToLower() == GcTypeOptions.RegularGC.ToString().ToLower())
{
giftCert.GcTypeId = (int)GcTypeOptions.RegularGC;
}
if (gc.GcTypeName.Replace(" ", "").ToLower() == GcTypeOptions.PromotionalGC.ToString().ToLower())
{
giftCert.GcTypeId = (int)GcTypeOptions.PromotionalGC;
}
if (gc.GcTypeName.Replace(" ", "").ToLower() == GcTypeOptions.CorporateGC.ToString().ToLower())
{
giftCert.GcTypeId = (int)GcTypeOptions.CorporateGC;
}
giftCert.Value = gc.Value;
giftCert.DtiPermitNo = gc.DtiPermitNo;
giftCert.ExpirationDate = gc.ExpirationDate;
giftCert.IssuanceDate = gc.IssuanceDate;
giftCert.QrCode = Guid.NewGuid().ToString();
giftCert.GiftCertNo = gc.GiftCertNo;
giftCert.Note = gc.Note;
giftCert.LastModifiedBy = "leila";
giftCert.CreatedDate = DateTime.Now;
giftCert.ModifiedDate = DateTime.Now;
giftCerts.Add(giftCert);
//gcoutlet
foreach (var outlet in gc.Outlets)
{
if (string.IsNullOrEmpty(outlet.Name))
{
break;
}
var gcOutlet = new GcOutlet();
if (outlet.Name.Replace(" ", "") == OutletOptions.CaféMarco.ToString())
{
gcOutlet.OutletId = (int)OutletOptions.CaféMarco;
}
if (outlet.Name.Replace(" ", "") == OutletOptions.ElViento.ToString())
{
gcOutlet.OutletId = (int)OutletOptions.ElViento;
}
if (outlet.Name.Replace(" ", "") == OutletOptions.LobbyLounge.ToString())
{
gcOutlet.OutletId = (int)OutletOptions.LobbyLounge;
}
if (outlet.Name.Replace(" ", "") == OutletOptions.WellnessZoneSpa.ToString())
{
gcOutlet.OutletId = (int)OutletOptions.WellnessZoneSpa;
}
if (outlet.Name.Replace(" ", "") == OutletOptions.Rooms.ToString())
{
gcOutlet.OutletId = (int)OutletOptions.Rooms;
}
if (outlet.Name.Replace(" ", "") == OutletOptions.BluBarAndGrill.ToString())
{
gcOutlet.OutletId = (int)OutletOptions.BluBarAndGrill;
}
gcOutlet.GiftCertNo = gc.GiftCertNo;
gcOutlets.Add(gcOutlet);
}
//services
foreach (var service in gc.Services)
{
if (string.IsNullOrEmpty(service.Name))
{
break;
}
var servicesType = new ServicesType();
servicesType.GiftCertNo = gc.GiftCertNo;
servicesType.Name = service.Name;
servicesType.Active = true;
servicesType.LastModifiedBy = "leila";
servicesType.CreatedDate = DateTime.Now;
servicesType.ModifiedDate = DateTime.Now;
services.Add(servicesType);
}
}
var gcParameters = new[]
{
nameof(GiftCert.GcTypeId),
nameof(GiftCert.Value),
nameof(GiftCert.IssuanceDate),
nameof(GiftCert.DtiPermitNo),
nameof(GiftCert.ExpirationDate),
nameof(GiftCert.LastModifiedBy),
nameof(GiftCert.CreatedDate),
nameof(GiftCert.ModifiedDate),
nameof(GiftCert.QrCode),
nameof(GiftCert.Note),
nameof(GiftCert.Status),
nameof(GiftCert.GiftCertNo)
};
var gcOutletParameters = new[]
{
nameof(GcOutlet.Id),
nameof(GcOutlet.OutletId),
nameof(GcOutlet.GiftCertNo)
};
var serviceTypeParameters = new[]
{
nameof(ServicesType.Id),
nameof(ServicesType.LastModifiedBy),
nameof(ServicesType.CreatedDate),
nameof(ServicesType.ModifiedDate),
nameof(ServicesType.Name),
nameof(ServicesType.Active),
nameof(ServicesType.GiftCertNo)
};
using (var sqlcopy = new SqlBulkCopy(connectionstring, SqlBulkCopyOptions.Default))
{
sqlcopy.BatchSize = 500;
sqlcopy.DestinationTableName = "[GiftCert]";
using (var reader = ObjectReader.Create(giftCerts, gcParameters))
{
sqlcopy.WriteToServer(reader);
}
sqlcopy.DestinationTableName = "[GcOutlet]";
using (var reader = ObjectReader.Create(gcOutlets, gcOutletParameters))
{
sqlcopy.WriteToServer(reader);
}
sqlcopy.DestinationTableName = "[ServicesType]";
using (var reader = ObjectReader.Create(services, serviceTypeParameters))
{
sqlcopy.WriteToServer(reader);
}
}
}
return(response);
}
public ValidationResponse BulkCopy(string filePath)
{
var response = new ValidationResponse();
response = ValidateExcelFile(filePath);
if (response.IsValid)
{
// TODO: Extract this to a service class
// load from excel
var builder = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
.AddEnvironmentVariables();
builder.AddEnvironmentVariables();
Configuration = builder.Build();
string connectionstring = Configuration["ConnectionStrings:DefaultConnection"];
// var filePath = @"D:/GcDetails.xlsx";
FileInfo file = new FileInfo(filePath);
using (ExcelPackage package = new ExcelPackage(file))
{
StringBuilder sb = new StringBuilder();
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int rowCount = worksheet.Dimension.Rows;
int ColCount = worksheet.Dimension.Columns;
var rawText = string.Empty;
List <string> records = new List <string>();
for (int row = 2; row <= rowCount; row++)
{
for (int col = 1; col <= ColCount; col++)
{
if (col == 1 && worksheet.Cells[row, col].Value == null)
{
break;
}
if (worksheet.Cells[row, col].Value != null)
{
rawText += worksheet.Cells[row, col].Value.ToString() + "|";
}
else
{
rawText += "|";
}
}
rawText += "\n";
}
records = new List <string>(rawText.Split('\n'));
var gcList = new List <GiftCertDto>();
foreach (string record in records)
{
if (string.IsNullOrEmpty(record))
{
break;
}
var gc = new GiftCertDto();
string[] textpart = record.Split('|');
if (textpart[0] != string.Empty)
{
gc.GiftCertNo = Convert.ToInt32(textpart[0]);
}
gc.GcTypeName = textpart[1];
if (textpart[2] != string.Empty)
{
gc.Value = Convert.ToDecimal(textpart[2]);
}
gc.Note = textpart[4];
gc.DtiPermitNo = textpart[5];
if (textpart[6] != string.Empty)
{
gc.ExpirationDate = Convert.ToDateTime(textpart[6]);
}
var servicesList = new List <ServicesTypeDto>();
string[] servicesRecords = textpart[3].Split(';');
foreach (string servicesRecord in servicesRecords)
{
var servicesType = new ServicesTypeDto();
servicesType.Name = servicesRecord;
servicesList.Add(servicesType);
}
var outletList = new List <OutletDto>();
string[] outletRecords = textpart[7].Split(';');
foreach (string outletRecord in outletRecords)
{
var outlet = new OutletDto();
outlet.Name = outletRecord;
outletList.Add(outlet);
}
gc.Services = servicesList;
gc.Outlets = outletList;
gcList.Add(gc);
}
//sql entity
var giftCerts = new List <GiftCert>();
var gcOutlets = new List <GcOutlet>();
var services = new List <ServicesType>();
foreach (var gc in gcList)
{
var giftCert = new GiftCert();
if (gc.GcTypeName.Replace(" ", "") == GcTypeOptions.RegularGC.ToString())
{
giftCert.GcTypeId = (int)GcTypeOptions.RegularGC;
}
if (gc.GcTypeName.Replace(" ", "") == GcTypeOptions.PromotionalGC.ToString())
{
giftCert.GcTypeId = (int)GcTypeOptions.PromotionalGC;
}
if (gc.GcTypeName.Replace(" ", "") == GcTypeOptions.CorporateGC.ToString())
{
giftCert.GcTypeId = (int)GcTypeOptions.CorporateGC;
}
giftCert.Value = gc.Value;
giftCert.DtiPermitNo = gc.DtiPermitNo;
giftCert.ExpirationDate = gc.ExpirationDate;
giftCert.QrCode = Guid.NewGuid().ToString();
giftCert.GiftCertNo = gc.GiftCertNo;
giftCert.Note = gc.Note;
giftCert.LastModifiedBy = "leila";
giftCert.CreatedDate = DateTime.Now;
giftCert.ModifiedDate = DateTime.Now;
giftCerts.Add(giftCert);
//gcoutlet
foreach (var outlet in gc.Outlets)
{
if (string.IsNullOrEmpty(outlet.Name))
{
break;
}
var gcOutlet = new GcOutlet();
if (outlet.Name.Replace(" ", "") == OutletOptions.CaféMarco.ToString())
{
gcOutlet.OutletId = (int)OutletOptions.CaféMarco;
}
if (outlet.Name.Replace(" ", "") == OutletOptions.ElViento.ToString())
{
gcOutlet.OutletId = (int)OutletOptions.ElViento;
}
if (outlet.Name.Replace(" ", "") == OutletOptions.LobbyLounge.ToString())
{
gcOutlet.OutletId = (int)OutletOptions.LobbyLounge;
}
gcOutlet.GiftCertNo = gc.GiftCertNo;
gcOutlets.Add(gcOutlet);
}
//services
foreach (var service in gc.Services)
{
if (string.IsNullOrEmpty(service.Name))
{
break;
}
var servicesType = new ServicesType();
servicesType.GiftCertNo = gc.GiftCertNo;
servicesType.Name = service.Name;
servicesType.Active = true;
servicesType.LastModifiedBy = "leila";
servicesType.CreatedDate = DateTime.Now;
servicesType.ModifiedDate = DateTime.Now;
services.Add(servicesType);
}
}
var gcParameters = new[]
{
nameof(GiftCert.GcTypeId),
nameof(GiftCert.Value),
nameof(GiftCert.IssuanceDate),
nameof(GiftCert.DtiPermitNo),
nameof(GiftCert.ExpirationDate),
nameof(GiftCert.LastModifiedBy),
nameof(GiftCert.CreatedDate),
nameof(GiftCert.ModifiedDate),
nameof(GiftCert.QrCode),
nameof(GiftCert.Note),
nameof(GiftCert.Status),
nameof(GiftCert.GiftCertNo)
};
var gcOutletParameters = new[]
{
nameof(GcOutlet.Id),
nameof(GcOutlet.OutletId),
nameof(GcOutlet.GiftCertNo)
};
var serviceTypeParameters = new[]
{
nameof(ServicesType.Id),
nameof(ServicesType.LastModifiedBy),
nameof(ServicesType.CreatedDate),
nameof(ServicesType.ModifiedDate),
nameof(ServicesType.Name),
nameof(ServicesType.Active),
nameof(ServicesType.GiftCertNo)
};
using (var sqlcopy = new SqlBulkCopy(connectionstring, SqlBulkCopyOptions.Default))
{
sqlcopy.BatchSize = 500;
sqlcopy.DestinationTableName = "[GiftCert]";
using (var reader = ObjectReader.Create(giftCerts, gcParameters))
{
sqlcopy.WriteToServer(reader);
}
sqlcopy.DestinationTableName = "[GcOutlet]";
using (var reader = ObjectReader.Create(gcOutlets, gcOutletParameters))
{
sqlcopy.WriteToServer(reader);
}
sqlcopy.DestinationTableName = "[ServicesType]";
using (var reader = ObjectReader.Create(services, serviceTypeParameters))
{
sqlcopy.WriteToServer(reader);
}
}
}
}
return(response);
}
