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 MemberModels GetItem(long memberId) { return(CommonDA.GetItem <MemberModels>("Member", memberId)); }
public static void DeletePlace(IEnumerable <long> IDs) { CommonDA.Delete("RewardPlace", IDs); }
public static void ToggleIssue(long ID) { CommonDA.ToggleIssue("RewardSetting", ID); }
public static RewardPlaceModel GetItemPlace(long id) { return(CommonDA.GetItem <RewardPlaceModel>("RewardPlace", id)); }
public static RewardFieldModel GetField(long id) { return(CommonDA.GetItem <RewardFieldModel>("RewardField", id)); }
public void SortKeywords(IEnumerable <SortItem> items) { CommonDA.Sort("Keywords", items, ""); }
public static ArticleTypesModels GetItem(long id) { return(CommonDA.GetItem <ArticleTypesModels>("ArticleTypes", id)); }
public static void ToggleIssue(long id) { CommonDA.ToggleIssue("ArticleTypes", id); }
public static AdsStatisticsLabelsModel GetItem(long id) { return(CommonDA.GetItem <AdsStatisticsLabelsModel>("AdsStatisticsLabels", id)); }
public static int DeleteItem(long id) { return(CommonDA.Delete("GroupPermission", new List <long> { id })); }
public static MemberModels GetItem(long id) { MemberModels m = CommonDA.GetItem <MemberModels>("[Member]", id); return(m); }
public static LogStatisticLabelModels GetItem(long id) { return(CommonDA.GetItem <LogStatisticLabelModels>("LogStatisticLabels", id)); }
public static IEnumerable <MemberModels> GetItems() { return(CommonDA.GetAllItem <MemberModels>("Member")); }
public void ToggleIssue(long keywordId) { CommonDA.ToggleIssue("Keywords", keywordId); }
public static int Delete(long id) { return(CommonDA.Delete("ResourceVideos", new long[] { id })); }
public void DeleteKeyword(IEnumerable <long> IDs) { CommonDA.Delete("Keywords", IDs); }
public static ResourceImagesModels GetItem(long id) { return(CommonDA.GetItem <ResourceImagesModels>("ResourceImages", id)); }
/// <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()); } }
public static ArticlePosterModels GetItem(long id) { return(CommonDA.GetItem <ArticlePosterModels>("ArticlePoster", id)); }
public static List <SysLogModels> GetItems(int pageSize, int pageIndex, out int recordCount, DateTime SDate, DateTime EDate, string KW = "", int[] actions = null, long memberId = 0) { List <SysLogModels> items = new List <SysLogModels>(); string sql = @"SELECT s.*,m.LoginID as MemberLoginID,m.Name as MemberName, site.Title AS SiteTitle, Case WHEN n.Title IS NOT NULL THEN n.Title WHEN p.Title IS NOT NULL THEN p.Title ELSE '' END as MenuTitle FROM SysLog s JOIN Member m ON s.MemberID=m.ID LEFT JOIN Sites site ON site.ID=s.SiteID LEFT JOIN Menus n ON n.ID=s.MenuID LEFT JOIN Pages p ON p.No=s.SourceID WHERE AddTime>='" + SDate.ToString("yyyy/MM/dd HH:mm:ss") + "' AND AddTime<='" + EDate.ToString("yyyy/MM/dd HH:mm:ss") + "' "; if (!string.IsNullOrEmpty(KW)) { sql += $"AND (m.LoginID like '%{KW.Replace("'", "''")}%' or m.Name like '%{KW.Replace("'", "''")}%' or m.LoginID like '%{KW.Replace("'", "''")}%' or s.IP like '%{KW.Replace("'", "''")}%') "; } if (memberId != 0) { sql += $" AND MemberID = {memberId} "; } if (actions != null) { string strActions = string.Join(", ", actions); sql += $" AND Actions IN ({strActions}) "; } items = CommonDA.GetPageData <SysLogModels>(sql, pageSize, pageIndex, out recordCount, null, "ORDER BY AddTime DESC").ToList(); for (int i = 0; i < items.Count; i++) { SysLogModels item = items[i]; items[i].MgrNoName = ((SysMgrNoName)Convert.ToByte(item.MgrNo)).ToString(); string menuTitle = "", pageTitle = ""; long? siteID = null, menuID = null, pageID = null; if (item.SiteID.HasValue) { siteID = item.SiteID.Value; if (item.MenuID.HasValue && item.SourceID.HasValue) { menuID = item.MenuID.Value; pageID = item.SourceID.Value; } else if (item.MenuID.HasValue) { menuID = item.MenuID.Value; } else if (item.SourceID.HasValue) { menuID = item.SourceID.Value; } if (siteID.HasValue && menuID.HasValue && pageID.HasValue) { List <WorkV3.Models.BreadCrumbsModels> BreadCrumbs = WorkV3.Models.DataAccess.MenusDAO.GetBreadCrumbs(siteID.Value, menuID.Value, pageID.Value); //WorkLib.WriteLog.Write(true, BreadCrumbs.Count.ToString()); foreach (WorkV3.Models.BreadCrumbsModels breadModel in BreadCrumbs) { menuTitle += breadModel.Title + ">"; pageTitle = breadModel.PagesTitle; } } else if (siteID.HasValue && menuID.HasValue) { List <WorkV3.Models.BreadCrumbsModels> BreadCrumbs = WorkV3.Models.DataAccess.MenusDAO.GetBreadCrumbs(siteID.Value, menuID.Value, 0); foreach (WorkV3.Models.BreadCrumbsModels breadModel in BreadCrumbs) { menuTitle += breadModel.Title + ">"; pageTitle = breadModel.PagesTitle; } } } if (!string.IsNullOrEmpty(menuTitle)) { menuTitle = menuTitle.Trim('>'); item.MenuTitle = menuTitle; } if (!string.IsNullOrEmpty(pageTitle)) { item.PageTitle = pageTitle; } //if (item.SiteID.HasValue && item.MenuID.HasValue && ) //{ // List<WorkV3.Models.BreadCrumbsModels> BreadCrumbs = WorkV3.Models.DataAccess.MenusDAO.GetBreadCrumbs(item.SiteID.Value, item.MenuID.Value, item.SourceID); //} //items[i].MenuTitle = items[i].ActionsName = ((SysActionsName)Convert.ToByte(item.Actions)).ToString(); } return(items); }
public static int Delete(IEnumerable <long> ids) { return(CommonDA.Delete("ArticlePoster", ids)); }
public static RewardSettingModel GetItem(long id) { return(CommonDA.GetItem <RewardSettingModel>("RewardSetting", id)); }
public static void Sort(long menuId, IEnumerable <SortItem> items) { CommonDA.Sort("ArticlePoster", items, "MenuID = " + menuId); }
public static void Delete(IEnumerable <long> IDs) { CommonDA.Delete("RewardSetting", IDs); }
public static void ToggleIssue(long id) { CommonDA.ToggleIssue("ArticlePoster", id); }
public static void SortPlace(long SiteID, IEnumerable <SortItem> items) { CommonDA.Sort("RewardPlace", items, "SiteID = " + SiteID); }
public static IntraIPlimitModel GetItem(long Id) { return(CommonDA.GetItem <IntraIPlimitModel>("IntraIPlimit", Id)); }
public static void ToggleIssuePlace(long ID) { CommonDA.ToggleIssue("RewardPlace", ID); }
public static bool IsUserIdExist(string userID) { return(CommonDA.IsValueExists("Member", "LoginID", userID)); }