private string ServiceSetVar(string servicesRecord, GiftCertDto gc, List <OutletDto> outletList)
{
var outlet = outletList.FirstOrDefault() != null?outletList.FirstOrDefault().Name : string.Empty;
servicesRecord = servicesRecord.Replace("@Value", "@Value".ToLower());
servicesRecord = servicesRecord.Replace("@Outlet", "@Outlet".ToLower());
servicesRecord = servicesRecord.Replace("@ExpirationDate", "@ExpirationDate".ToLower());
servicesRecord = servicesRecord.Replace("@Value".ToLower(), gc.Value.ToString());
servicesRecord = servicesRecord.Replace("@Outlet".ToLower(), outlet);
var expirationDate = gc.ExpirationDate != null?Convert.ToDateTime(gc.ExpirationDate).ToShortDateString() : string.Empty;
servicesRecord = servicesRecord.Replace("@ExpirationDate".ToLower(), expirationDate);
return(servicesRecord);
}
// GET: GiftCert
// public async Task<IActionResult> Index()
public IActionResult Index()
{
GiftCertDto model;
var marcoPoloGCDBContext = _context.GiftCert.Include(g => g.GcType);
//return View(await marcoPoloGCDBContext.ToListAsync());
var giftCert = marcoPoloGCDBContext.ToList();
model = new GiftCertDto
{
//Requester = user,
// Creator = user,
Status = GiftCertStatus.Draft
};
return(View(model));
}
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);
}
private ValidationResponse ValidateExcelFile(string filePath)
{
var response = new ValidationResponse();
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++)
{
var rawTextTmp = string.Empty;
for (int col = 1; col <= ColCount; col++)
{
if (worksheet.Cells[row, col].Value != null)
{
rawTextTmp += worksheet.Cells[row, col].Value.ToString() + "|";
}
else
{
rawTextTmp += "|";
}
}
if (!string.IsNullOrEmpty(rawTextTmp) && rawTextTmp != "|||||||||")
{
rawText += rawTextTmp;
rawText += "\n";
}
}
var rawText2 = rawText.Substring(0, rawText.LastIndexOf("\n") - 1);
records = new List <string>(rawText2.Split('\n'));
//limit reocrds to 100
if (records.Count > 100)
{
response.ErrorMsg.Add("The maximum number of records allowed (100) has been exceeded.");
}
for (int i = 0; i < records.Count; i++)
{
var record = records[i];
var lineNumber = i + 2;
if (string.IsNullOrEmpty(record))
{
continue;
}
var gc = new GiftCertDto();
string[] servicesRecords = new List <string>().ToArray();
string[] outletRecords = new List <string>().ToArray();
string[] textpart = record.Split('|');
//gcnumber should be in whole number
if (!IsCharDigit(textpart[0].ToString()))
{
response.ErrorMsg.Add(String.Format("Line {0}: Gift Cert No should be in whole number.", lineNumber));
}
else
{
gc.GiftCertNo = !string.IsNullOrEmpty(textpart[0]) ? Convert.ToInt32(textpart[0]) : 0;
}
//value should be in whole number
if (!IsCharDigit(textpart[2].ToString()))
{
response.ErrorMsg.Add(String.Format("Line {0}: Value should be in whole number.", lineNumber));
}
else
{
gc.Value = !string.IsNullOrEmpty(textpart[2]) ? Convert.ToDecimal(textpart[2]) : 0;
}
//gctype contains only
var gcTypeItems = new List <string> {
"regular gc", "promotional gc", "corporate gc"
};
if (!gcTypeItems.Contains(textpart[1].ToLower().Trim()))
{
response.ErrorMsg.Add(String.Format("Line {0}: GC Type should contains only Regular GC, Promotional GC and Corporate GC.", lineNumber));
}
else
{
gc.GcTypeName = !string.IsNullOrEmpty(textpart[1]) ? textpart[1] : string.Empty;
}
if (IsValidDateFormat(textpart[3]))
{
gc.IssuanceDate = !string.IsNullOrEmpty(textpart[3]) ? Convert.ToDateTime(textpart[3]) : DateTime.MinValue;
}
else
{
response.ErrorMsg.Add(String.Format("Line {0}: Please Enter the issuance date in the format 'M/D/YYYY'.", lineNumber));
}
if (IsValidDateFormat(textpart[7]))
{
gc.ExpirationDate = !string.IsNullOrEmpty(textpart[7]) ? Convert.ToDateTime(textpart[7]) : DateTime.MinValue;
}
else
{
response.ErrorMsg.Add(String.Format("Line {0}: Please Enter the expiration date in the format 'M/D/YYYY'.", lineNumber));
}
gc.DtiPermitNo = !string.IsNullOrEmpty(textpart[6]) ? textpart[6] : string.Empty;
if (!string.IsNullOrEmpty(textpart[4]))
{
servicesRecords = textpart[4].Split(';');
}
if (!string.IsNullOrEmpty(textpart[8]))
{
outletRecords = textpart[8].Split(';');
}
var giftCert = _context.GiftCert.FirstOrDefault(m => m.GiftCertNo == gc.GiftCertNo);
if (giftCert != null)
{
response.ErrorMsg.Add(String.Format("Line {0}: Gift Cert No {1} already exists in the database.", lineNumber, gc.GiftCertNo));
}
if (!string.IsNullOrEmpty(gc.GcTypeName))
{
if (gc.GcTypeName.Trim().ToLower() == "regular gc")
{
if (gc.GiftCertNo == 0 || gc.Value == 0 || servicesRecords.Count() == 0)
{
response.ErrorMsg.Add(String.Format("Line {0}: Regular GC requires Gift Cert No, GC Type, Value and Services.", lineNumber));
}
if (!string.IsNullOrEmpty(gc.DtiPermitNo) || gc.ExpirationDate != DateTime.MinValue || outletRecords.Count() > 0)
{
response.ErrorMsg.Add(String.Format("Line {0}: Regular GC restricts DTI Permit No, Expiration Date and Oulet.", lineNumber));
}
}
if (gc.GcTypeName.Trim().ToLower() == "corporate gc")
{
if (gc.GiftCertNo == 0 || gc.Value == 0 || servicesRecords.Count() == 0 || gc.ExpirationDate == DateTime.MinValue)
{
response.ErrorMsg.Add(String.Format("Line {0}: Corporate GC requires Gift Cert No, GC Type, Value, Expiration Date and Services.", lineNumber));
}
if (!string.IsNullOrEmpty(gc.DtiPermitNo) || outletRecords.Count() > 0)
{
response.ErrorMsg.Add(String.Format("Line {0}: Corporate GC restricts DTI Permit No and Oulet.", lineNumber));
}
}
if (gc.GcTypeName.Trim().ToLower() == "promotional gc")
{
if (gc.GiftCertNo == 0 || gc.Value == 0 || servicesRecords.Count() == 0 || string.IsNullOrEmpty(gc.DtiPermitNo) || gc.ExpirationDate == DateTime.MinValue || outletRecords.Count() == 0)
{
response.ErrorMsg.Add(String.Format("Line {0}: Promotional GC requires all fields.", lineNumber));
}
}
}
//expiry date greater than purchase
if (gc.ExpirationDate != DateTime.MinValue && gc.IssuanceDate != DateTime.MinValue)
{
if (gc.ExpirationDate < gc.IssuanceDate)
{
response.ErrorMsg.Add(String.Format("Line {0}: Expiration date must be greater than Issuance date.", lineNumber));
}
}
if (response.ErrorMsg.Count > 0)
{
response.IsValid = false;
}
}
}
return(response);
}
public async Task <IEnumerable <PurchaseDto> > GetPurchase()
{
var purchases = new List <PurchaseDto>();
try
{
var gcPurchases = await _context.GcPurchase.Include(p => p.Purchase).Include(g => g.GiftCertNoNavigation).ToListAsync();
foreach (var gcPurchase in gcPurchases)
{
var purchase = new PurchaseDto();
purchase.Id = gcPurchase.Purchase.Id;
purchase.PurchaseDate = gcPurchase.Purchase.PurchaseDate != null ? gcPurchase.Purchase.PurchaseDate : DateTime.MinValue;
purchase.LastModifiedBy = gcPurchase.Purchase.LastModifiedBy != null ? gcPurchase.Purchase.LastModifiedBy : string.Empty;
purchase.CreatedDate = gcPurchase.Purchase.CreatedDate != null ? gcPurchase.Purchase.CreatedDate : DateTime.MinValue;
purchase.ModifiedDate = gcPurchase.Purchase.ModifiedDate != null ? gcPurchase.Purchase.ModifiedDate : DateTime.MinValue;
purchase.Active = gcPurchase.Purchase.Active != null ? gcPurchase.Purchase.Active : true;
purchase.Remarks = gcPurchase.Purchase.Remarks != null ? gcPurchase.Purchase.Remarks : string.Empty;
purchase.PaymentMode = gcPurchase.Purchase.PaymentMode != null ? gcPurchase.Purchase.PaymentMode : string.Empty;
purchase.CcNumber = gcPurchase.Purchase.CcNumber != null ? gcPurchase.Purchase.CcNumber : string.Empty;
purchase.ExpirationDate = gcPurchase.Purchase.ExpirationDate != null ? gcPurchase.Purchase.ExpirationDate : DateTime.MinValue;
purchase.CardType = gcPurchase.Purchase.CardType != null ? gcPurchase.Purchase.CardType : string.Empty;
purchases.Add(purchase);
}
purchases = purchases.GroupBy(p => p.Id).Select(p => p.First()).ToList();
foreach (var gcPurchase in gcPurchases)
{
var giftCert = new GiftCertDto();
giftCert.GiftCertNo = gcPurchase.GiftCertNoNavigation.GiftCertNo;
giftCert.Value = gcPurchase.GiftCertNoNavigation.Value;
giftCert.GcTypeId = gcPurchase.GiftCertNoNavigation.GcTypeId;
giftCert.IssuanceDate = gcPurchase.GiftCertNoNavigation.IssuanceDate ?? DateTime.MinValue;
giftCert.DtiPermitNo = gcPurchase.GiftCertNoNavigation.DtiPermitNo ?? string.Empty;
giftCert.ExpirationDate = gcPurchase.GiftCertNoNavigation.ExpirationDate;
giftCert.LastModifiedBy = gcPurchase.GiftCertNoNavigation.LastModifiedBy ?? string.Empty;
giftCert.CreatedDate = gcPurchase.GiftCertNoNavigation.CreatedDate ?? DateTime.MinValue;
giftCert.ModifiedDate = gcPurchase.GiftCertNoNavigation.ModifiedDate ?? DateTime.MinValue;
giftCert.QrCode = gcPurchase.GiftCertNoNavigation.QrCode;
giftCert.Note = gcPurchase.GiftCertNoNavigation.Note ?? string.Empty;
giftCert.Status = gcPurchase.GiftCertNoNavigation.Status ?? 1;
var purchase = purchases.SingleOrDefault(p => p.Id == gcPurchase.PurchaseId);
if (purchase != null)
{
if (purchase.GiftCerts == null)
{
purchase.GiftCerts = new List <GiftCertDto>();
}
if (giftCert.GiftCertNo > 0)
{
purchase.GiftCerts.Add(giftCert);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(purchases);
}
private ValidationResponse ValidateExcelFile(string filePath)
{
var response = new ValidationResponse();
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'));
foreach (string record in records)
{
if (string.IsNullOrEmpty(record))
{
continue;
}
var gc = new GiftCertDto();
string[] servicesRecords = new List <string>().ToArray();
string[] outletRecords = new List <string>().ToArray();
string[] textpart = record.Split('|');
gc.GiftCertNo = !string.IsNullOrEmpty(textpart[0]) ? Convert.ToInt32(textpart[0]) : 0;
gc.GcTypeName = !string.IsNullOrEmpty(textpart[1]) ? textpart[1] : string.Empty;
gc.Value = !string.IsNullOrEmpty(textpart[2]) ? Convert.ToDecimal(textpart[2]) : 0;
gc.DtiPermitNo = !string.IsNullOrEmpty(textpart[5]) ? textpart[5] : string.Empty;
gc.ExpirationDate = !string.IsNullOrEmpty(textpart[6]) ? Convert.ToDateTime(textpart[6]) : DateTime.MinValue;
if (!string.IsNullOrEmpty(textpart[3]))
{
servicesRecords = textpart[3].Split(';');
}
if (!string.IsNullOrEmpty(textpart[7]))
{
outletRecords = textpart[7].Split(';');
}
if (string.IsNullOrEmpty(gc.GcTypeName))
{
response.ErrorMsg.Add("GC Type is required.");
response.IsValid = false;
break;
}
if (gc.GcTypeName.Trim().ToLower() == "regular gc")
{
if (gc.GiftCertNo == 0 || gc.Value == 0 || servicesRecords.Count() == 0)
{
response.ErrorMsg.Add("Regular GC requires Gift Cert No, GC Type, Value and Services.");
}
if (!string.IsNullOrEmpty(gc.DtiPermitNo) || gc.ExpirationDate != DateTime.MinValue || outletRecords.Count() > 0)
{
response.ErrorMsg.Add("Regular GC restricts DTI Permit No, Expiration Date and Oulet.");
}
}
if (gc.GcTypeName.Trim().ToLower() == "corporate gc")
{
if (gc.GiftCertNo == 0 || gc.Value == 0 || servicesRecords.Count() == 0 || gc.ExpirationDate == DateTime.MinValue)
{
response.ErrorMsg.Add("Corporate GC requires Gift Cert No, GC Type, Value, Expiration Date and Services.");
}
if (!string.IsNullOrEmpty(gc.DtiPermitNo) || outletRecords.Count() > 0)
{
response.ErrorMsg.Add("Corporate GC restricts DTI Permit No and Oulet.");
}
}
if (gc.GcTypeName.Trim().ToLower() == "promotional gc")
{
if (gc.GiftCertNo == 0 || gc.Value == 0 || servicesRecords.Count() == 0 || string.IsNullOrEmpty(gc.DtiPermitNo) || gc.ExpirationDate == DateTime.MinValue || outletRecords.Count() == 0)
{
response.ErrorMsg.Add("Promotional GC requires all fields.");
}
}
if (response.ErrorMsg.Count > 0)
{
response.IsValid = false;
break;
}
//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);
}
}
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);
}
