public static List <AdsStatisticsDetailViewModel> GetStatisticsDetail(AdsDetailStatisticsSearchModel search, int pageSize, int pageIndex, out int recordCount) { using (var conn = new SqlConnection(WebInfo.Conn)) { Dictionary <string, object> param = new Dictionary <string, object>(); List <string> wheres = new List <string>(); wheres.Add($" [Event] = '{search.Type}' "); string dateConstraint = ""; if (search.StartDate != DateTime.MinValue && search.EndDate != DateTime.MinValue) { wheres.Add($" (RecordDay BETWEEN '{search.StartDate.ToString("yyyy/MM/dd")}' AND '{search.EndDate.ToString("yyyy/MM/dd")}') "); } if (search.AdsCustomId != 0) { wheres.Add($" AdsCustomizeID = @AdsCustomizeID "); param.Add("@AdsCustomizeID", search.AdsCustomId); } string whereClause = wheres.Count == 0? "": $" WHERE {String.Join(" AND ", wheres)} "; string orderDirection = search.IsAsc ? " ASC " : " DESC "; string order = $" ORDER BY RecordTime {orderDirection} "; string sql = $@"SELECT distinct d.Name MemberName, d.Email MemberEmail, c.Title, a.* FROM [huashan].[dbo].[AdsStatistics] a JOIN AdsCustomize b on (a.AdsCustomizeID = b.ID) JOIN Pages c on (a.PageID = c.No) LEFT OUTER JOIN Member d on (a.MemberId = d.ID) {whereClause} "; return(CommonDA.GetPageData <AdsStatisticsDetailViewModel>(sql, pageSize, pageIndex, out recordCount, param, order).ToList()); } }
// 廣告主統計頁 public ActionResult AdvertiserStatistics(AdsDetailStatisticsSearchModel 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 = firstDayOfCurrentMonth; search.EndDate = endDayOfCurrentMonth; Session["ExportAdvertiserSearch"] = search; } else { AdsDetailStatisticsSearchModel prevSearch = Utility.GetSearchValue <AdsDetailStatisticsSearchModel>(); if (prevSearch != null) { search = prevSearch; } } } else if (Request.HttpMethod == "POST") { Utility.SetSearchValue(search); Session["ExportAdvertiserSearch"] = search; } if (index == null) { index = 1; } int pageSize = 20; int recordCount = 0; List <AdvertiserStatisticsViewModel> data = AdvertisementStatisticsDAO.GetAdvertiserStatistics(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(siteId, "Advertisement"); ViewBag.Search = search; ViewBag.StartDate = firstDayOfCurrentMonth; ViewBag.EndDate = endDayOfCurrentMonth; ViewBag.SiteID = siteId; return(View(data)); }
// 廣告成效詳細內容頁 public ActionResult Detail(AdsDetailStatisticsSearchModel search, int?index) { DateTime today = DateTime.Now; DateTime firstDayOfCurrentMonth = new DateTime(today.Year, today.Month, 1); DateTime endDayOfCurrentMonth = firstDayOfCurrentMonth.AddMonths(1).AddDays(-1); AdsCustomizeModel adCustomize = AdvertisementDAO.GetAdsCustomizeItem(search.AdsCustomId); adCustomize = adCustomize ?? new AdsCustomizeModel(); 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["ExportDetailSearch"] = search; } else { AdsDetailStatisticsSearchModel prevSearch = Utility.GetSearchValue <AdsDetailStatisticsSearchModel>(); if (prevSearch != null) { search = prevSearch; } } } else if (Request.HttpMethod == "POST") { Utility.SetSearchValue(search); Session["ExportDetailSearch"] = search; } if (index == null) { index = 1; } int pageSize = 20; int recordCount = 0; List <AdsStatisticsDetailViewModel> statisticsData = AdvertisementStatisticsDAO.GetStatisticsDetail(search, pageSize, (int)index, out recordCount); ViewBag.Pagination = new Pagination { PageSize = pageSize, PageIndex = (int)index, TotalRecord = recordCount }; ViewBag.Search = search; ViewBag.StartDate = firstDayOfCurrentMonth; ViewBag.EndDate = endDayOfCurrentMonth; ViewBag.AdsCustomizeDescription = adCustomize.Description; return(View(statisticsData)); }
public FileResult ExportAdvertisers(bool?privacy) { AdsDetailStatisticsSearchModel search = Session["ExportAdvertiserSearch"] as AdsDetailStatisticsSearchModel; if (search == null) { search = new AdsDetailStatisticsSearchModel(); } ViewData["AdvertiserInfo"] = AdvertisementStatisticsDAO.GetAllAdvertisers(search); ViewData["AdvertiserPrivacy"] = privacy ?? false; string html = Utility.GetViewHtml(this, "ExportAdvertisers", null); string title = $"廣告主分析報表-{DateTime.Now.ToString("yyyyMMddHHmmsss")}.xls"; return(File(System.Text.Encoding.UTF8.GetBytes(html), "application/vnd.ms-excel", title)); }
public FileResult ExportDetail(bool?privacy, bool IsClick = true) { AdsDetailStatisticsSearchModel search = Session["ExportDetailSearch"] as AdsDetailStatisticsSearchModel; if (search == null) { search = new AdsDetailStatisticsSearchModel(); } ViewData["DetailInfo"] = AdvertisementStatisticsDAO.GetAllDetail(search); ViewData["DetailPrivacy"] = privacy ?? false; string html = Utility.GetViewHtml(this, "ExportDetail", null); string titleCategory = IsClick ? "點擊量" : "瀏覽量"; string title = $"廣告{titleCategory}分析報表-{DateTime.Now.ToString("yyyyMMddHHmmsss")}.xls"; return(File(System.Text.Encoding.UTF8.GetBytes(html), "application/vnd.ms-excel", title)); }
public static List <AdvertiserStatisticsViewModel> GetAdvertiserStatistics(AdsDetailStatisticsSearchModel search, int pageSize, int pageIndex, out int recordCount) { using (var conn = new SqlConnection(WebInfo.Conn)) { List <string> wheres = new List <string>(); 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")}') "); } string sql = $@"SELECT AdvertiserID, CompanyName, ContactInfo, sum(ClickCount) ClickCount, sum(BrowseCount) BrowseCount, sum(FeeEstimate) FeeEstimate FROM ( SELECT c.ID AdvertiserID, c.CompanyName, (c.ContactPerson + ' ' + c.ContactPhone) ContactInfo, a.ClickCount, a.BrowseCount, a.FeeEstimate, CASE WHEN a.FeeEstimate = 0 THEN 0 ELSE ROUND(CAST(a.ClickCount AS DECIMAL(20,2)) / CAST(a.FeeEstimate AS DECIMAL(20,2)), 3) END CP FROM (SELECT AdsCustomizeID, sum(ClickCount) ClickCount, sum(BrowseCount) BrowseCount, sum(Fee) FeeEstimate FROM (SELECT a.AdsCustomizeID, 1 ClickCount, 0 BrowseCount, ISNULL(b.BillingByClick, 0) Fee FROM AdsStatistics a JOIN AdsCustomizeAccountSet b ON (a.AdsCustomizeID = b.AdsCustomize_ID) WHERE a.Event = 'Click' {dateConstraint} UNION ALL SELECT a.AdsCustomizeID, 0 ClickCount, 1 BrowseCount, ISNULL(b.BillingByBrowse, 0) Fee FROM AdsStatistics a JOIN AdsCustomizeAccountSet b ON (a.AdsCustomizeID = b.AdsCustomize_ID) WHERE a.Event = 'Browsing' {dateConstraint} ) a GROUP BY AdsCustomizeID ) a JOIN AdsCustomize b ON (a.AdsCustomizeID = b.ID) JOIN Advertisers c ON (b.Advertisers_ID = c.ID) WHERE b.Advertisers_ID is not null GROUP BY c.ID, c.CompanyName, c.ContactPerson, c.ContactPhone, a.ClickCount, a.BrowseCount, a.FeeEstimate ) a GROUP BY AdvertiserID, CompanyName, ContactInfo "; string orderDirection = search.IsAsc ? " ASC " : " DESC "; string order = $" ORDER BY ClickCount {orderDirection} "; return(CommonDA.GetPageData <AdvertiserStatisticsViewModel>(sql, pageSize, pageIndex, out recordCount, null, order).ToList()); } }
public static List <AdvertiserStatisticsViewModel> GetAllAdvertisers(AdsDetailStatisticsSearchModel search) { int recordCount = 0; return(GetAdvertiserStatistics(search, int.MaxValue, 1, out recordCount)); }
public static List <AdsStatisticsDetailViewModel> GetAllDetail(AdsDetailStatisticsSearchModel search) { int recordCount = 0; return(GetStatisticsDetail(search, int.MaxValue, 1, out recordCount)); }