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