public void UpdateDBRecord()
{
try
{
if (Id == 0)
{
throw new Exception("Id cannot be 0 - Source: BLCompteResultat::UpdateDBRecord");
}
MapDALObject();
CompteResult.Update(CRDALObject);
//Update Planning information
//### update Planning only if there is only one single Planning available - otherwise we don't know which Planning shoul dbe updated
//we also need to retrieve the Id of that Planning
List <CRPlanning> oldPls = CRPlanning.GetCRPlannngForComptesResultat(Id);
if (oldPls.Count == 1)
{
CRPlanning newPl = CRPlannings[0];
newPl.CRId = Id;
newPl.Id = oldPls[0].Id;
CRPlanning.Update(newPl);
}
}
catch (Exception ex)
{
log.Error("UpdateDBRecord :: " + ex.Message);
throw ex;
}
}
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 int AddDBRecord()
{
try
{
int crId = 0;
MapDALObject();
crId = CompteResult.Insert(CRDALObject);
//add the planning information
foreach (CRPlanning crp in CRPlannings)
{
crp.CRId = crId;
CRPlanning.Insert(crp);
}
return(crId);
}
catch (Exception ex)
{
log.Error("AddDBRecord :: " + 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;
}
}
private void CreateExcelDoc(int CRId, CRPlanning crp)
{
try
{
string templateFileName;
C.eReportTemplateTypes templateType = C.eReportTemplateTypes.SANTE;
string templateFileNamePPT;
string templateFilePath;
string templateFilePathPPT;
string prevProvisionFile;
string crFileName;
string crFileNamePPT;
string crFilePath;
string crFilePathPPT;
int yearsToCalc = 1;
bool reportWithOption = false;
//new name of the Exel file
crFileName = Name + ".xlsm";
crFileNamePPT = Name + ".pptm";
//copy & rename the appropriate Template File from the ReportTemplates folder to the ExcelCR folder
if (ReportLevelId.HasValue)
{
templateFileName = ReportTemplate.GetTemplateFileNameForId(ReportLevelId.Value);
templateType = ReportTemplate.GetTemplateTypeForId(ReportLevelId.Value);
templateFileNamePPT = templateFileName.Replace(".xlsm", ".pptm");
yearsToCalc = ReportTemplate.GetYearsToCalcForId(ReportLevelId.Value);
reportWithOption = ReportTemplate.GetWithOptionFlag(ReportLevelId.Value);
}
else
{
throw new Exception("No report template is associated with the specified CR!");
}
//templateFileName = C.ExcelTemplateGlobalCompany;
//if (ReportType == C.eReportTypes.GlobalEnt || ReportType == C.eReportTypes.GlobalSubsid)
//{
// if (IsPrev)
// {
// templateFileName = C.ExcelTemplateGlobalCompanyPrevoyance;
// }
// else
// {
// templateFileName = C.ExcelTemplateGlobalCompanySante;
// }
//}
templateFilePath = Path.Combine(ExcelTemplatePath, templateFileName);
templateFilePathPPT = Path.Combine(ExcelTemplatePath, templateFileNamePPT);
prevProvisionFile = Path.Combine(ExcelTemplatePath, C.cPrevProvisionFileName);
crFilePath = Path.Combine(ExcelCRPath, crFileName);
crFilePathPPT = Path.Combine(ExcelCRPath, crFileNamePPT);
string newPrevProvPath = Path.Combine(ExcelCRPath, C.cPrevProvisionFileName);
//### display warning if the destination file already exists or should we just overwrtie the file ???
if (File.Exists(templateFilePath))
{
File.Copy(templateFilePath, crFilePath, true);
}
else
{
throw new Exception("The specified Excel template file does not exist: " + templateFileName);
}
if (ReportType == C.eReportTypes.Standard)
{
if (File.Exists(templateFilePathPPT))
{
File.Copy(templateFilePathPPT, crFilePathPPT, true);
}
else
{
throw new Exception("The specified PPT template file does not exist: " + templateFileNamePPT);
}
}
if (templateType == C.eReportTemplateTypes.PREV)
{
if (File.Exists(prevProvisionFile))
{
File.Copy(prevProvisionFile, newPrevProvPath, true);
}
else
{
throw new Exception("The 'PrevProvision.xlsm' file does not exist in the ReportTemplates folder!");
}
}
if (CRExists)
{
ReportFile.Delete(CRId, C.eReportFileTypes.Excel.ToString());
}
ReportFile repFile = new ReportFile {
CRId = CRId, FileType = C.eReportFileTypes.Excel.ToString(), FileLocation = crFileName
};
int repId = ReportFile.Insert(repFile);
ReportFiles.Add(repFile);
//modify the excel file => update various Data Sheets
FileInfo fiExcelFile = new FileInfo(crFilePath);
string college;
if (CollegeId.HasValue)
{
college = College.GetCollegeNameForId(CollegeId.Value);
}
else
{
throw new Exception("CreateExcelDoc:: CollegeId does not contain any value");
}
//the Presta Data will be required for several methods, so we extract it only once here
DateTime debutPeriod;
DateTime finPeriod;
DateTime dateArret;
//### change debutPeriode if we are using Template 1 => check DB value in ReportFile Table
//DateTime dtNewDebut = null;
if (crp.DebutPeriode.HasValue)
{
debutPeriod = crp.DebutPeriode.Value;
}
else
{
throw new Exception("CreateExcelDoc:: No date was provided for 'DebutPériode'!");
}
if (crp.FinPeriode.HasValue)
{
finPeriod = crp.FinPeriode.Value;
}
else
{
throw new Exception("CreateExcelDoc:: No date was provided for 'FinPériode'!");
}
if (crp.DateArret.HasValue)
{
dateArret = crp.DateArret.Value;
}
else
{
throw new Exception("CreateExcelDoc:: No date was provided for 'DateArret'!");
}
if (ReportType == C.eReportTypes.Standard)
{
if (templateType == C.eReportTemplateTypes.SANTE)
{
CreateExcelSANTEData(fiExcelFile, AssurNames, ParentCompanyNames, SubsidNames, ContractNames, debutPeriod, finPeriod, yearsToCalc, college,
dateArret, crp, crFilePathPPT, crFilePath, templateType, reportWithOption);
}
else if (templateType == C.eReportTemplateTypes.PREV)
{
CreateExcelPREVData(fiExcelFile, AssurNames, ParentCompanyNames, SubsidNames, ContractNames, debutPeriod, finPeriod, college,
dateArret, crp, crFilePathPPT, crFilePath, templateType, yearsToCalc, CalculateProvision);
}
}
else
{
CreateExcelGLOBALData(fiExcelFile, ParentCompanyNames, SubsidNames, debutPeriod, finPeriod, dateArret, ReportType, templateType);
}
}
catch (Exception ex)
{
log.Error("CreateExcelDoc :: " + ex.Message);
throw ex;
}
}
