public void CreateExcelPREVData(FileInfo fiExcelFile, string assurNameList, string parentCompanyNameList, string companyNameList, string contrNameList, DateTime debutPeriod, DateTime finPeriod,
string college, DateTime dateArret, CRPlanning crp, string crFilePathPPT, string crFilePath, C.eReportTemplateTypes templateType,
int yearsToCalc, bool calculateProvision)
{
DateTime debutNew;
DateTime finNew;
try
{
//Fill the DATA COT Sheets
ExcelSheetHandler.FillCotSheet(fiExcelFile, assurNameList, parentCompanyNameList, companyNameList, contrNameList, college, debutPeriod, finPeriod, dateArret, yearsToCalc, templateType);
//Fill the DATA PREV Sheet
ExcelSheetHandler.FillPrevSheet(fiExcelFile, assurNameList, parentCompanyNameList, companyNameList, contrNameList, college, debutPeriod, finPeriod, dateArret, yearsToCalc);
ExcelSheetHandler.FillDates(fiExcelFile, dateArret, debutPeriod, finPeriod, null, null, null, calculateProvision);
//Test - we no longer use Excel Macros
ExcelSheetHandler.FillOUI(fiExcelFile);
//### run Excel Macros
//RunExcelMacro(fiExcelFile.FullName, "PROVISIONCALCUL.PROVISIONCALCUL", false);
//RunExcelMacro(fiExcelFile.FullName, "FULL.FULL", false);
//RunPPTMacro(crFilePathPPT, crFilePath, "M2", false);
}
catch (Exception ex)
{
log.Error("CreateExcelPREVData" + ex.Message);
throw ex;
}
}
public void CreateExcelGLOBALData(FileInfo fiExcelFile, string companyList, string subsidList, DateTime debutPeriod, DateTime finPeriod,
DateTime dateArret, C.eReportTypes reportType, C.eReportTemplateTypes templateType)
{
if (templateType == C.eReportTemplateTypes.SANTE_GLOBAL)
{
ExcelSheetHandler.FillGlobalSheet(fiExcelFile, companyList, subsidList, debutPeriod, finPeriod, dateArret, reportType, TaxDef, TaxAct, TaxPer);
}
else if (templateType == C.eReportTemplateTypes.PREV_GLOBAL)
{
ExcelSheetHandler.FillGlobalSheetPrev(fiExcelFile, companyList, subsidList, debutPeriod, finPeriod, dateArret, reportType, TaxDef, TaxAct, TaxPer);
}
ExcelSheetHandler.FillDates(fiExcelFile, dateArret, debutPeriod, finPeriod, TaxDef, TaxAct, TaxPer, null);
ExcelSheetHandler.FillOUI(fiExcelFile);
}
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;
}
}