Ejemplo n.º 1
0
        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;
            }
        }
Ejemplo n.º 2
0
        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;
            }
        }
Ejemplo n.º 4
0
        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;
            }
        }