public static void RecreateGroupsGarantiesSanteFromPresta()
{
try
{
//truncate table GroupGarantySante
GroupGarantySante.TruncateTable();
//get data from PrestSante
List <GroupesGarantiesSante> GroupGarantyList = PrestSante.GetGroupGarantyList();
//add data to GroupGarantySante
// ### Paramètres par défaut
foreach (GroupesGarantiesSante item in GroupGarantyList)
{
int id = GroupGarantySante.InsertGroupGaranty(new GroupGarantySante
{
AssureurName = item.AssureurName,
GroupName = item.GroupName,
GarantyName = item.GarantyName,
CodeActe = item.CodeActe,
OrderNumber = 1
});
}
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static void RecreateExperienceFromPresta()
{
try
{
//truncate table Experience
C_TempExpData.TruncateTable();
//get data from PrestSante
List <PrestSante> PrestaData = PrestSante.GetPrestations();
//add data to _TempExpData
List <PrestSante> prestaDataNormalized = BLCompteResultat.NormalizeGroupGarantyLabelsInPrestaTable(PrestaData);
List <ExcelPrestaSheet> excelPrestDataLarge = ExcelSheetHandler.GenerateModifiedPrestData(prestaDataNormalized);
//save data to table: _TempExpData
foreach (ExcelPrestaSheet item in excelPrestDataLarge)
{
int id = C_TempExpData.InsertExp(new C_TempExpData
{
ImportId = item.ImportId,
AssureurName = item.AssureurName,
Au = item.DateVision.HasValue ? item.DateVision.Value : DateTime.MinValue,
Contrat = item.ContractId,
CodCol = item.CodeCol,
AnneeExp = item.DateSoins.HasValue ? item.DateSoins.Value.Year : 0,
LibActe = item.GarantyName,
LibFam = item.GroupName,
TypeCas = item.CAS,
NombreActe = item.NombreActe,
Fraisreel = item.FraisReel,
Rembss = item.RembSS,
RembAnnexe = item.RembAnnexe,
RembNous = item.RembNous,
Reseau = item.Reseau,
MinFr = item.MinFR,
MaxFr = item.MaxFR,
MinNous = item.MinNous,
MaxNous = item.MaxNous
});
}
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public void CreateExcelSANTEData(FileInfo fiExcelFile, string assurNameList, string parentCompanyNameList, string companyNameList, string contrNameList, DateTime debutPeriod, DateTime finPeriod,
int yearsToCalc, string college, DateTime dateArret, CRPlanning crp, string crFilePathPPT, string crFilePath, C.eReportTemplateTypes templateType, bool reportWithOption)
{
DateTime debutNew;
DateTime finNew;
try
{
//Fill the various Excel Sheets
//Stopwatch stopWatch = new Stopwatch();
//string elapsedTime = "";
//TimeSpan ts = new TimeSpan();
//stopWatch.Start();
ExcelSheetHandler.FillDemoSheet(fiExcelFile, assurNameList, parentCompanyNameList, companyNameList, contrNameList, debutPeriod, finPeriod, dateArret, yearsToCalc, reportWithOption);
//stopWatch.Stop();
//ts = stopWatch.Elapsed;
//elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10);
//Debug.WriteLine("Demo: " + elapsedTime);
//stopWatch.Start();
ExcelSheetHandler.FillCotSheet(fiExcelFile, assurNameList, parentCompanyNameList, companyNameList, contrNameList, college, debutPeriod, finPeriod, dateArret, yearsToCalc, templateType);
//stopWatch.Stop();
//ts = stopWatch.Elapsed;
//elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10);
//Debug.WriteLine("Cot: " + elapsedTime);
//*************************** START PRESTA DATA ******************************************
#region PRESTA DATA SHEET
//truncate TempPresta Table
//C_TempPrestSante.TruncateTable();
List <string> contrList = Regex.Split(contrNameList, C.cVALSEP).ToList();
List <string> parentCompanyList = Regex.Split(parentCompanyNameList, C.cVALSEP).ToList();
List <string> companyList = Regex.Split(companyNameList, C.cVALSEP).ToList();
List <string> assurList = Regex.Split(assurNameList, C.cVALSEP).ToList();
//old
//List<PrestSante> myPrestData = PrestSante.GetPrestationsForContracts(contrList, college, debutPeriod, finPeriod);
//update group & garanty labels in Presta Data
//List<PrestSante> myPrestDataNormalized = NormalizeGroupGarantyLabelsInPrestaTable(myPrestData);
//ExcelSheetHandler.FillPrestSheet(fiExcelFile, crp, myPrestDataNormalized);
List <PrestSante> myPrestaDataNormalized = new List <PrestSante>();
List <PrestSante> prestaDataNormalizedFirstYear = new List <PrestSante>();
List <PrestSante> yearPrestaData;
//certain report templates will require data for more than 1 year, take this into account
int years = 0;
//for (int years = 0; years < yearsToCalc; years++)
//{
debutNew = new DateTime(debutPeriod.Year - years, debutPeriod.Month, debutPeriod.Day);
finNew = new DateTime(finPeriod.Year - years, finPeriod.Month, finPeriod.Day);
yearPrestaData = PrestSante.GetPrestationsForContracts(assurList, parentCompanyList, companyList, contrList, college, debutNew, finNew, dateArret);
List <PrestSante> yearPrestaDataNormalized = NormalizeGroupGarantyLabelsInPrestaTable(yearPrestaData);
//the following is required for FillQuartileSheet & FillAffichageSheet
if (years == 0)
{
prestaDataNormalizedFirstYear = yearPrestaDataNormalized;
}
myPrestaDataNormalized.AddRange(yearPrestaDataNormalized);
//}
// this is for Report Templates with Option Flag get required Presta Data & fill Temp Table
int mycnt = 0;
if (reportWithOption)
{
foreach (PrestSante ps in myPrestaDataNormalized)
{
mycnt++;
string benef = ps.Beneficiaire;
string contr = ps.ContractId;
bool isOption = false;
bool basefound = false;
bool optionfound = false;
if (!string.IsNullOrEmpty(ps.WithOption))
{
if (ps.WithOption.Trim().ToUpper() == "TRUE")
{
isOption = true;
}
}
if (isOption)
{
ps.BO1 = "OPTION";
ps.BO2 = "OPTION";
optionfound = true;
//if we find an option first, both BO's will be OPTION and we can stop here (continue loop with next element)
continue;
}
else
{
ps.BO1 = "BASE";
ps.BO2 = "BASE";
basefound = true;
}
//var x = myPrestaDataNormalized.Where(p => p.Beneficiaire == benef && p.WithOption.Trim().ToUpper() == "TRUE");
//if (x.Any())
//{
// ps.BO1 = "BASEOPTION";
// ps.BO2 = "OPTION";
// continue;
//}
if (true)
{
foreach (PrestSante ps2 in myPrestaDataNormalized)
{
if (ps2.Beneficiaire == benef)
{
//isOption = ps2.WithOption.HasValue ? ps2.WithOption.Value : false;
isOption = false;
if (!string.IsNullOrEmpty(ps2.WithOption))
{
if (ps2.WithOption.Trim().ToUpper() == "TRUE")
{
isOption = true;
}
}
if (basefound && isOption)
{
ps.BO1 = "BASEOPTION";
ps.BO2 = "OPTION";
break;
}
//this is no longer required - we will never get here
else if (optionfound && !isOption)
{
ps.BO1 = "BASEOPTION";
ps.BO2 = "OPTION";
basefound = true;
break;
}
}
}
} // end of : if(false) ...
}
}
ExcelSheetHandler.FillPrestSheet(fiExcelFile, crp, myPrestaDataNormalized, reportWithOption);
#endregion
//*************************** END PRESTA DATA ******************************************
//*************************** START EXPERIENCE DATA******************************************
//### //this is a temporary solution => get data from table _TempExpData and send them to excel
ExcelSheetHandler.FillExperienceSheet2(fiExcelFile, debutPeriod, finPeriod);
#region OLD EXPERIENCE DATA
if (false)
{
//iterate all Group Garanty Pairs
//THERE IS NO NEED TO ITERATE OVER ALL GARANTIES
//var uniqueGGList = myPrestDataNormalized.OrderBy(p => p.GroupName).GroupBy(p => new { p.GroupName, p.GarantyName }).ToList();
//if (uniqueGGList.Any())
//{
// foreach (var elem in uniqueGGList)
// {
// //newRow["FAMILLE"] = elem.First().GroupName;
// //newRow["ACTE"] = elem.First().GarantyName;
// }
//}
List <PrestSante> myPrestDataExp = new List <PrestSante>();
List <PrestSante> yearPrestaExpData;
//###old
//List<PrestSante> myPrestDataExp = PrestSante.GetPrestationsForContracts(contrListExp, college, debutPeriod, finPeriod);
//List<PrestSante> myPrestDataExpNormalized = NormalizeGroupGarantyLabelsInPrestaTable(myPrestDataExp);
//certain report templates will require data for more than 1 year, take this into account
//for (int yars = 0; yars < yearsToCalc; yars++)
//for (int yars = 1; yars < 2; yars++) {
//get Experience Data - with a different contract list
//### what contracts do we need? - regional, country, all...
//### if we take all the contracts, we get an outofmemory exception => therefor, we manually create a list of contractIds
//that returns only a certain number of prestations
//string contrNameListExp = Contract.GetAllContractNames();
//List<string> contrListExp = Regex.Split(contrNameListExp, C.cVALSEP).ToList();
//contrListExp = contrListExp.Take(300).ToList();
int yars = 1;
int indx = 0;
List <PrestSanteContrIdCount> contrIdCount = PrestSante.GetContractIdCount();
debutNew = new DateTime(debutPeriod.Year - yars, debutPeriod.Month, debutPeriod.Day);
finNew = new DateTime(finPeriod.Year - yars, finPeriod.Month, finPeriod.Day);
for (int j = 1; j <= C.maxPrestaIterations; j++)
{
List <string> contrListExp = GetContractIds(contrIdCount, ref indx);
indx++;
if (contrListExp == null)
{
break;
}
yearPrestaExpData = PrestSante.GetPrestationsForContracts(assurList, parentCompanyList, companyList, contrListExp, college, debutNew, finNew, dateArret);
List <PrestSante> myPrestDataExpNormalized = NormalizeGroupGarantyLabelsInPrestaTable(yearPrestaExpData);
myPrestDataExp.AddRange(myPrestDataExpNormalized);
//Debug.WriteLine("Number of iterations: " + j.ToString());
}
ExcelSheetHandler.FillExperienceSheet(fiExcelFile, crp, myPrestDataExp);
}
#endregion
//*************************** END EXPERIENCE DATA ******************************************
ExcelSheetHandler.FillProvisionSheet(fiExcelFile, crp, myPrestaDataNormalized);
ExcelSheetHandler.FillQuartileSheet(fiExcelFile, prestaDataNormalizedFirstYear);
//this is no longer required
ExcelSheetHandler.FillAffichageSheet(fiExcelFile, assurList[0]);
ExcelSheetHandler.FillDates(fiExcelFile, dateArret, debutPeriod, finPeriod, TaxDef, TaxAct, TaxPer, null);
//### do we need to calculate data and add them to the ReportData Table ???
//###Test - we no longer use Excel Macros
ExcelSheetHandler.FillOUI(fiExcelFile);
//### run Excel Macro
//RunExcelMacro(fiExcelFile.FullName, "FULL.FULL", false);
//RunPPTMacro(crFilePathPPT, crFilePath, "M2", false);
//### for testing
//System.Diagnostics.Process.Start(crFilePath);
//System.Diagnostics.Process.Start(crFilePathPPT);
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static ExcelPackage ExportCadencier()
{
try
{
int colCounter = 2;
double cumulTotal = 0;
double? sommePresta = 0;
double? res = 0;
List <CumulPresta> presta = PrestSante.CumulPrestaData();
List <int> years = presta.GroupBy(p => new { p.AnneeSoins })
.Select(g => g.Key.AnneeSoins)
.ToList();
List <double> yearsCumul = new List <double>(years.Count);
foreach (int year in years)
{
yearsCumul.Add(0);
}
int maxMonth = presta.Max(p => p.MoisReglement);
List <double> lstSommePresta = new List <double>();
ExcelPackage pck = new ExcelPackage();
var ws = pck.Workbook.Worksheets.Add("PRESTATIONS");
//write the header
ws.Cells[1, 1].Value = "Mois de règlement";
foreach (int year in years)
{
ws.Cells[1, colCounter].Value = year.ToString();
colCounter++;
}
ws.Cells[1, colCounter++].Value = "Somme des prestations";
ws.Column(colCounter).Style.Numberformat.Format = "#0\\.00%";
ws.Cells[1, colCounter++].Value = "Prestations moyenne";
ws.Column(colCounter).Style.Numberformat.Format = "#0\\.00%";
ws.Cells[1, colCounter++].Value = "Prestations cumulées";
ws.Column(colCounter).Style.Numberformat.Format = "#0\\.00%";
ws.Cells[1, colCounter++].Value = "Taux PSAP";
ws.Cells[1, colCounter++].Value = "Coefficient PSAP";
int row = 2;
for (int i = 1; i <= maxMonth; i++)
{
colCounter = 2;
sommePresta = 0;
ws.Cells[row, 1].Value = i.ToString();
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;
ws.Cells[row, colCounter].Value = res;
sommePresta += res;
yearsCumul[yearCount] += res.HasValue ? res.Value : 0;
yearCount++;
colCounter++;
}
//Somme Presta
ws.Cells[row, colCounter++].Value = sommePresta;
lstSommePresta.Add(sommePresta.HasValue ? sommePresta.Value : 0);
row++;
}
//calculate CumulTotal => somme des prestations && write the last line => cumul
colCounter = 2;
foreach (double annualTotal in yearsCumul)
{
ws.Cells[row, colCounter].Value = annualTotal;
cumulTotal += annualTotal;
colCounter++;
}
ws.Cells[row, colCounter++].Value = cumulTotal;
ws.Cells[row, colCounter].Value = 100;
//complete missing columns
row = 2;
double cumPresta = 0;
foreach (double sumPresta in lstSommePresta)
{
//presta moyenne %
if (cumulTotal != 0)
{
ws.Cells[row, colCounter].Value = sumPresta * 100 / cumulTotal;
//presta cumulées %
cumPresta += sumPresta * 100 / cumulTotal;
ws.Cells[row, colCounter + 1].Value = cumPresta;
}
else
{
ws.Cells[row, colCounter].Value = 0;
ws.Cells[row, colCounter + 1].Value = 0;
}
//Taux PSAP %
ws.Cells[row, colCounter + 2].Value = 100 - cumPresta;
//Coeff PSAP %
if (cumPresta != 0)
{
ws.Cells[row, colCounter + 3].Value = (100 - cumPresta) / cumPresta;
}
else
{
ws.Cells[row, colCounter + 3].Value = 0;
}
row++;
}
//Format table => first line bold
ws.Row(1).Style.Font.Bold = true;
ws.Row(1).Style.Font.Size = 12;
//last column green
for (int i = 1; i <= maxMonth + 1; i++)
{
ws.Cells[i, colCounter + 3].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells[i, colCounter + 3].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Green);
ws.Cells[i, colCounter + 3].Style.Numberformat.Format = "0.0000";
}
//years in header, background yellow
for (int i = 2; i <= years.Count + 1; i++)
{
ws.Cells[1, i].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells[1, i].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Yellow);
ws.Cells[maxMonth + 2, i].Style.Numberformat.Format = "0";
}
ws.Cells[maxMonth + 2, years.Count + 2].Style.Numberformat.Format = "0";
//last line (totals): bold
ws.Cells[maxMonth + 2, 1].Value = "Cumul";
ws.Row(maxMonth + 2).Style.Font.Bold = true;
ws.Row(maxMonth + 2).Style.Font.Size = 12;
//autofit column width
for (int i = 1; i <= colCounter + 3; i++)
{
ws.Column(i).AutoFit();
}
return(pck);
}
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;
}
}
