private void LoadListBox() { TheNumbersDataContext dc = new TheNumbersDataContext(); var years = dc.Movies.GroupBy(x => x.year).Select(y => y.Key); lstYear.DataSource = years; lstYear.Update(); }
public static void WriteToError(string _link, string _detail, int _year) { TheNumbersDataContext dc = new TheNumbersDataContext(); dc.Errors.InsertOnSubmit(new Error { detail = _detail, link = _link.Replace(@"http://www.the-numbers.com/", String.Empty), year = _year }); dc.SubmitChanges(); }
private void ExportData() { TheNumbersDataContext dc = new TheNumbersDataContext(); foreach (var item in lstYear.SelectedItems) { if (!Directory.Exists(mainpath + @"\" + item.ToString())) { Directory.CreateDirectory(mainpath + @"\" + item.ToString()); } foreach (var movie in dc.Movies.Where(x => x.year == Int32.Parse(item.ToString()))) { CreateExcel(dc, movie, mainpath + @"\" + item.ToString()); } } }
/// <summary> /// Write into log. /// </summary> /// <param name="status">Status of the program</param> /// <param name="desc">Default is ""</param> public static void WriteToLog(ProgramStatus status, string desc = "") { string log = ""; switch (status) { case ProgramStatus.Error: log = "ERROR - " + DateTime.Now; break; case ProgramStatus.End: log = "END - " + DateTime.Now; break; case ProgramStatus.Start: log = "START - " + DateTime.Now; break; case ProgramStatus.Success: log = "SUCCESS - " + DateTime.Now; break; default: break; } try { TheNumbersDataContext dc = new TheNumbersDataContext(); dc.Logs.InsertOnSubmit(new DataModel.Log { LogTime = DateTime.Now, Desc = log }); dc.SubmitChanges(); } catch { throw; } return; }
private void Redownload_Click(object sender, EventArgs e) { TheNumbersDataContext dc = new TheNumbersDataContext(); var errors = dc.Errors.DistinctBy(x => x.link); foreach (var error in errors) { dc.ExecuteCommand("TRUNCATE TABLE [dbo].[log]"); // clears the log first. SingleMovieLink tempsml = new SingleMovieLink(error.link, error.year); dc.Connection.Close(); tempsml.GetMovie(); if (dc.Connection.State.Equals(ConnectionState.Closed)) { dc.Connection.Open(); } if (!dc.IsThereAnyError()) { var tempdelete = dc.Errors.Where(err => err.link.Equals(error.link)); dc.Errors.DeleteAllOnSubmit(tempdelete); dc.SubmitChanges(); } } }
private void btnDownload_Click(object sender, EventArgs e) { TheNumbersDataContext dc = new TheNumbersDataContext(); dc.ExecuteCommand("TRUNCATE TABLE [dbo].[log]"); // clears the log first. Helper.WriteToLog(ProgramStatus.Start); if (rbAll.Checked) { // get all years listed List <String> years = new List <string>(); var yearListPage = new HtmlAgilityPack.HtmlDocument(); bool downloaded = false; int count = 0; while (!downloaded) { try { yearListPage.LoadHtml(Helper.GetHTML(@"http://www.the-numbers.com/movies/#tab=year")); downloaded = true; } catch { downloaded = false; count++; if (count >= 3) { Helper.WriteToLog(ProgramStatus.Error, "error loading the entire page"); } else { Task.Delay(5000); // add sleep for 5 sec to delay the fetch } } } // download all if (downloaded) { // get year list. var table = yearListPage.DocumentNode.Descendants("div").Single( o => o.HasAttributes && o.Attributes.Any(p => p.Name.Equals("id")) && o.Attributes["id"].Value.Equals("year")) .Descendants("table").FirstOrDefault(); var trs = table.Descendants("tr").Reverse().ToList(); trs.Remove(trs.Last()); foreach (var tr in trs) { years.Add(tr.Descendants("td").FirstOrDefault().Descendants("b") .FirstOrDefault().Descendants("a") .FirstOrDefault().InnerText.Trim()); } foreach (string year in years) { this.GetASingleYear(year); } MessageBox.Show("Whole database successfully stored to DB!"); } MessageBox.Show("All successfully stored to DB!"); } else { this.GetASingleYear(txtYear.Text); MessageBox.Show("Year " + txtYear.Text + " successfully stored to DB!"); } if (dc.Errors.Count() > 0) { MessageBox.Show("Scrapping completed with error. Please send the entire log from DB to [email protected]"); } Helper.WriteToLog(ProgramStatus.End); }
private void CreateExcel(TheNumbersDataContext dc, Movy movie, string path) { Excel.Application excelApp = new Excel.Application(); excelApp.Visible = false; Excel.Workbook wb = excelApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet summ = (Excel.Worksheet)wb.Sheets[1]; try { if (!Directory.Exists(path + @"\" + movie.Title)) { Directory.CreateDirectory(path + @"\" + movie.Title); } summ.Name = "@Summary"; summ.get_Range("A1", "A1").Value2 = "Title"; summ.get_Range("A2", "A2").Value2 = "Budget"; summ.get_Range("A3", "A3").Value2 = "MPAA Rating"; summ.get_Range("A4", "A4").Value2 = "Running Time"; summ.get_Range("A5", "A5").Value2 = "Franchise"; summ.get_Range("A6", "A6").Value2 = "Genre"; summ.get_Range("A7", "A7").Value2 = "Company"; summ.get_Range("A8", "A8").Value2 = "RT Critics"; summ.get_Range("A9", "A9").Value2 = "RT Audiences"; summ.get_Range("A10", "A10").Value2 = "Released on"; summ.get_Range("B1", "B1").Value2 = movie.Title; summ.get_Range("B2", "B2").Value2 = movie.Budget; summ.get_Range("B3", "B3").Value2 = movie.MPAARating; summ.get_Range("B4", "B4").Value2 = movie.RunningTime; summ.get_Range("B5", "B5").Value2 = movie.Franchise; summ.get_Range("B6", "B6").Value2 = movie.Genre; summ.get_Range("B7", "B7").Value2 = movie.Company; summ.get_Range("B8", "B8").Value2 = movie.RTCRating; summ.get_Range("B9", "B9").Value2 = movie.RTARating; int counter = 0; foreach (var reldate in dc.ReleaseDates.Where(o => o.MovieID == movie.ID)) { counter++; summ.get_Range("B" + (counter + 9), "B" + (counter + 9)).Value2 = reldate.ReleaseDate1 + reldate.Desc + " by " + reldate.Company; } Excel.Worksheet DBO = (Excel.Worksheet)excelApp.Worksheets.Add(); DBO.Name = @"BOX OFFICE"; DBO.get_Range("A1", "A1").Value2 = @"Rank"; DBO.get_Range("B1", "B1").Value2 = @"Gross"; DBO.get_Range("C1", "C1").Value2 = @"Theatre Count"; DBO.get_Range("D1", "D1").Value2 = @"Total Gross"; DBO.get_Range("E1", "E1").Value2 = @"Num Days"; DBO.get_Range("F1", "F1").Value2 = @"Date"; counter = 0; foreach (var BO in dc.DailyBOs.Where(o => o.MovieID == movie.ID)) { counter++; DBO.get_Range("A" + counter, "A" + counter).Value2 = BO.Rank; DBO.get_Range("B" + counter, "B" + counter).Value2 = BO.Gross; DBO.get_Range("C" + counter, "C" + counter).Value2 = BO.TheatersCount; DBO.get_Range("D" + counter, "D" + counter).Value2 = BO.TotalGross; DBO.get_Range("E" + counter, "E" + counter).Value2 = BO.NumDays; DBO.get_Range("F" + counter, "F" + counter).Value2 = BO.DateCounted; } Excel.Worksheet ROLE = (Excel.Worksheet)excelApp.Worksheets.Add(); ROLE.Name = @"Cast & Crew"; ROLE.get_Range("A1", "A1").Value2 = @"Role"; ROLE.get_Range("B1", "B1").Value2 = @"Name"; counter = 0; foreach (var Ro in dc.Roles.Where(o => o.IDMovie == movie.ID)) { counter++; ROLE.get_Range("A" + counter, "A" + counter).Value2 = Ro.Role1; ROLE.get_Range("B" + counter, "B" + counter).Value2 = Ro.Name; } Excel.Worksheet VidSales = (Excel.Worksheet)excelApp.Worksheets.Add(); VidSales.Name = @"Video Sales"; VidSales.get_Range("A1", "A1").Value2 = @"Rank"; VidSales.get_Range("B1", "B1").Value2 = @"Units"; VidSales.get_Range("C1", "C1").Value2 = @"Spending"; VidSales.get_Range("D1", "D1").Value2 = @"Week"; VidSales.get_Range("E1", "E1").Value2 = @"Total Spending"; VidSales.get_Range("F1", "F1").Value2 = @"Type"; VidSales.get_Range("G1", "G1").Value2 = @"Date"; counter = 0; foreach (var VS in dc.Videos.Where(o => o.MovieID == movie.ID)) { counter++; VidSales.get_Range("A" + counter, "A" + counter).Value2 = VS.Rank; VidSales.get_Range("B" + counter, "B" + counter).Value2 = VS.Units; VidSales.get_Range("C" + counter, "C" + counter).Value2 = VS.Spending; VidSales.get_Range("D" + counter, "D" + counter).Value2 = VS.Week; VidSales.get_Range("E" + counter, "E" + counter).Value2 = VS.TotalSpending; VidSales.get_Range("F" + counter, "F" + counter).Value2 = VS.Type; VidSales.get_Range("G" + counter, "G" + counter).Value2 = VS.DateCounted; } wb.SaveAs(path + @"\" + movie.Title + @"\" + movie.ID + "-" + movie.Title + @".xls"); wb.Close(); excelApp.Quit(); } catch (Exception ex) { MessageBox.Show(ex.StackTrace); } finally { excelApp.Quit(); } }