public IActionResult GetMngmtReport(MngmtReportRequest model) { string sql = @" select date_format(a.delivery_time, '%Y-%m') as Id, date_format(a.delivery_time, '%M') as Month, count(1) as TotalCount, count(1) as SuccessCount, 0 as FailCount /*sum( case when message_status = 255 then 1 else 0 end) as SuccessCount, sum( case when message_status = 255 then 0 else 1 end) as FailCount*/ from ( SELECT CALL_TIMESTAMP as Delivery_Time, TRANSACTION_ID as Transaction_Id, ORIGINATING_ADDRESS as Origination_Address, DESTINATING_ADDRESS as Destination_Address, STATUS as Message_Status FROM ( select * from MCA_VMS_CC_01 UNION ALL select * from MCA_VMS_CC_02 UNION ALL select * from MCA_VMS_CC_03 UNION ALL select * from MCA_VMS_CC_04 UNION ALL select * from MCA_VMS_CC_05 UNION ALL select * from MCA_VMS_CC_06 UNION ALL select * from MCA_VMS_CC_07 UNION ALL select * from MCA_VMS_CC_08 UNION ALL select * from MCA_VMS_CC_09 UNION ALL select * from MCA_VMS_CC_10 UNION ALL select * from MCA_VMS_CC_11 UNION ALL select * from MCA_VMS_CC_12 ) a ) a where date_format(a.delivery_time, '%Y') = {0} and destination_address = {1} group by date_format(a.delivery_time, '%Y-%m'), date_format(a.delivery_time, '%M') order by 1 "; var q = _context.MngmtReportViewModel.FromSqlRaw(sql, model.Year, model.DestinationAddress); return(Ok(q)); }
public IQueryable <MngmtReportViewModel> QueryMngmtReport(MngmtReportRequest model, string id) { string sql = System.IO.File.ReadAllText(@".\SQL\USSD_GETMNGMTREPORT.sql"); sql = sql.Replace("[ID]", id); var q = _context.MngmtReportViewModel.FromSqlRaw(sql, model.Year, model.DestinationAddress); return(q); }
public IActionResult GetMngmtReport(MngmtReportRequest model) { var q = QueryMngmtReport(model, "01"); for (int i = 2; i <= 31; i++) { q = q.Concat(QueryMngmtReport(model, i.ToString("D2"))); } var qq = q .GroupBy(a => new { a.Id, a.Month }) .Select(g => new MngmtReportViewModel { Id = g.Key.Id, Month = g.Key.Month, FailCount = g.Sum(b => b.FailCount), SuccessCount = g.Sum(b => b.SuccessCount), TotalCount = g.Sum(b => b.TotalCount) }); qq = qq.OrderBy(a => a.Id); return(Ok(qq)); }