public IActionResult GetDashboardReport1(DashboardReport1Request model) { string sql = @" select destination_address as DestinationAddress, count(1) as TotalCount 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 (date_format(a.delivery_time, '%m') = {1} OR {1} = '') and 'Success' = {2} /*and IF(Message_Status = 255, 'Success', 'Fail') = {2}*/ group by destination_address order by 2 desc "; var q = _context.DashboardReport1ViewModel.FromSqlRaw(sql, model.Year, model.Month, model.MessageStatus).Take(10); return(Ok(q)); }
public IQueryable <DashboardReport1ViewModel> QueryDashboardReport1(DashboardReport1Request model, string id) { string sql = System.IO.File.ReadAllText(@".\SQL\USSD_GETDASHBOARDREPORT.sql"); sql = sql.Replace("[ID]", id); var q = _context.DashboardReport1ViewModel.FromSqlRaw(sql, model.Year, model.Month, model.MessageStatus).Take(10); return(q); }
public IActionResult GetDashboardReport1(DashboardReport1Request model) { var q = QueryDashboardReport1(model, "01"); for (int i = 2; i <= 31; i++) { q = q.Concat(QueryDashboardReport1(model, i.ToString("D2"))); } var qq = q .GroupBy(a => new { a.DestinationAddress }) .Select(g => new DashboardReport1ViewModel { DestinationAddress = g.Key.DestinationAddress, TotalCount = g.Sum(b => b.TotalCount) }); qq = qq.OrderByDescending(a => a.TotalCount); return(Ok(qq)); }