// 廣告成效頁 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()); } }