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 static List <MemberModels> GetItems(int pageSize, int pageIndex, out int recordCount, string KW = "", string HospId = "", string GroupID = "", string MStatus = "")
{
using (var conn = new SqlConnection(WebInfo.Conn))
{
List <MemberModels> items = new List <MemberModels>();
string sql = " SELECT m.*,g.Name as GroupName FROM Member m LEFT JOIN [Group] g ON m.GroupID=g.ID WHERE 1=1 ";
if (!string.IsNullOrEmpty(KW))
{
sql += $" AND (m.LoginID like '%{KW.Replace("'", "''")}%' or m.Name like '%{KW.Replace("'", "''")}%' or m.Email like '%{KW.Replace("'", "''")}%' ) ";
}
if (!string.IsNullOrEmpty(GroupID))
{
sql += " AND m.GroupID IN ('" + GroupID.TrimEnd(',').Replace(",", "','") + "') ";
}
if (!string.IsNullOrEmpty(MStatus))
{
sql += " AND m.MStatus IN ('" + MStatus.TrimEnd(',').Replace(",", "','") + "') ";
}
return(CommonDA.GetPageData <MemberModels>(sql, pageSize, pageIndex, out recordCount, orderClause: " ORDER BY IsSupremeAuthority DESC, Sort, ID DESC ").ToList());
}
}
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 <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);
}
/// <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());
}
}