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); }