public JsonResult SummaryReports() { //var query = "select count(betmoney) as TotalBets,sum(betmoney) as Sales,CONVERT(VARCHAR(19),month(betdate))+'/' +CONVERT(VARCHAR(19),day(betdate))+'/' +CONVERT(VARCHAR(19),Year(betdate)) as businessday from Receipts group by day(betdate) ,month(betdate),year(betdate)"; const string query = "select count(Stake) as TotalBets,sum(Stake) as Sales,CONVERT(VARCHAR(19),month(ReceiptDate))+'/' +CONVERT(VARCHAR(19),day(ReceiptDate))+'/' +CONVERT(VARCHAR(19),Year(ReceiptDate)) as businessday," + "[dbo].[ReturnTotalWinAmount](CONVERT(VARCHAR(19),month(ReceiptDate))+'/' +CONVERT(VARCHAR(19),day(ReceiptDate))+'/' +CONVERT(VARCHAR(19)," + "Year(ReceiptDate))) as WinAmount,[dbo].[ReturnTotalOutStandingAmount](CONVERT(VARCHAR(19),month(ReceiptDate))+'/' +CONVERT(VARCHAR(19),day(ReceiptDate))+'/' +CONVERT(VARCHAR(19),Year(ReceiptDate))) as Outstanding ," + "[dbo].[ReturnTotalPaidAmount](CONVERT(VARCHAR(19),month(ReceiptDate))+'/' +CONVERT(VARCHAR(19),day(ReceiptDate))+'/' +CONVERT(VARCHAR(19),Year(ReceiptDate))) as Paid," + " [dbo].[ReturnTotalCanceledTicket](CONVERT(VARCHAR(19),month(ReceiptDate))+'/' +CONVERT(VARCHAR(19),day(ReceiptDate))+'/' +CONVERT(VARCHAR(19),Year(ReceiptDate))) as Canceled," + " [dbo].[ReturnTotalCanceledAmount](CONVERT(VARCHAR(19),month(ReceiptDate))+'/' +CONVERT(VARCHAR(19),day(ReceiptDate))+'/' +CONVERT(VARCHAR(19),Year(ReceiptDate))) as TotalCanceled ," + " [dbo].[ReturnTotalPaidNumber](CONVERT(VARCHAR(19),month(ReceiptDate))+'/' +CONVERT(VARCHAR(19),day(ReceiptDate))+'/' +CONVERT(VARCHAR(19),Year(ReceiptDate))) as PaidNumber," + " [dbo].[ReturnTicketByStatus](CONVERT(VARCHAR(19),month(ReceiptDate))+'/' +CONVERT(VARCHAR(19),day(ReceiptDate))+'/' +CONVERT(VARCHAR(19),Year(ReceiptDate)),3) as WinNumber " + "from Receipts where ReceiptDate is not null and ReceiptDate > dateadd(day,-8,getdate()) and ReceiptStatus<> -1 group by day(ReceiptDate) ,month(ReceiptDate),year(ReceiptDate) order by month(ReceiptDate) desc,day(ReceiptDate) desc"; var report = new List <SummaryReportVm>(); try { var bridge = new DBBridge(); var reader = bridge.ExecuteReaderSQL(query); while (reader.Read()) { var dayreport = new SummaryReportVm { TicketSold = reader.GetInt32(0), Sales = Convert.ToDecimal(reader.GetDouble(1)), BusinessDay = reader.GetString(2), TotalWins = reader.GetInt32(3), PaidOrders = reader.GetInt32(5), Canceled = reader.GetInt32(7), CanceledNumber = reader.GetInt32(6), TicketsPaid = reader.GetInt32(8), WinCount = reader.GetInt32(9), }; report.Add(dayreport); } } catch (Exception er) { } var reports = report .Select(x => new { x.Sales, x.BusinessDay, x.TotalWins, x.PaidOrders, x.Canceled, Outstanding = x.OutStanding(), x.TicketsPaid, x.CanceledNumber, x.TicketSold, Profit = x.Sales - x.TotalWins, WinCount = x.WinCount, Cash = x.Cash() }); var counts = reports.Count(); return(Json(reports, JsonRequestBehavior.AllowGet)); }
public JsonResult BranchSummaryReports(int id) { // var query = "select count(betmoney) as TotalBets,sum(betmoney) as Sales,CONVERT(VARCHAR(19),month(betdate))+'/' +CONVERT(VARCHAR(19),day(betdate))+'/' +CONVERT(VARCHAR(19),Year(betdate)) as businessday from Receipts where branchid="+id+" group by day(betdate) ,month(betdate),year(betdate)"; string query = "select count(Stake) as TotalBets,sum(Stake) as Sales,CONVERT(VARCHAR(19),month(ReceiptDate))+'/' +CONVERT(VARCHAR(19),day(ReceiptDate))+'/' +CONVERT(VARCHAR(19),Year(ReceiptDate)) as businessday," + "[dbo].[ReturnTotalWinAmount](CONVERT(VARCHAR(19),month(ReceiptDate))+'/' +CONVERT(VARCHAR(19),day(ReceiptDate))+'/' +CONVERT(VARCHAR(19)," + "Year(ReceiptDate))) as WinAmount,[dbo].[ReturnTotalOutStandingAmount](CONVERT(VARCHAR(19),month(ReceiptDate))+'/' +CONVERT(VARCHAR(19),day(ReceiptDate))+'/' +CONVERT(VARCHAR(19),Year(ReceiptDate))) as Outstanding ," + "[dbo].[ReturnTotalPaidAmount](CONVERT(VARCHAR(19),month(ReceiptDate))+'/' +CONVERT(VARCHAR(19),day(ReceiptDate))+'/' +CONVERT(VARCHAR(19),Year(ReceiptDate))) as Paid," + " [dbo].[ReturnTotalCanceledTicket](CONVERT(VARCHAR(19),month(ReceiptDate))+'/' +CONVERT(VARCHAR(19),day(ReceiptDate))+'/' +CONVERT(VARCHAR(19),Year(ReceiptDate))) as Canceled," + " [dbo].[ReturnTotalCanceledAmount](CONVERT(VARCHAR(19),month(ReceiptDate))+'/' +CONVERT(VARCHAR(19),day(ReceiptDate))+'/' +CONVERT(VARCHAR(19),Year(ReceiptDate))) as TotalCanceled ," + " [dbo].[ReturnTotalPaidNumber](CONVERT(VARCHAR(19),month(ReceiptDate))+'/' +CONVERT(VARCHAR(19),day(ReceiptDate))+'/' +CONVERT(VARCHAR(19),Year(ReceiptDate))) as PaidNumber " + "from Receipts where ReceiptDate is not null and ReceiptStatus<> -1 and Receipts.BranchId= " + id + " group by day(ReceiptDate) ,month(ReceiptDate),year(ReceiptDate) "; var report = new List<SummaryReportVm>(); try { var bridge = new DBBridge(); var reader = bridge.ExecuteReaderSQL(query); while (reader.Read()) { var dayreport = new SummaryReportVm { TicketSold = reader.GetInt32(0), Sales = Convert.ToDecimal(reader.GetDouble(1)), BusinessDay = reader.GetString(2), TotalWins = reader.GetInt32(3), PaidOrders = reader.GetInt32(5), Canceled = reader.GetInt32(7), CanceledNumber = reader.GetInt32(6), TicketsPaid = reader.GetInt32(8), }; report.Add(dayreport); } } catch (Exception er) { } var reports = report .Select(x => new { x.Sales, x.BusinessDay, x.TotalWins, x.PaidOrders, x.Canceled, Outstanding = x.OutStanding(), x.TicketsPaid, x.CanceledNumber, x.TicketSold }); var counts = reports.Count(); return Json(reports, JsonRequestBehavior.AllowGet); }