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