protected void cmdExport_Click(object sender, EventArgs e)
{
string uploadPath = "";
try
{
string assurName = lbAssur.SelectedItem.Text.ToString();
List <Cadencier> groupsSante = Cadencier.GetCadencierForAssureur(assurName);
ExcelPackage pack = BLCadencier.ExportCadencierForAssureur(assurName);
uploadPath = Path.Combine(Request.PhysicalApplicationPath, C.uploadFolder);
uploadPath = Path.Combine(uploadPath, User.Identity.Name + "_" + assurName + "_Cadencier.xlsx");
pack.SaveAs(new FileInfo(uploadPath));
UICommon.DownloadFile(this.Page, uploadPath);
}
catch (Exception ex)
{
if (ex.Message != "Thread was being aborted.")
{
log.Error(ex.Message);
}
var myCustomValidator = new CustomValidator();
myCustomValidator.IsValid = false;
myCustomValidator.ErrorMessage = ex.Message;
Page.Validators.Add(myCustomValidator);
}
}
protected void cmdDelete_Click(object sender, EventArgs e)
{
try
{
//handle the delete event
string assurName = lbAssur.SelectedItem.Text.ToString();
Session["SelectedAssureurIndex"] = lbAssur.SelectedIndex;
if (!string.IsNullOrWhiteSpace(assurName))
{
Cadencier.DeleteCadencierWithSpecificAssureurName(assurName);
}
else
{
throw new Exception("Please select the name of the 'Assureur' for which you want to delete the Cadencier!");
}
//refresh the tree
lbAssur.DataBind();
if (ItemExists(assurName))
{
SelectItem(assurName);
rptCad.DataBind();
//UpdateTreeView(assurName);
}
else
{
if (lbAssur.Items.Count > 0)
{
SelectItem(lbAssur.Items[0].Text);
rptCad.DataBind();
//UpdateTreeView(lbAssur.Items[0].Text);
}
else
{
//tvGaranties.Nodes.Clear();
rptCad.DataBind();
}
}
}
catch (Exception ex) { UICommon.HandlePageError(ex, this.Page, "GestionCadencier::cmdDelete_Click"); }
}
public IEnumerable <Cadencier> GetCadencier()
{
string assurName;
try
{
if (lbAssur.SelectedItem != null)
{
assurName = lbAssur.SelectedItem.Text.ToString();
}
else
{
assurName = C.cDEFAULTASSUREUR;
}
return(Cadencier.GetCadencierForAssureur(assurName));
}
catch (Exception ex) { UICommon.HandlePageError(ex, this.Page, "GestionCadencier::GetCadencier"); return(null); }
}
public static ExcelPackage ExportCadencierForAssureur(string assureurName)
{
try
{
List <Cadencier> cad = Cadencier.GetCadencierForAssureur(assureurName);
ExcelPackage pck = new ExcelPackage();
var ws = pck.Workbook.Worksheets.Add(assureurName);
//write the header
//ws.Cells["A3"].Style.Numberformat.Format = "yyyy-mm-dd";
//ws.Column(2).Style.Numberformat.Format = "dd-mm-yyyy";
//ws.Column(3).Style.Numberformat.Format = "dd-mm-yyyy";
ws.Column(2).Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
ws.Column(3).Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
ws.Cells[1, 1].Value = "Year";
ws.Cells[1, 2].Value = "DebutSurvenance";
ws.Cells[1, 3].Value = "FinSurvenance";
ws.Cells[1, 4].Value = "Month";
ws.Cells[1, 5].Value = "Cumul";
int row = 2;
foreach (Cadencier c in cad)
{
ws.Cells[row, 1].Value = c.Year;
ws.Cells[row, 2].Value = c.DebutSurvenance;
ws.Cells[row, 3].Value = c.FinSurvenance;
ws.Cells[row, 4].Value = c.Month;
ws.Cells[row, 5].Value = c.Cumul;
row++;
}
return(pck);
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static void ImportCadencierForAssureur(string assureurName, string excelFilePath, bool firstRowAsColumnNames)
{
try
{
int year;
DateTime debutSurv;
DateTime finSurv;
int month;
double cumul;
//read Excel file into datatable
DataTable dt = G.ExcelToDataTable(excelFilePath, firstRowAsColumnNames);
// delete all rows in DB Tables with the specified assurName
Cadencier.DeleteCadencierWithSpecificAssureurName(assureurName);
foreach (DataRow row in dt.Rows)
{
//### validate => all fields must be specified
//codeActe = row[C.eExcelGroupsGaranties.CodeActe.ToString()].ToString();
if (!Int32.TryParse(row[C.eExcelCadencier.Year.ToString()].ToString(), out year))
{
throw new Exception("One of the provided 'Year' values is not valid for the Cadencier you are trying to import !");
}
if (!Int32.TryParse(row[C.eExcelCadencier.Month.ToString()].ToString(), out month))
{
throw new Exception("One of the provided 'Month' values is not valid for the Cadencier you are trying to import !");
}
if (!double.TryParse(row[C.eExcelCadencier.Cumul.ToString()].ToString(), out cumul))
{
throw new Exception("One of the provided 'Cumul' values is not valid for the Cadencier you are trying to import !");
}
if (!DateTime.TryParse(row[C.eExcelCadencier.DebutSurvenance.ToString()].ToString(), out debutSurv))
{
throw new Exception("One of the provided 'DebutSurvenance' values is not valid for the Cadencier you are trying to import !");
}
if (!DateTime.TryParse(row[C.eExcelCadencier.FinSurvenance.ToString()].ToString(), out finSurv))
{
throw new Exception("One of the provided 'FinSurvenance' values is not valid for the Cadencier you are trying to import !");
}
int id = Cadencier.InsertCadencier(new Cadencier
{
AssureurName = assureurName,
Year = year,
DebutSurvenance = debutSurv,
FinSurvenance = finSurv,
Month = month,
Cumul = cumul
});
}
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static void RecreateCadencier()
{
try
{
List <double?> lstSommePresta = new List <double?>();
double? res = 0;
double? cumulTotal = 0;
double? sommePresta = 0;
double? coeffPSAP = 0;
string assName = "";
//get Presta
List <CumulPresta> presta = PrestSante.CumulPrestaData();
List <int> years = presta.GroupBy(p => new { p.AnneeSoins })
.Select(g => g.Key.AnneeSoins)
.ToList();
if (presta.Any())
{
assName = presta.First().AssureurName;
}
//get last year
int maxYear = years.Max();
List <double> yearsCumul = new List <double>(years.Count);
foreach (int year in years)
{
yearsCumul.Add(0);
}
//delete Cad for specific year
Cadencier.DeleteCadencierForSpecificYear(maxYear);
//re-create Cad for that year
int maxMonth = presta.Max(p => p.MoisReglement);
for (int i = 1; i <= maxMonth; i++)
{
sommePresta = 0;
int yearCount = 0;
foreach (int year in years)
{
res = presta.Where(p => p.AnneeSoins == year && p.MoisReglement == i).Select(p => p.SommePresta).SingleOrDefault().HasValue ?
presta.Where(p => p.AnneeSoins == year && p.MoisReglement == i).Select(p => p.SommePresta).SingleOrDefault() : 0;
sommePresta += res.HasValue ? res.Value : 0;
yearsCumul[yearCount] += res.HasValue ? res.Value : 0;
yearCount++;
}
lstSommePresta.Add(sommePresta.HasValue ? sommePresta.Value : 0);
}
//calculate CumulTotal => somme des prestations
foreach (double annualTotal in yearsCumul)
{
cumulTotal += annualTotal;
}
//complete missing columns
double?cumPresta = 0;
int month = 1;
foreach (double sumPresta in lstSommePresta)
{
//presta moyenne %
if (cumulTotal != 0)
{
//presta cumulées %
cumPresta += sumPresta * 100 / cumulTotal;
}
//Coeff PSAP %
if (cumPresta != 0)
{
coeffPSAP = (100 - cumPresta) / cumPresta;
}
else
{
coeffPSAP = 0;
}
int id = Cadencier.InsertCadencier(new Cadencier
{
AssureurName = assName,
Year = maxYear,
DebutSurvenance = new DateTime(maxYear, 1, 1),
FinSurvenance = new DateTime(maxYear, 12, 31),
Month = month,
Cumul = Math.Round(coeffPSAP.Value, 4)
});
month++;
}
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
