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