public IActionResult GetReport(TransCdr01RequestViewModel model)
{
var q = QueryReport(model, "01");
for (int i = 2; i <= 31; i++)
{
q = q.Concat(QueryReport(model, i.ToString("D2")));
}
q = q.OrderByDescending(a => a.DeliveryTime);
var qq = PaginatedList <Report1ViewModel> .Create(q, model.PageNumber ?? 1, model.PageSize ?? 10).GetPaginatedData();
return(Ok(qq));
}
public IQueryable <Report1ViewModel> QueryReport(TransCdr01RequestViewModel model, string id)
{
string sql = System.IO.File.ReadAllText(@".\SQL\USSD_GETREPORT.sql");
sql = sql.Replace("[ID]", id);
var q = _context.Report1ViewModel.FromSqlRaw(sql);
if (model.MessageType != null)
{
q = q.Where(a => a.MessageType == model.MessageType);
}
if (!string.IsNullOrEmpty(model.OriginationAddress))
{
q = q.Where(a => a.OriginationAddress.Contains(model.OriginationAddress));
}
if (!string.IsNullOrEmpty(model.DestinationAddress))
{
q = q.Where(a => a.DestinationAddress.Contains(model.DestinationAddress));
}
if (!string.IsNullOrEmpty(model.StartDate))
{
var d = DateTime.Parse(model.StartDate);
q = q.Where(a => a.DeliveryTime >= d);
}
if (!string.IsNullOrEmpty(model.EndDate))
{
var d = DateTime.Parse(model.EndDate);
q = q.Where(a => a.DeliveryTime < d.AddDays(1));
}
if (model.MessageStatus != null)
{
q = q.Where(a => a.MessageStatus == model.MessageStatus);
}
return(q);
}
public IActionResult GetReport(TransCdr01RequestViewModel model)
{
string sql = @"
SELECT Transaction_Id as TransactionId,
IF(Delivery_Time='0000-00-00 00:00:00.000',NULL,Delivery_Time) as DeliveryTime,
date_format(Delivery_Time, '%d %M %Y %T') as DeliveryTimeText,
Origination_Address as OriginationAddress,
Destination_Address as DestinationAddress,
/*IF(Message_Status=255, 1, 2) as MessageStatus,*/
1 as MessageStatus,
Message_Status as InternalMessageStatus,
Message_Type as MessageType
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,
Message_Type
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
";
var q = _context.Report1ViewModel.FromSqlRaw(sql);
if (model.MessageType != null)
{
q = q.Where(a => a.MessageType == model.MessageType);
}
if (!string.IsNullOrEmpty(model.OriginationAddress))
{
q = q.Where(a => a.OriginationAddress.Contains(model.OriginationAddress));
}
if (!string.IsNullOrEmpty(model.DestinationAddress))
{
q = q.Where(a => a.DestinationAddress.Contains(model.DestinationAddress));
}
if (!string.IsNullOrEmpty(model.StartDate))
{
var d = DateTime.Parse(model.StartDate);
q = q.Where(a => a.DeliveryTime >= d);
}
if (!string.IsNullOrEmpty(model.EndDate))
{
var d = DateTime.Parse(model.EndDate);
q = q.Where(a => a.DeliveryTime < d.AddDays(1));
}
if (model.MessageStatus != null)
{
q = q.Where(a => a.MessageStatus == model.MessageStatus);
}
q = q.OrderByDescending(a => a.DeliveryTime);
var qq = PaginatedList <Report1ViewModel> .Create(q, model.PageNumber ?? 1, model.PageSize ?? 10).GetPaginatedData();
return(Ok(qq));
}
