Exemple #1
0
        public void GetAggregatorsExcelInfo(string fileLocation)
        {
            //declare classes
            FileCorrect              ga  = new FileCorrect();
            OpenExcelFile            ga1 = new OpenExcelFile();
            ExcelRowListByRowValuecs ga2 = new ExcelRowListByRowValuecs();
            ExcelCleanUp             ga3 = new ExcelCleanUp();


            //open excel file and set xlworksheet to be used in retrievecolumnByRow

            ga.FileDateCorrect(fileLocation);

            var xlWorksheet = ga1.OpenExcel(fileLocation);

            // returns list of colum values based on search value.

            var rowList = ga2.RetrieveRowByRow(xlWorksheet, yesterday);

            ga3.CleanUp(fileLocation);
        }
        public void AddCompareTheMarket(string fileLocation)
        {
            //declare classes
            FileCorrect               cm  = new FileCorrect();
            OpenExcelFile             cm1 = new OpenExcelFile();
            ExcelColumnListByRowValue cm2 = new ExcelColumnListByRowValue();
            ExcelCleanUp              cm3 = new ExcelCleanUp();
            CTMunprotect              eu  = new CTMunprotect();



            cm.FileDateCorrect(fileLocation);

            //eu.UnprotectExcelFile(fileLocation);

            DailyPerformanceReportDbEntities1 db = new DailyPerformanceReportDbEntities1();

            var quoteSource  = "Compare the Market";
            var days         = db.GetEntryDates(quoteSource).FirstOrDefault().Value;
            var daysToGoBack = Convert.ToInt32(days);



            while (daysToGoBack > 0)
            {
                //var daysPast = DateTime.Now.AddDays(-i).ToString("ddd, dd MMM yyyy");

                do
                {
                    var daysPast   = DateTime.Now.AddDays(-daysToGoBack).ToString("dd/MM/yyyy");
                    var daysPastdb = DateTime.Now.AddDays(-daysToGoBack).Date;
                    --daysToGoBack;
                    AddCTMToDb(cm1, cm2, fileLocation, cm3, daysPast, daysPastdb);
                }while (daysToGoBack > 0);
            }
        }
        public void AddCTMToDb(OpenExcelFile cm1, ExcelColumnListByRowValue cm2, string fileLocation, ExcelCleanUp cm3, string daysPast, DateTime daysPastdb)
        {
            // open excel file
            Application xlApp       = new Application();
            Workbook    xlWorkbook  = xlApp.Workbooks.Open(@fileLocation);
            Worksheet   xlWorksheet = xlWorkbook.Sheets[1];
            Range       xlRange     = xlWorksheet.UsedRange;


            // returns list of colum values based on search value.


            var columnList = cm2.RetrieveColumnByRow(xlWorksheet, daysPast);


            var newlist = columnList[0];



            var CTMConsumerEnquiries       = newlist[7].ToString();
            var CTMPricesPresented         = newlist[8].ToString();
            var CTMPricesPresentedP1       = newlist[9].ToString();
            var CTMClickThroughPCTs        = newlist[11].ToString();
            var CTMTopQuotesPercentage     = Convert.ToString(Math.Round((Convert.ToDouble(newlist[21]) * 100), 2, MidpointRounding.ToEven));
            var CTMQuotesDeclinePercentage = Convert.ToString(Math.Round((Convert.ToDouble(newlist[15]) * 100), 2, MidpointRounding.ToEven));

            //add to database

            DailyPerformanceReportDbEntities1 db = new DailyPerformanceReportDbEntities1();


            DailyQuote newQuote    = new DailyQuote();
            var        QuoteSource = "Compare The Market";
            var        Date        = DateTime.Today.Date;


            newQuote.Date        = daysPastdb;
            newQuote.QuoteSource = QuoteSource;

            newQuote.QuoteType = "Consumer Enquiries";
            newQuote.Value     = CTMConsumerEnquiries;
            newQuote.Comments  = null;
            db.DailyQuotes.Add(newQuote);
            db.SaveChanges();

            newQuote.QuoteType = "Prices Presented";
            newQuote.Value     = CTMPricesPresented;
            newQuote.Comments  = null;
            db.DailyQuotes.Add(newQuote);
            db.SaveChanges();

            newQuote.QuoteType = "Prices Presented @P1";
            newQuote.Value     = CTMPricesPresentedP1;
            newQuote.Comments  = null;
            db.DailyQuotes.Add(newQuote);
            db.SaveChanges();

            newQuote.QuoteType = "Click Through (PCTs)";
            newQuote.Value     = CTMClickThroughPCTs;
            newQuote.Comments  = null;
            db.DailyQuotes.Add(newQuote);
            db.SaveChanges();

            newQuote.QuoteType = "Top Quotes %";
            newQuote.Value     = CTMTopQuotesPercentage;
            newQuote.Comments  = null;
            db.DailyQuotes.Add(newQuote);
            db.SaveChanges();


            newQuote.QuoteType = "Quotes Declined %";
            newQuote.Value     = CTMQuotesDeclinePercentage;
            newQuote.Comments  = null;
            db.DailyQuotes.Add(newQuote);

            db.SaveChanges();

            // close excel file

            GC.Collect();
            GC.WaitForPendingFinalizers();

            //release com objects to fully kill excel process from running in the background
            Marshal.ReleaseComObject(xlRange);
            Marshal.ReleaseComObject(xlWorksheet);

            //close and release
            xlWorkbook.Close(true, fileLocation, Missing.Value);
            Marshal.ReleaseComObject(xlWorkbook);

            //quit and release
            xlApp.Quit();
            Marshal.ReleaseComObject(xlApp);

            //Process[] processes = Process.GetProcessesByName("EXCEL");
            //foreach (Process p in processes)
            //{
            //    p.Kill();
            //}
        }