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