protected void btnExecute_Click(object sender, EventArgs e) { //Define workbook and store null initially Workbook workbook = null; string path = MapPath("."); path = path.Substring(0, path.LastIndexOf("\\")); SalesTotals salesTotals = new SalesTotals(path); //Create workbook based on the custom method of a class workbook = salesTotals.CreateSalesTotals(); if (ddlFileVersion.SelectedItem.Value == "XLS") { ////Save file and send to client browser using selected format workbook.Save(HttpContext.Current.Response, "SalesTotals.xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003)); } else { workbook.Save(HttpContext.Current.Response, "SalesTotals.xlsx", ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx)); } //end response to avoid unneeded html HttpContext.Current.Response.End(); }
public static List<SalesTotals> Sales(string startDate, string endDate, string transactionType, String location) { List<SalesTotals> list; string str = ""; string str2 = ""; string str4 = transactionType; if (str4 != null) { if (!(str4 == "Meal Plan")) { if (str4 == "Location") { str = "LocationID"; goto Label_0062; } if (str4 == "Meal") { str = "COALESCE (CodeCafeEmpChargePrice.ShortDescription, 'out of range')"; str2 = "LEFT JOIN CodeCafeEmpChargePrice ON CONVERT(datetime, CONVERT(varchar(200), DailyTransactions.DateTime, 108), 108) BETWEEN CONVERT(datetime, CONVERT(varchar(200), CodeCafeEmpChargePrice.StartTime, 108), 108) AND CONVERT(datetime, CONVERT(varchar(200), CodeCafeEmpChargePrice.EndTime, 108), 108) "; goto Label_0062; } } else { str = "PlanId"; goto Label_0062; } } str = "PlanId"; Label_0062: list = new List<SalesTotals>(); string str3 = "select " + str + @" as description, sum(Amount), sum(case when ttid = 'MUNCHCHRG' then Amount else 0 end) as munch, sum(case when ttid <> 'MUNCHCHRG' then Amount else 0 end) as nonMunch, sum(case when ttid = 'MUNCHCHRG' then 0 else 1 end), sum(case when ttid = 'MUNCHCHRG' then 1 else 0 end) from dailytransactions " + str2 + @" where ttid in ('EMPCHRG', 'MUNCHCHRG', 'MEALCHRG') "; if (transactionType.Equals("Meal")) { str3 = str3 + "and (DailyTransactions.LocationID = 'CAFE') "; } if ((startDate != null) && !"".Equals(startDate)) { string str5 = str3; str3 = str5 + "and DateTime between '" + startDate + "' and '" + endDate + "' "; } if (!location.ToLower().Equals("all")) { str3 = str3 + "and (DailyTransactions.LocationID = '" + location + "') "; } str3 = str3 + "group by " + str + " "; if (transactionType.Equals("Meal")) { str3 = str3 + "order by MIN(CodeCafeEmpChargePrice.StartTime) "; } else { str3 = str3 + "order by " + str; } SqlConnection connection = new SqlConnection(); SqlCommand command = new SqlCommand(); connection.ConnectionString = ConfigurationManager.ConnectionStrings["ChargeProgramConnectionString"].ConnectionString; command.CommandText = str3; command.Connection = connection; connection.Open(); SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult); while (reader.Read()) { SalesTotals item = new SalesTotals(); item.Description = reader.GetString(0); item.Amount = reader.GetDecimal(1); item.MunchAmount = reader.GetDecimal(2); item.NonMunchAmount = reader.GetDecimal(3); item.MealplanCount = reader.GetInt32(4); item.MunchCount = reader.GetInt32(5); list.Add(item); } reader.Close(); connection.Close(); return list; }