private async Task SaveProductToDB(EnrollPlanFileExcelDataVM clPrd, EnrollmentHdrModel _EnrolH) { ///// Save Data To Database using (var transaction = _context.Database.BeginTransaction()) { try { // Save Enroll Header untuk mendapatkan ID Header _context.Add(_EnrolH); await _context.SaveChangesAsync(); clPrd.PlanUploadModel.ForEach(x => x.EnrollmentHdrID = _EnrolH.ID); clPrd.CoverageUploadModel.ForEach(x => x.EnrollmentHdrID = _EnrolH.ID); clPrd.BenefitUploadModel.ForEach(x => x.EnrollmentHdrID = _EnrolH.ID); if (clPrd.PlanUploadModel.Count() <= 0) { throw new Exception("Empty Data Plan for insert"); } if (clPrd.CoverageUploadModel.Count() <= 0) { throw new Exception("Empty Data coveverage for insert"); } if (clPrd.BenefitUploadModel.Count() <= 0) { throw new Exception("Empty Data Benefit for insert"); } _context.BulkInsert(clPrd.PlanUploadModel); _context.BulkInsert(clPrd.CoverageUploadModel); _context.BulkInsert(clPrd.BenefitUploadModel); try { var _params = new SqlParameter { ParameterName = "@EnrollmentHdrID", SqlDbType = SqlDbType.Int, Value = _EnrolH.ID }; //validasi data plan Upload _context.Database.ExecuteSqlCommand("exec ValidasiUploadPlan @EnrollmentHdrID", _params); // validasi data coverage upload _context.Database.ExecuteSqlCommand("exec ValidasiUploadCoverage @EnrollmentHdrID", _params); // validasi data benefit upload _context.Database.ExecuteSqlCommand("exec ValidasiUploadBenefit @EnrollmentHdrID", _params); } catch (Exception ex) { throw new Exception(ex.Message); } transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); throw new Exception(ex.Message); } } }
public async Task <IActionResult> UploadFilePlan([Bind("ClientID,Fileupload")] UploadFilePlanVM ClientfilePlan) { string _urlBack = (Request.Headers["Referer"].ToString() == "" ? "Index" : Request.Headers["Referer"].ToString()); try { if (ClientfilePlan.Fileupload == null || ClientfilePlan.Fileupload.Length == 0) { flashMessage.Danger("File Not Selected"); throw new Exception(); } var _client = ClientFindByID(Convert.ToInt32(ClientfilePlan.ClientID)); if (_client == null) { flashMessage.Danger("Client Not Found"); throw new Exception(); } FileUploadExt uplExt = new FileUploadExt(); //FileStream Filestrm; ExcelExt excelRead; EnrollmentHdrModel enrollH = new EnrollmentHdrModel(); // upload file ke server // Param 1 untuk file Plan excel //// Copy File To Server string FilePath; EnrollPlanFileExcelDataVM EnrolPlan = new EnrollPlanFileExcelDataVM(); try { EnrolPlan.StringPathFileUpload = await uplExt.BackupFile(1, ClientfilePlan.Fileupload); FilePath = EnrolPlan.StringPathFileUpload; } catch (Exception ex) { throw new Exception(ex.Message); } try { excelRead = new ExcelExt(EnrolPlan.StringPathFileUpload, ClientfilePlan.ClientID); } catch (Exception ex) { throw new Exception(ex.Message); } //// Read Cell Value to VM EnrolPlan.ClientID = ClientfilePlan.ClientID; EnrolPlan.ClientCode = _client.ClientCode; try { excelRead.ReadExcelEnrollPlan(ref EnrolPlan); } catch (Exception ex) { throw new Exception("Error Read Excel : " + ex.Message); } // Proses pembersihan data dan generate error Upload ValidasiFileUploadPlan(ref EnrolPlan); // Save To DB try { enrollH.ClientID = ClientfilePlan.ClientID; enrollH.FileUploadName = FilePath; await SaveProductToDB(EnrolPlan, enrollH); } catch (Exception ex) { throw new Exception("Error Bulk Insert : " + ex.Message); } flashMessage.Confirmation("Upload Success"); } catch (Exception ex) { flashMessage.Danger(ex.Message); } return(Redirect(_urlBack)); }
private void ValidasiFileUploadPlan(ref EnrollPlanFileExcelDataVM pln) { int tmp; DateTime tmpdate; var CleanPlan = new EnrollPlanFileExcelDataVM(); CleanPlan.ClientID = pln.ClientID; CleanPlan.ClientCode = pln.ClientCode; CleanPlan.PlanUploadModel = new List <PlanUploadModel>(); CleanPlan.CoverageUploadModel = new List <CoverageUploadModel>(); CleanPlan.BenefitUploadModel = new List <BenefitUploadModel>(); foreach (var item in pln.PlanUploadModel) { var newItem = new PlanUploadModel { PayorCode = (item.PayorCode ?? "").Trim(), PlanId = (item.PlanId ?? "").Trim(), CorpCode = (item.CorpCode ?? "").Trim(), EffectiveDate = (item.EffectiveDate ?? "").Trim(), TerminationDate = (item.TerminationDate ?? "").Trim(), ActiveFlag = (item.ActiveFlag ?? "").Trim(), ShortName = (item.ShortName ?? "").Trim(), LongName = (item.LongName ?? "").Trim(), Remarks = (item.Remarks ?? "").Trim(), PolicyNo = (item.PolicyNo ?? "").Trim(), FrequencyCode = (item.FrequencyCode ?? "").Trim(), LimitCode = (item.LimitCode ?? "").Trim(), MaxValue = (item.MaxValue ?? "").Trim(), FamilyMaxValue = (item.FamilyMaxValue ?? "0").Trim(), PrintText = (item.PrintText ?? "").Trim(), UploadDate = item.UploadDate, UserUpload = item.UserUpload }; //newItem.ErrorMessage = item.ErrorMessage; if (newItem.EffectiveDate.Length != 8) { newItem.errEffectiveDate = "EffectiveDate : Invalid Date"; } else if (!DateTime.TryParseExact(newItem.EffectiveDate, "yyyymmdd", CultureInfo.InvariantCulture, DateTimeStyles.None, out tmpdate)) { newItem.errEffectiveDate = "EffectiveDate : Invalid Format Date"; } if (newItem.TerminationDate.Length != 8) { newItem.errTerminationDate = "TerminationDate : Invalid Date"; } else if (!DateTime.TryParseExact(newItem.TerminationDate, "yyyymmdd", CultureInfo.InvariantCulture, DateTimeStyles.None, out tmpdate)) { newItem.errTerminationDate = "TerminationDate : Invalid Format Date"; } if (newItem.PlanId.Trim() == "") { newItem.errPlanId = "PlanID : Can not be empty"; } if (newItem.ActiveFlag.Trim().ToUpper() != "Y" && newItem.ActiveFlag != "N") { newItem.errActiveFlag = "ActiveFlag : Invalid Character"; } if (newItem.CorpCode != pln.ClientCode) { newItem.errCorpCode = "CorpCode : Incorrect ClientCode for this client"; } if (!int.TryParse(newItem.FrequencyCode, out tmp)) { newItem.errFrequencyCode = "FrequencyCode : Invalid Number"; } if (!int.TryParse(newItem.LimitCode, out tmp)) { newItem.errLimitCode = "LimitCode : Invalid Number"; } if (!int.TryParse(newItem.MaxValue, out tmp)) { newItem.errMaxValue = "MaxValue : Invalid Number"; } if (!int.TryParse(newItem.FamilyMaxValue, out tmp)) { newItem.errFamilyMaxValue = "FamilyMaxValue : Invalid Number"; } CleanPlan.PlanUploadModel.Add(newItem); } pln.PlanUploadModel.Clear(); foreach (var item in pln.CoverageUploadModel) { var newItem = new CoverageUploadModel(); newItem.PlanId = (item.PlanId ?? "").Trim(); newItem.CorpCode = (item.CorpCode ?? "").Trim(); newItem.CoverageCode = (item.CoverageCode ?? "").Trim(); newItem.ActiveFlag = (item.ActiveFlag ?? "").Trim(); newItem.ClientCoverageCode = (item.ClientCoverageCode ?? "").Trim(); newItem.LimitCode = (item.LimitCode ?? "").Trim(); newItem.FrequencyCode = (item.FrequencyCode ?? "").Trim(); newItem.MinValue = (item.MinValue ?? "0").Trim(); newItem.MaxValue = (item.MaxValue ?? "").Trim(); newItem.FamilyValue = (item.FamilyValue ?? "0").Trim(); newItem.UploadDate = item.UploadDate; newItem.UserUpload = item.UserUpload; //newItem.ErrorMessage = item.ErrorMessage; if (newItem.PlanId.Trim() == "") { newItem.errPlanId = "PlanID : Can not be empty"; } if (newItem.ActiveFlag.Trim().ToUpper() != "Y" && newItem.ActiveFlag != "N") { newItem.errActiveFlag = "ActiveFlag : Invalid Character"; } if (newItem.CorpCode != pln.ClientCode) { newItem.errCorpCode = "CorpCode : Incorrect ClientCode for this client"; } if (!int.TryParse(newItem.CoverageCode, out tmp)) { newItem.errCoverageCode = "CoverageCode : Invalid Number"; } if (!int.TryParse(newItem.FrequencyCode, out tmp)) { newItem.errFrequencyCode = "FrequencyCode : Invalid Number"; } if (!int.TryParse(newItem.LimitCode, out tmp)) { newItem.errLimitCode = "LimitCode : Invalid Number"; } if (!int.TryParse(newItem.MaxValue, out tmp)) { newItem.errMaxValue = "MaxValue : Invalid Number"; } if (!int.TryParse(newItem.FamilyValue, out tmp)) { newItem.errFamilyValue = "FamilyValue : Invalid Number"; } CleanPlan.CoverageUploadModel.Add(newItem); } pln.CoverageUploadModel.Clear(); foreach (var item in pln.BenefitUploadModel) { var newItem = new BenefitUploadModel(); newItem.PlanId = (item.PlanId ?? "").Trim(); newItem.CorpCode = (item.CorpCode ?? "").Trim(); newItem.CoverageCode = (item.CoverageCode ?? "").Trim(); newItem.BenefitCode = (item.BenefitCode ?? "").Trim(); newItem.ActiveFlag = (item.ActiveFlag ?? "").Trim(); newItem.ConditionDescription = (item.ConditionDescription ?? "").Trim(); newItem.LOA_Description = (item.LOA_Description ?? "").Trim(); newItem.ClientBenefitcode = (item.ClientBenefitcode ?? "").Trim(); newItem.MaxValue = (item.MaxValue ?? "").Trim(); newItem.LimitCode = (item.LimitCode ?? "").Trim(); newItem.FrequencyCode = (item.FrequencyCode ?? "").Trim(); newItem.MultipleCondition = (item.MultipleCondition ?? "").Trim(); newItem.UploadDate = item.UploadDate; newItem.UserUpload = item.UserUpload; //newItem.ErrorMessage = item.ErrorMessage; if (newItem.PlanId.Trim() == "") { newItem.errPlanId = "PlanID : Can not be empty"; } if (newItem.CorpCode != pln.ClientCode) { newItem.errCorpCode = "CorpCode : Incorrect ClientCode for this client"; } if (newItem.ActiveFlag.Trim().ToUpper() != "Y" && newItem.ActiveFlag != "N") { newItem.errActiveFlag = "ActiveFlag : Invalid Character"; } if (!int.TryParse(newItem.CoverageCode, out tmp)) { newItem.errCoverageCode = "CoverageCode : Invalid Number"; } if (!int.TryParse(newItem.BenefitCode, out tmp)) { newItem.errBenefitCode = "BenefitCode : Invalid Number"; } if (newItem.ConditionDescription.Trim() == "") { newItem.errConditionDescription = "ConditionDescription : Can not be empty"; } if (newItem.LOA_Description.Trim() == "") { newItem.errLOA_Description = "LOA_Description : Can not be empty"; } if (newItem.ClientBenefitcode.Trim() == "") { newItem.errClientBenefitcode = "ClientBenefitcode : Can not be empty"; } if (!int.TryParse(newItem.FrequencyCode, out tmp)) { newItem.errFrequencyCode = "FrequencyCode : Invalid Number"; } if (!int.TryParse(newItem.LimitCode, out tmp)) { newItem.errLimitCode = "LimitCode : Invalid Number"; } if (!int.TryParse(newItem.MaxValue, out tmp)) { newItem.errMaxValue = "MaxValue : Invalid Number"; } //if (!int.TryParse(newItem.FamilyValue, out tmp)) newItem.errFamilyValue = "FamilyValue : Invalid Number"; CleanPlan.BenefitUploadModel.Add(newItem); } pln.BenefitUploadModel.Clear(); pln = CleanPlan; }
public EnrollPlanFileExcelDataVM ReadExcelEnrollPlan(ref EnrollPlanFileExcelDataVM evm) { //var evm = new EnrollPlanFileExcelDataVM(); ExcelPackage package; FileInfo _fileInfoUpload = new FileInfo(_fullPathFileUpload); try { package = new ExcelPackage(_fileInfoUpload); } catch { throw new Exception("The file is not an valid Excel file. If the file is encrypted, please remove the password"); } if (package.Workbook.Worksheets.Count() < 3) { throw new Exception("File must consist of 3 sheet (Plan,Coverage,Benefit)"); } var plans = new List <PlanUploadModel>(evm.ClientID); var covs = new List <CoverageUploadModel>(evm.ClientID); var benfs = new List <BenefitUploadModel>(evm.ClientID); ExcelWorksheet ws0 = package.Workbook.Worksheets[0]; try { // Sheet 1 Plan plans = ws0 .Extract <PlanUploadModel>() .WithProperty(p => p.PayorCode, "A") .WithProperty(p => p.PlanId, "B") .WithProperty(p => p.CorpCode, "D") .WithProperty(p => p.EffectiveDate, "E") .WithProperty(p => p.TerminationDate, "F") .WithProperty(p => p.ActiveFlag, "G") .WithProperty(p => p.ShortName, "H") .WithProperty(p => p.LongName, "I") .WithProperty(p => p.Remarks, "K") .WithProperty(p => p.PolicyNo, "L") .WithProperty(p => p.FrequencyCode, "N") .WithProperty(p => p.LimitCode, "O") .WithProperty(p => p.MaxValue, "Q") .WithProperty(p => p.FamilyMaxValue, "S") .WithProperty(p => p.PrintText, "U") .GetData(2, ws0.Dimension.Rows) .ToList(); ws0.Dispose(); //plans.RemoveAll(x => x == null); //plans.RemoveAll(x => string.IsNullOrWhiteSpace(x.PayorCode)); evm.PlanUploadModel = plans; // Sheet 2 Coverage ws0 = package.Workbook.Worksheets[1]; covs = ws0 .Extract <CoverageUploadModel>() .WithProperty(p => p.PlanId, "A") .WithProperty(p => p.CorpCode, "B") .WithProperty(p => p.CoverageCode, "C") .WithProperty(p => p.ActiveFlag, "D") .WithProperty(p => p.ClientCoverageCode, "E") .WithProperty(p => p.LimitCode, "G") .WithProperty(p => p.FrequencyCode, "H") .WithProperty(p => p.MinValue, "I") .WithProperty(p => p.MaxValue, "J") .WithProperty(p => p.FamilyValue, "K") .GetData(2, ws0.Dimension.Rows) .ToList(); ws0.Dispose(); //covs.RemoveAll(x => x == null); //covs.RemoveAll(x => string.IsNullOrWhiteSpace(x.PlanId)); evm.CoverageUploadModel = covs; // Sheet 3 Benefit ws0 = package.Workbook.Worksheets[2]; benfs = ws0 .Extract <BenefitUploadModel>() .WithProperty(p => p.PlanId, "A") .WithProperty(p => p.CorpCode, "B") .WithProperty(p => p.CoverageCode, "C") .WithProperty(p => p.BenefitCode, "D") .WithProperty(p => p.ActiveFlag, "E") .WithProperty(p => p.ConditionDescription, "F") .WithProperty(p => p.LOA_Description, "G") .WithProperty(p => p.ClientBenefitcode, "H") .WithProperty(p => p.MaxValue, "J") .WithProperty(p => p.LimitCode, "K") .WithProperty(p => p.FrequencyCode, "L") .WithProperty(p => p.MultipleCondition, "P") .GetData(2, ws0.Dimension.Rows) .ToList(); //benfs.RemoveAll(x => x == null); //benfs.RemoveAll(x => string.IsNullOrWhiteSpace(x.PlanId)); evm.BenefitUploadModel = benfs; } catch (Exception ex) { throw new Exception("Error Read Excel Sheet : " + ex.Message); } finally { ws0.Dispose(); package.Dispose(); //_filestrm.Dispose(); } return(evm); }