// 廣告成效頁
public ActionResult Index(AdsStatisticsSearchModel search, long?siteId, int?index)
{
DateTime today = DateTime.Now;
DateTime firstDayOfCurrentMonth = new DateTime(today.Year, today.Month, 1);
DateTime endDayOfCurrentMonth = firstDayOfCurrentMonth.AddMonths(1).AddDays(-1);
if (Request.HttpMethod == "GET")
{
if (index == null)
{
Utility.ClearSearchValue();
search.StartDate = search.StartDate != DateTime.MinValue ? search.StartDate : firstDayOfCurrentMonth;
search.EndDate = search.EndDate != DateTime.MinValue ? search.EndDate : endDayOfCurrentMonth;
Session["ExportSearch"] = search;
}
else
{
AdsStatisticsSearchModel prevSearch = Utility.GetSearchValue <AdsStatisticsSearchModel>();
if (prevSearch != null)
{
search = prevSearch;
}
}
}
else if (Request.HttpMethod == "POST")
{
Utility.SetSearchValue(search);
Session["ExportSearch"] = search;
}
search.SiteId = search.SiteId ?? siteId;
if (index == null)
{
index = 1;
}
int pageSize = 20;
int recordCount = 0;
List <AdsStatisticsViewModel> statisticsData = AdvertisementStatisticsDAO.GetAdsStatisticsData(search, pageSize, (int)index, out recordCount);
ViewBag.Pagination = new Pagination {
PageSize = pageSize, PageIndex = (int)index, TotalRecord = recordCount
};
ViewBag.AdsPositionMapping = AdvertisementStatisticsDAO.AdsPosition;
ViewBag.UploadUrl = WorkV3.Golbal.UpdFileInfo.GetVPathBySiteID((long)search.SiteId, "Advertisement");
ViewBag.Search = search;
ViewBag.StartDate = firstDayOfCurrentMonth;
ViewBag.EndDate = endDayOfCurrentMonth;
ViewBag.SiteId = search.SiteId;
return(View(statisticsData));
}
public FileResult Export(bool?privacy)
{
AdsStatisticsSearchModel search = Session["ExportSearch"] as AdsStatisticsSearchModel;
if (search == null)
{
search = new AdsStatisticsSearchModel();
}
ViewData["Info"] = AdvertisementStatisticsDAO.GetAll(search);
ViewData["Privacy"] = privacy ?? false;
ViewData["AdsPositionMapping"] = AdvertisementStatisticsDAO.AdsPosition;
string html = Utility.GetViewHtml(this, "Export", null);
string title = $"廣告分析報表-{DateTime.Now.ToString("yyyyMMddHHmmsss")}.xls";
return(File(System.Text.Encoding.UTF8.GetBytes(html), "application/vnd.ms-excel", title));
}
public static List <AdsStatisticsViewModel> GetAll(AdsStatisticsSearchModel search)
{
int recordCount = 0;
return(GetAdsStatisticsData(search, int.MaxValue, 1, out recordCount));
}
/// <summary>
/// 取得廣告成效資料
/// </summary>
/// <param name="search"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="recordCount"></param>
/// <returns></returns>
public static List <AdsStatisticsViewModel> GetAdsStatisticsData(AdsStatisticsSearchModel search, int pageSize, int pageIndex, out int recordCount)
{
using (var conn = new SqlConnection(WebInfo.Conn))
{
search.Advertisers = search.Advertisers ?? new List <long>();
string whereClause = search.Advertisers.Count > 0? $" WHERE Advertisers_ID IN ({string.Join(", ", search.Advertisers)}) ": "";
string dateConstraint = "";
if (search.StartDate != DateTime.MinValue && search.EndDate != DateTime.MinValue)
{
dateConstraint = $" AND (RecordDay BETWEEN '{search.StartDate.ToString("yyyy/MM/dd")}' AND '{search.EndDate.ToString("yyyy/MM/dd")}') AND a.AdsCustomizeID = b.AdsCustomize_ID ";
}
string orderDirection = search.IsAsc? " ASC ": " DESC ";
string order = "";
switch (search.OrderType)
{
case 1:
default:
order = $" ORDER BY ClickCount {orderDirection}, BrowseCount {orderDirection} ";
break;
case 2:
order = $" ORDER BY BrowseCount {orderDirection}, ClickCount {orderDirection} ";
break;
case 3:
order = $" ORDER BY FullPrice {orderDirection}, ClickCount {orderDirection}, BrowseCount {orderDirection} ";
break;
case 4:
order = $" ORDER BY CP {orderDirection}, ClickCount {orderDirection}, BrowseCount {orderDirection} ";
break;
}
string filterAdvertiser = search.Advertisers.Count > 0 ? $" AND Advertisers_ID IN ({string.Join(", ", search.Advertisers)}) " : "";
string sql = $@"select z.MenuID, z.BrowseCount, z.ClickCount, a.ID AdsCustomizeID, c.Title MenuTitle,
(case when d.BillingByClick is null then 0 else d.BillingByClick end * z.ClickCount + case when d.BillingByBrowse is null then 0 else d.BillingByBrowse end * z.BrowseCount) FullPrice,
case when
case when d.BillingByClick is null then 0 else d.BillingByClick end + case when d.BillingByBrowse is null
then 0
else d.BillingByBrowse end > 0 then ROUND(CAST(z.ClickCount AS DECIMAL(20,2)) / CAST((case when d.BillingByClick is null then 0 else d.BillingByClick end + case when d.BillingByBrowse is null then 0 else d.BillingByBrowse end) AS DECIMAL(20,2)), 3) else 0 end CP,
f.CompanyName, f.ContactPerson, f.ContactPhone, a.*
from (
select AdsCustomizeID, MenuID, sum(case when [Event] = 'Browsing' then [Count] else 0 end) BrowseCount, sum(case when [Event] = 'Click' then [Count] else 0 end) ClickCount
from
(
select a.AdsCustomizeID, c.MenuID, a.[Event], count(*) 'Count'
from AdsStatistics a
join AdsCustomize b on (a.AdsCustomizeID = b.ID)
join Pages c on (a.PageID = c.No)
join (
select Advertisement_ID, MenuID
from AdsDisplayAreaSet
group by Advertisement_ID, MenuID
) d on (b.Advertisement_ID = d.Advertisement_ID and c.MenuID = d.MenuID)
where AdsCustomizeID in (
select distinct a.AdsCustomizeID
from AdsStatistics a
join AdsCustomize b on (a.AdsCustomizeID = b.ID {filterAdvertiser})
where RecordDay BETWEEN '{search.StartDate.ToString("yyyy/MM/dd")}' AND '{search.EndDate.ToString("yyyy/MM/dd")}'
)
group by a.AdsCustomizeID, c.MenuID, a.[Event]
) a
group by AdsCustomizeID, MenuID
) z
join AdsCustomize a on(z.AdsCustomizeID = a.ID)
JOIN (SELECT Advertisement_ID, MenuID FROM AdsDisplayAreaSet group by Advertisement_ID, MenuID) b ON (a.Advertisement_ID = b.Advertisement_ID and z.MenuID = b.MenuID)
JOIN Menus c on (b.MenuID = c.ID)
join AdsCustomizeAccountSet d on (d.AdsCustomize_ID = a.ID)
LEFT JOIN Advertisers f ON (a.Advertisers_ID = f.ID)
{whereClause} ";
return(CommonDA.GetPageData <AdsStatisticsViewModel>(sql, pageSize, pageIndex, out recordCount, null, order).ToList());
}
}
