public List <SalesReportRecord> GetSalesReport(SalesReportCM form)
{
List <SalesReportRecord> report = new List <SalesReportRecord>();
using (SqlConnection conn = new SqlConnection(Settings.GetConnectionString()))
{
var sql = "SELECT lastName, firstName,purchaseDate, SUM(ISNULL(purchasePrice, 0))AS 'total', COUNT(ISNULL(Purchase, 0)) AS 'count' " +
"FROM Employee" +
"LEFT JOIN Purchase ON Employee.employeeId = Purchase.employeeId" +
" WHERE 1 = 1";
SqlCommand cmd = new SqlCommand(sql, conn);
if (form.StartDate.HasValue)
{
sql += " AND purchaseDate >= @startDate";
cmd.Parameters.AddWithValue("@startDate", form.StartDate);
}
if (form.EndDate.HasValue)
{
sql += " AND purchaseDate <= @endDate";
cmd.Parameters.AddWithValue("@startDate", form.StartDate);
}
if (form.EmployeeId.HasValue)
{
sql += " AND employee = @employeeId";
cmd.Parameters.AddWithValue("@employeeId", form.EmployeeId);
}
sql += " GROUP BY firstName, lastName";
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
SalesReportRecord currentRow = new SalesReportRecord();
currentRow.FirstName = dr["firstName"].ToString();
currentRow.LastName = dr["lastName"].ToString();
currentRow.TotalSales = (decimal)dr["total"];
currentRow.Count = (int)dr["count"];
report.Add(currentRow);
}
}
conn.Close();
}
return(report);
}
public ActionResult Sales(SalesReportCM form)
{
if (ModelState.IsValid)
{
ReportsSalesVM VM = new ReportsSalesVM();
var repo = new SalesReportRepositoryADO();
VM.Form = form;
return(View(VM));
}
else
{
ReportsSalesVM VM = new ReportsSalesVM();
var repo = new SalesReportRepositoryADO();
VM.Form = form;
return(View(VM));
}
}
public ReportsSalesVM()
{
SalesReportCM Form = new SalesReportCM();
}
