// GET: topSalesInSpecificTimes
public ActionResult Index()
{
List <Models.topSalesInSpecificTime> list1 = new List <topSalesInSpecificTime>();
String sql = "select authors.au_lname,authors.au_fname,SUM(titles.price*sales.qty) as total from authors inner join titleauthor On authors.au_id = titleauthor.au_id inner join titles on titleauthor.title_id = titles.title_id inner join sales on titles.title_id = sales.title_id group by au_lname,au_fname order by total DESC ";
string strCon = "data source=(localdb)\\MSSQLLocalDB;initial catalog=pubs;integrated security=True;MultipleActiveResultSets=True;";
SqlConnection conn = new SqlConnection(strCon);
SqlCommand comm = new SqlCommand(sql, conn);
conn.Open();
SqlDataReader nwReader = comm.ExecuteReader();
while (nwReader.Read())
{
Models.topSalesInSpecificTime topSales = new Models.topSalesInSpecificTime();
topSales.name = nwReader[0].ToString();
topSales.sname = nwReader[1].ToString();
topSales.total = float.Parse(nwReader[2].ToString());
list1.Add(topSales);
}
nwReader.Close();
conn.Close();
return(View(list1));
}
public ActionResult Index(string searchString, string date1, string date2)
{
List <Models.topSalesInSpecificTime> list1 = new List <topSalesInSpecificTime>();
string stringwhere = "where (sales.ord_date BETWEEN '" + date1 + "' AND '" + date2 + "')";
if (date1 == "" || date2 == "")
{
stringwhere = "";
}
string stringtop = "TOP " + searchString;
if (searchString == "")
{
stringtop = "";
}
String sql = "select " + stringtop + " authors.au_lname,authors.au_fname,SUM(titles.price*sales.qty) as total from authors inner join titleauthor On authors.au_id = titleauthor.au_id inner join titles on titleauthor.title_id = titles.title_id inner join sales on titles.title_id = sales.title_id " + stringwhere + " group by au_lname,au_fname order by total DESC ";
string strCon = "data source = (localdb)\\MSSQLLocalDB; initial catalog = pubs; integrated security = True; MultipleActiveResultSets = True; ";
SqlConnection conn = new SqlConnection(strCon);
SqlCommand comm = new SqlCommand(sql, conn);
conn.Open();
SqlDataReader nwReader = comm.ExecuteReader();
while (nwReader.Read())
{
Models.topSalesInSpecificTime topSales = new Models.topSalesInSpecificTime();
topSales.name = nwReader[0].ToString();
topSales.sname = nwReader[1].ToString();
topSales.total = float.Parse(nwReader[2].ToString());
list1.Add(topSales);
}
nwReader.Close();
conn.Close();
return(View(list1));
}
