public static List <SomerenModel.Report> DB_getRevenueReport() { SqlConnection connection = openConnectionDB(); List <SomerenModel.Report> report = new List <SomerenModel.Report>(); //put the query into string builder StringBuilder sb = new StringBuilder(); sb.Append("SELECT sum(StudentBarService.drink_sold) as 'NumberOfDrinks', sum(BarService.price * StudentBarService.drink_sold) as 'Turnover', COUNT(distinct StudentBarService.student_id) as 'NumberOfCustomers' FROM StudentBarService INNER JOIN BarService ON StudentBarService.drink_id = BarService.drink_id"); String sql = sb.ToString(); //start connection with DB SqlCommand command = new SqlCommand(sql, connection); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { SomerenModel.Report record = new SomerenModel.Report(); record.SetNumberOfDrinks((int)reader["NumberOfDrinks"]); record.SetTurnover((decimal)reader["Turnover"]); record.SetnumberOfCustomers((int)reader["NumberOfCustomers"]); report.Add(record); } reader.Close(); connection.Close(); return(report); }
//if user wants to get date-limited report public static List <SomerenModel.Report> DB_getLimitedRevenueReport(DateTime start, DateTime end) { SqlConnection connection = openConnectionDB(); List <SomerenModel.Report> report = new List <SomerenModel.Report>(); //editing dates in proper format yyyy-MM-dd for DB string start_edit = start.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture); string end_edit = end.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture); //put the query into string builder StringBuilder sb = new StringBuilder(); sb.Append("SELECT sum(StudentBarService.drink_sold) as 'NumberOfDrinks', " + "sum(BarService.price * StudentBarService.drink_sold) as 'Turnover', " + "COUNT(distinct StudentBarService.student_id) as 'NumberOfCustomers' " + "FROM StudentBarService INNER JOIN BarService ON StudentBarService.drink_id = BarService.drink_id " + "WHERE StudentBarService.date BETWEEN '" + start_edit + "' AND '" + end_edit + "'"); String sql = sb.ToString(); //start connection with DB SqlCommand command = new SqlCommand(sql, connection); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { SomerenModel.Report record = new SomerenModel.Report(); record.SetNumberOfDrinks((int)reader["NumberOfDrinks"]); record.SetTurnover((decimal)reader["Turnover"]); record.SetnumberOfCustomers((int)reader["NumberOfCustomers"]); report.Add(record); } reader.Close(); connection.Close(); return(report); }
public void addList(SomerenModel.Report report) { reportList.Add(report); }