public ActionResult Index(Report1 report1) { if (ModelState.IsValid) { pubsEntities2 db = new pubsEntities2(); string query; IEnumerable <authors> results; SqlParameter number_of_sales = new SqlParameter("number_of_sales", report1.X); SqlParameter first_date = new SqlParameter("first_date", report1.From_time); SqlParameter second_date = new SqlParameter("second_date", report1.To_time); if (!report1.X.HasValue) { query = "SELECT authors.au_id,authors.au_fname,authors.au_lname,authors.phone,authors.address,authors.city,authors.state,authors.zip,authors.contract " + "FROM authors INNER JOIN titleauthor ON authors.au_id=titleauthor.au_id INNER JOIN sales ON titleauthor.title_id=sales.title_id " + "WHERE sales.ord_date >= CONVERT(datetime,@first_date,110) AND sales.ord_date <= CONVERT(datetime,@second_date,110) GROUP BY authors.au_id,authors.au_fname,authors.au_lname,authors.phone,authors.address,authors.city,authors.state,authors.zip,authors.contract ORDER BY sum(sales.qty) DESC;"; results = db.Database.SqlQuery <authors>(query, parameters: new[] { first_date, second_date }); } else if (!report1.From_time.HasValue) { query = "SELECT TOP (@number_of_sales) authors.au_id,authors.au_fname,authors.au_lname,authors.phone,authors.address,authors.city,authors.state,authors.zip,authors.contract " + "FROM authors INNER JOIN titleauthor ON authors.au_id=titleauthor.au_id INNER JOIN sales ON titleauthor.title_id=sales.title_id " + "WHERE sales.ord_date <= CONVERT(datetime,@second_date,110) GROUP BY authors.au_id,authors.au_fname,authors.au_lname,authors.phone,authors.address,authors.city,authors.state,authors.zip,authors.contract ORDER BY sum(sales.qty) DESC;"; results = db.Database.SqlQuery <authors>(query, parameters: new[] { number_of_sales, second_date }); } else { query = "SELECT TOP (@number_of_sales) authors.au_id,authors.au_fname,authors.au_lname,authors.phone,authors.address,authors.city,authors.state,authors.zip,authors.contract " + "FROM authors INNER JOIN titleauthor ON authors.au_id=titleauthor.au_id INNER JOIN sales ON titleauthor.title_id=sales.title_id " + "WHERE sales.ord_date >= CONVERT(datetime,@first_date,110) GROUP BY authors.au_id,authors.au_fname,authors.au_lname,authors.phone,authors.address,authors.city,authors.state,authors.zip,authors.contract ORDER BY sum(sales.qty) DESC;"; results = db.Database.SqlQuery <authors>(query, parameters: new[] { number_of_sales, first_date }); } ViewBag.results = results; return(View("Results")); } else { return(View()); } }
public ActionResult Index(Report2_get report2) { if (ModelState.IsValid) { pubsEntities2 db = new pubsEntities2(); string query; IEnumerable <Report2_post> rep2; SqlParameter first_date = new SqlParameter("first_date", report2.From_time); SqlParameter second_date = new SqlParameter("second_date", report2.To_time); if (!report2.From_time.HasValue) { query = "SELECT ord_date,ord_num,stor_name,title " + "FROM sales INNER JOIN stores on sales.stor_id=stores.stor_id INNER JOIN titles on titles.title_id=sales.title_id " + "WHERE sales.ord_date >= '1/1/1900' AND sales.ord_date <= CONVERT(datetime,@second_date,110) " + "AND stor_name LIKE '" + report2.starts + "%" + report2.ends + "' ORDER BY ord_date DESC;"; rep2 = db.Database.SqlQuery <Report2_post>(query, parameters: new[] { second_date }); } else if (!report2.To_time.HasValue) { query = "SELECT ord_date,ord_num,stor_name,title " + "FROM sales INNER JOIN stores on sales.stor_id=stores.stor_id INNER JOIN titles on titles.title_id=sales.title_id " + "WHERE sales.ord_date >= CONVERT(datetime,@first_date,110) AND sales.ord_date <= '1/1/2222' " + "AND stor_name LIKE '" + report2.starts + "%" + report2.ends + "' ORDER BY ord_date DESC;"; rep2 = db.Database.SqlQuery <Report2_post>(query, parameters: new[] { first_date }); } else if (string.IsNullOrWhiteSpace(report2.starts) && string.IsNullOrWhiteSpace(report2.ends)) { query = "SELECT ord_date,ord_num,stor_name,title " + "FROM sales INNER JOIN stores on sales.stor_id=stores.stor_id INNER JOIN titles on titles.title_id=sales.title_id " + "WHERE sales.ord_date >= CONVERT(datetime,@first_date,110) AND sales.ord_date <= CONVERT(datetime,@second_date,110) " + "ORDER BY ord_date DESC;"; rep2 = db.Database.SqlQuery <Report2_post>(query, parameters: new[] { first_date, second_date }); } else if (string.IsNullOrWhiteSpace(report2.starts)) { query = "SELECT ord_date,ord_num,stor_name,title " + "FROM sales INNER JOIN stores on sales.stor_id=stores.stor_id INNER JOIN titles on titles.title_id=sales.title_id " + "WHERE sales.ord_date >= CONVERT(datetime,@first_date,110) AND sales.ord_date <= CONVERT(datetime,@second_date,110) " + "AND stor_name LIKE '%" + report2.ends + "' ORDER BY ord_date DESC;"; rep2 = db.Database.SqlQuery <Report2_post>(query, parameters: new[] { first_date, second_date }); } else if (string.IsNullOrWhiteSpace(report2.ends)) { query = "SELECT ord_date,ord_num,stor_name,title " + "FROM sales INNER JOIN stores on sales.stor_id=stores.stor_id INNER JOIN titles on titles.title_id=sales.title_id " + "WHERE sales.ord_date >= CONVERT(datetime,@first_date,110) AND sales.ord_date <= CONVERT(datetime,@second_date,110) " + "AND stor_name LIKE '" + report2.starts + "%' ORDER BY ord_date DESC;"; rep2 = db.Database.SqlQuery <Report2_post>(query, parameters: new[] { first_date, second_date }); } else { query = "SELECT ord_date,ord_num,stor_name,title " + "FROM sales INNER JOIN stores on sales.stor_id=stores.stor_id INNER JOIN titles on titles.title_id=sales.title_id " + "WHERE sales.ord_date >= CONVERT(datetime,@first_date,110) AND sales.ord_date <= CONVERT(datetime,@second_date,110) " + "AND stor_name LIKE '" + report2.starts + "%" + report2.ends + "' ORDER BY ord_date DESC;"; rep2 = db.Database.SqlQuery <Report2_post>(query, parameters: new[] { first_date, second_date }); } Console.WriteLine(rep2); ViewBag.Resultset2 = rep2; return(View("Results2")); } else { return(View()); } }