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();
}
}
