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));
}
