public IActionResult SelectCampaign()
{
ToShowCharByCampaignViewModel model = new ToShowCharByCampaignViewModel
{
Campaigns = _combosHelper.GetComboCampaigns()
};
return(View(model));
}
public async Task <IActionResult> StatisticsByCampaign(ToShowCharByCampaignViewModel model)
{
List <ContactStatistics> statistics = await _dataContext.ContactStatistics
.FromSql($"SELECT DAY(c.StartDate) AS Day,CONVERT(FLOAT,ROUND(((COUNT(c.CustomerId)*1.0/tab.Total_Contacto)*100),0)) AS PercentContact FROM Calls AS c INNER JOIN Codifications AS cod ON cod.Id = c.CodificationId INNER JOIN AspNetUsers AS u ON u.Id = c.UserId INNER JOIN (SELECT DAY(c.StartDate) AS Dia, COUNT(c.CustomerId) AS Total_Contacto FROM Calls AS c INNER JOIN AspNetUsers AS u ON u.Id = c.UserId WHERE u.CampaignId ={model.CampaignId} GROUP BY DAY(c.StartDate)) AS tab ON tab.Dia = DAY(c.StartDate) WHERE cod.ContactType = 0 AND u.CampaignId ={model.CampaignId} GROUP BY DAY(c.StartDate),tab.Total_Contacto ORDER BY DAY(c.StartDate) ASC").ToListAsync();
List <EffectivityStatistics> statistics2 = await _dataContext.EffectivityStatistics
.FromSql($"SELECT DAY(c.StartDate) AS Day,CONVERT(FLOAT,ROUND(((COUNT(c.CustomerId)*1.0/tab.Total_Contacto)*100),0)) AS PercentEffectivity FROM Calls AS c INNER JOIN Codifications AS cod ON cod.Id = c.CodificationId INNER JOIN AspNetUsers AS u ON u.Id = c.UserId INNER JOIN (SELECT DAY(c.StartDate) AS Dia, COUNT(c.CustomerId) AS Total_Contacto FROM Calls AS c INNER JOIN AspNetUsers AS u ON u.Id = c.UserId WHERE u.CampaignId ={model.CampaignId} GROUP BY DAY(c.StartDate)) AS tab ON tab.Dia = DAY(c.StartDate) WHERE cod.EffectivityType = 0 AND u.CampaignId ={model.CampaignId} GROUP BY DAY(c.StartDate),tab.Total_Contacto ORDER BY DAY(c.StartDate) ASC").ToListAsync();
model.ContactStatistics = statistics;
model.EffectivityStatistics = statistics2;
return(View(model));
}
