Exemplo n.º 1
0
        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));
        }
Exemplo n.º 6
0
        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());
            }
        }
Exemplo n.º 7
0
        public static List <AdvertiserStatisticsViewModel> GetAllAdvertisers(AdsDetailStatisticsSearchModel search)
        {
            int recordCount = 0;

            return(GetAdvertiserStatistics(search, int.MaxValue, 1, out recordCount));
        }
Exemplo n.º 8
0
        public static List <AdsStatisticsDetailViewModel> GetAllDetail(AdsDetailStatisticsSearchModel search)
        {
            int recordCount = 0;

            return(GetStatisticsDetail(search, int.MaxValue, 1, out recordCount));
        }