public void AddGoCompare(string fileLocationHome, string fileLocationQuote)
{
//declare classes
FileCorrect cm = new FileCorrect();
OpenExcelDB openDB = new OpenExcelDB();
ExcelUnprotect eu = new ExcelUnprotect();
eu.UnprotectExcelFile(fileLocationHome);
eu.UnprotectExcelFile(fileLocationQuote);
cm.FileDateCorrect(fileLocationHome);
cm.FileDateCorrect(fileLocationQuote);
var ConnectionString = openDB.ConnectionString(fileLocationHome);
var ConnectionStringTwo = openDB.ConnectionString(fileLocationQuote);
DailyPerformanceReportDbEntities1 db = new DailyPerformanceReportDbEntities1();
var quoteSource = "Go Compare";
var days = db.GetEntryDates(quoteSource).FirstOrDefault().Value;
var daysToGoBack = Convert.ToInt32(days);
do
//var daysPast = DateTime.Now.AddDays(-i).ToString();
{
var daysPast = Convert.ToString(DateTime.Now.AddDays(-days).Date.ToOADate());
var daysPastAlt = DateTime.Now.AddDays(-days).ToString("ddd, dd MMM yyyy");
DateTime daysPastdb = DateTime.Now.AddDays(-days).Date;
--days;
AddGoCompareToDb(ConnectionString, ConnectionStringTwo, daysPast, daysPastAlt, daysPastdb);
}while (days > 0);
}
public void AddMoneySupermarket(string fileLocation)
{
//declare classes
FileCorrect cm = new FileCorrect();
OpenExcelDB openDB = new OpenExcelDB();
cm.FileDateCorrect(fileLocation);
var ConnectionString = openDB.ConnectionString(fileLocation);
DailyPerformanceReportDbEntities1 db = new DailyPerformanceReportDbEntities1();
var quoteSource = "Money Supermarket";
var days = db.GetEntryDates(quoteSource).FirstOrDefault().Value;
var daysToGoBack = Convert.ToInt32(days);
do
//var daysPast = DateTime.Now.AddDays(-i).ToString("ddd, dd MMM yyyy");
{
var daysPast = DateTime.Now.AddDays(-daysToGoBack).ToString("yyyy.MM.dd");
DateTime daysPastdb = DateTime.Now.AddDays(-daysToGoBack).Date;
--daysToGoBack;
AddMSToDb(daysPast, daysPastdb, ConnectionString);
}while (daysToGoBack > 1);
}
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 AddGoCompareToDb(string ConnectionString, string ConnectionStringTwo, string daysPast, string daysPastAlt, DateTime daysPastdb)
{
//Get values from GoCompare Home
using (OleDbConnection objcn = new OleDbConnection {
ConnectionString = ConnectionString
})
{
objcn.Open();
//reads all data and stores in datatable
DataTable dtexcel = new DataTable();
OleDbDataAdapter adp = new OleDbDataAdapter("Select * From[QuotabilityPerformance-Datatabl$]", objcn);
adp.Fill(dtexcel);
dtexcel.Columns[1].ColumnName = "Date";
//Searches for row via date
var res = from row in dtexcel.AsEnumerable()
where (row.Field <string>("Date") == daysPastAlt) || (row.Field <string>("Date") == daysPast)
select row;
//stores into Array
var newres = res.ToArray();
var date = newres[0];
// array values stores as variables to add to Database.
var GCTotalQuotes = date.ItemArray[2].ToString();
var GCPartnerQuotes = date.ItemArray[4].ToString();
var GCTopQuote = date.ItemArray[13].ToString();
//var GCTopQuotesHIPercentage = Convert.ToString(Math.Round((Convert.ToDouble(date.ItemArray[14]) * 100), 2, MidpointRounding.ToEven));
//var GCQuotesBlockedPercentage = Convert.ToString(Math.Round((Convert.ToDouble(date.ItemArray[7]) * 100), 2, MidpointRounding.ToEven));
var GCTopQuotesHIPercentage = Convert.ToString(date.ItemArray[14]);
var GCQuotesBlockedPercentage = Convert.ToString(date.ItemArray[7]);
DailyPerformanceReportDbEntities1 db = new DailyPerformanceReportDbEntities1();
DailyQuote newQuote = new DailyQuote();
var QuoteSource = "Go Compare";
newQuote.Date = daysPastdb;
newQuote.QuoteSource = QuoteSource;
newQuote.QuoteType = "Total Quotes";
newQuote.Value = GCTotalQuotes;
newQuote.Comments = null;
db.DailyQuotes.Add(newQuote);
db.SaveChanges();
newQuote.QuoteType = "Partner Quotes";
newQuote.Value = GCPartnerQuotes;
newQuote.Comments = null;
db.DailyQuotes.Add(newQuote);
db.SaveChanges();
newQuote.QuoteType = "Top Quote";
newQuote.Value = GCTopQuote;
newQuote.Comments = null;
db.DailyQuotes.Add(newQuote);
db.SaveChanges();
newQuote.QuoteType = "Top Quotes HI %";
newQuote.Value = GCTopQuotesHIPercentage;
newQuote.Comments = null;
db.DailyQuotes.Add(newQuote);
db.SaveChanges();
newQuote.QuoteType = "Quotes Blocked %";
newQuote.Value = GCQuotesBlockedPercentage;
newQuote.Comments = null;
db.DailyQuotes.Add(newQuote);
db.SaveChanges();
}
//Get Values from GoCompare Quote
using (OleDbConnection objcn = new OleDbConnection {
ConnectionString = ConnectionStringTwo
})
{
objcn.Open();
//reads all data and stores in datatable
DataTable dtexcel = new DataTable();
OleDbDataAdapter adp = new OleDbDataAdapter("Select * From[QuotabilityPerformance-Datatabl$]", objcn);
adp.Fill(dtexcel);
dtexcel.Columns[1].ColumnName = "Date";
//Searches for row via date
var res = from row in dtexcel.AsEnumerable()
where (row.Field <string>("Date") == daysPastAlt) || (row.Field <string>("Date") == daysPast)
select row;
//stores into Array
var newres = res.ToArray();
var date = newres[0];
// array values stores as variables to add to Database.
var GCTopQuotesQPercentage = Convert.ToString(date.ItemArray[14]);
//var GCTopQuotesQPercentage = Convert.ToString(Math.Round((Convert.ToDouble(date.ItemArray[14]) * 100), 2, MidpointRounding.ToEven));
//==== ADD TO DATABASE BELOW ======================== //
DailyPerformanceReportDbEntities1 db = new DailyPerformanceReportDbEntities1();
DailyQuote newQuote = new DailyQuote();
var QuoteSource = "Go Compare";
var Date = daysPastdb;
newQuote.Date = Date;
newQuote.QuoteSource = QuoteSource;
newQuote.QuoteType = "Top Quotes Q %";
newQuote.Value = GCTopQuotesQPercentage.ToString();
newQuote.Comments = null;
db.DailyQuotes.Add(newQuote);
db.SaveChanges();
}
}
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();
//}
}
public void AddMSToDb(string daysPast, DateTime daysPastDb, string ConnectionString)
{
using (OleDbConnection objcn = new OleDbConnection {
ConnectionString = ConnectionString
})
{
objcn.Open();
//reads all data and stores in datatable
DataTable dtexcel = new DataTable();
OleDbDataAdapter adp = new OleDbDataAdapter("Select * From[$Data]", objcn);
adp.Fill(dtexcel);
dtexcel.Columns[0].ColumnName = "Date";
DailyPerformanceReportDbEntities1 db = new DailyPerformanceReportDbEntities1();
var res = from row in dtexcel.AsEnumerable()
where row.Field <string>("Date") == daysPast
select row;
if (res == null || !res.Any())
{
throw new ArgumentNullException("value", "not");
}
var newres = res.ToArray();
var date = newres[0];
var MSTopQuotes = date.ItemArray[8].ToString();
var MSTotalQueries = date.ItemArray[3].ToString();
var MSTotalQuotesReturned = date.ItemArray[4].ToString();
var MSTotalClicks = date.ItemArray[5].ToString();
var MSProviderQuoteRatePercentage = date.ItemArray[6].ToString();
var MSFilteredQuotes = date.ItemArray[7].ToString();
DailyQuote newQuote = new DailyQuote();
var QuoteSource = "Money Supermarket";
newQuote.Date = daysPastDb;
newQuote.QuoteSource = QuoteSource;
newQuote.QuoteType = "Total Queries";
newQuote.Value = MSTotalQueries;
newQuote.Comments = null;
db.DailyQuotes.Add(newQuote);
db.SaveChanges();
newQuote.QuoteType = "Total Quotes Returned";
newQuote.Value = MSTotalQuotesReturned;
newQuote.Comments = null;
db.DailyQuotes.Add(newQuote);
db.SaveChanges();
newQuote.QuoteType = "Total Clicks";
newQuote.Value = MSTotalClicks;
newQuote.Comments = null;
db.DailyQuotes.Add(newQuote);
db.SaveChanges();
newQuote.QuoteType = "Provider Quote Rate %";
newQuote.Value = MSProviderQuoteRatePercentage;
newQuote.Comments = null;
db.DailyQuotes.Add(newQuote);
db.SaveChanges();
newQuote.QuoteType = "Filtered Quotes";
newQuote.Value = MSFilteredQuotes;
newQuote.Comments = null;
db.DailyQuotes.Add(newQuote);
db.SaveChanges();
newQuote.QuoteType = "Top Quotes";
newQuote.Value = MSTopQuotes;
newQuote.Comments = null;
db.DailyQuotes.Add(newQuote);
db.SaveChanges();
}
}