private void RecommendHandler2(long id, ArticleSettingModels setting, string uploadUrl, IEnumerable <ArticleTypesModels> types)
{
if (setting.ExtendReadOpen2)
{
IEnumerable <ArticleModels> recommends = ArticleDAO.GetRecommendItems2(setting, id, types.Select(t => t.ID));
Dictionary <long, SitePage> recommendSitePages = CardsDAO.GetPages(recommends.Select(r => r.CardNo));
Dictionary <long, dynamic> imgPathInfo = new Dictionary <long, dynamic>();
imgPathInfo.Add(setting.MenuID, new { Setting = setting, UploadUrl = uploadUrl });
foreach (ArticleModels item in recommends)
{
SitePage page = recommendSitePages[item.CardNo];
dynamic pathInfo;
if (!imgPathInfo.TryGetValue(page.MenuID, out pathInfo))
{
ArticleSettingModels newSetting = ArticleSettingDAO.GetItem(page.MenuID);
string newUploadUrl = UpdFileInfo.GetVPathByMenuID(page.SiteID, page.MenuID).TrimEnd('/') + "/";
pathInfo = new { Setting = newSetting, UploadUrl = newUploadUrl };
imgPathInfo.Add(page.MenuID, pathInfo);
}
string img = item.GetFirstImg(pathInfo.Setting);
item.Icon = string.IsNullOrWhiteSpace(img) ? string.Empty : pathInfo.UploadUrl + img;
}
ViewBag.Recommends2 = recommends;
ViewBag.RecommendSitePages2 = recommendSitePages;
}
}
public static void SetItem(ArticleSettingModels item)
{
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
SQLData.TableObject tableObj = db.GetTableObject("ArticleSetting");
tableObj.GetDataFromObject(item);
string sql = "Select 1 From ArticleSetting Where MenuID = " + item.MenuID;
bool isNew = db.GetFirstValue(sql) == null;
if (isNew)
{
tableObj["Creator"] = WorkV3.Areas.Backend.Models.DataAccess.MemberDAO.SysCurrent.Id;
tableObj["CreateTime"] = DateTime.Now;
tableObj.Insert();
}
else
{
tableObj.Remove("MenuID");
tableObj.Remove("Creator");
tableObj.Remove("CreateTime");
tableObj["Modifier"] = WorkV3.Areas.Backend.Models.DataAccess.MemberDAO.SysCurrent.Id;
tableObj["ModifyTime"] = DateTime.Now;
tableObj.Update(item.MenuID);
}
}
private static string GetWhereSql(ArticleSettingModels setting, string key, string type, long?typeId = null)
{
List <string> where = new List <string>();
where.Add("IsIssue = 1");
where.Add("MenuID = " + setting.MenuID);
if (!string.IsNullOrWhiteSpace(key))
{
key = $"N'%{ key.Replace("'", "''") }%'";
List <string> orSql = new List <string>();
orSql.Add($"Title Like { key }");
orSql.Add($"ID IN (Select SourceNo From Paragraph Where (Title Like { key } OR Contents Like { key }))");
orSql.Add($"ID IN (Select SourceNo From Paragraph Where ID IN (Select SourceNo From ResourceImages Where (Spec Like { key } OR SpecDetail Like { key })))");
orSql.Add($"ID IN (Select SourceNo From SEO Where (Keywords Like { key }) AND MenuID = { setting.MenuID })");
where.Add($"({ string.Join(" OR ", orSql) })");
}
if (!string.IsNullOrWhiteSpace(type) && type != "全部")
{
where.Add($"ID IN (Select ArticleID From ArticleToType Where TypeID IN (Select ID From ArticleTypes Where IsIssue = 1 And ID IN ({ type.Replace("'", "''") }) And MenuID = { setting.MenuID }))");
}
if (typeId != null)
{
where.Add($"ID IN (Select ArticleID From ArticleToType Where TypeID = { typeId })");
}
else
{
if (setting.Types != "all")
{
IEnumerable <long> types = setting.GetTypes();
if (types?.Count() > 0)
{
where.Add($"ID IN (Select ArticleID From ArticleToType Where TypeID In ({ string.Join(", ", types) }))");
}
else
{
where.Add("1 <> 1"); // 不選取任何資料
}
}
}
IEnumerable <int> issueSetting = setting.GetIssueSetting();
if (issueSetting.Count() == 1)
{
if (issueSetting.Contains(0))
{
where.Add("(IssueStart IS NULL OR IssueStart <= GETDATE()) AND (IssueEnd IS NULL OR IssueEnd >= GETDATE())");
}
else
{
where.Add("IssueEnd <= GETDATE()");
}
}
return(string.Join(" AND ", where));
}
public static List <ArticleModels> GetItems(ArticleSettingModels setting, string key, string type, long?typeId, int pageIndex, out int totalRecord)
{
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
//string sessionKey = setting.MenuID.ToString();
//string token = key + type + typeId;
//System.Web.SessionState.HttpSessionState session = HttpContext.Current.Session;
//dynamic sessionVal = session[sessionKey];
List <long> ids = null;
//if(sessionVal == null || sessionVal.Token != token) {
// session.Remove(sessionKey);
string query = $"Select ID From Article A Where { GetWhereSql(setting, key, type, typeId) } Order By { setting.SortField }";
ids = db.GetDataTable(query).AsEnumerable().Select(dr => (long)dr["ID"]).ToList();
// session.Add(sessionKey, new { Token = token, IDList = ids });
//} else {
// ids = sessionVal.IDList;
//}
if (ids == null)
{
ids = new List <long>();
}
totalRecord = ids.Count;
IEnumerable <long> currentIds = ids.Skip((pageIndex - 1) * setting.PageSize).Take(setting.PageSize);
if (currentIds.Count() == 0)
{
return(new List <ArticleModels>());
}
string sql =
"Select ID,SiteID, CardNo, Type, Title, Link, IsOpenNew, Archive, IssueDate, CustomIcon, Icon, IsShowVideo, VideoID, " +
"(SELECT TOP(1) Contents FROM Paragraph WHERE SourceNo = A.ID AND Contents <> '' ORDER BY Sort) Summary " +
$"From Article A Where ID IN ({ string.Join(", ", currentIds) })";
DataTable datas = db.GetDataTable(sql);
List <ArticleModels> items = GetListItems(datas);
List <ArticleModels> itemList = new List <ArticleModels>();
foreach (long id in currentIds)
{
ArticleModels item = items.FirstOrDefault(a => a.ID == id);
if (item != null)
{
itemList.Add(item);
}
}
return(itemList);
}
//private static void WriteLogs(string msg)
//{
// string folderPath = HttpContext.Current.Server.MapPath("~/App_Data");
// string logFileName = string.Format("log_{0}.txt", DateTime.Now.ToString("yyyyMM"));
// if (!System.IO.Directory.Exists(folderPath))
// {
// System.IO.Directory.CreateDirectory(folderPath);
// }
// System.IO.File.AppendAllText(folderPath + "\\" + logFileName, DateTime.Now.ToString()+" "+ msg+System.Environment.NewLine);
//}
public static IEnumerable <long> GetAllIDs(ArticleSettingModels setting)
{
string sql = @"Select ID From Article A Where CardNo IN (SELECT No FROM Cards WHERE ZoneNo IN (SELECT No FROM Zones Where PageNo IN
(SELECT No From Pages))) {0} Order By {1}";
string where_cond = GetWhereSql(setting, null, null);
sql = string.Format(sql, string.IsNullOrEmpty(where_cond)?"":(" AND " + where_cond), setting.SortField);
//WorkLib.WriteLog.Write(true, sql);
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(WebInfo.Conn)) {
return(conn.Query <long>(sql));
}
}
public ActionResult Next(long siteId, long menuId, int style, int pageIndex, string key, string type, long?typeId)
{
ArticleSettingModels setting = ArticleSettingDAO.GetItem(menuId);
int totalRecord;
IEnumerable <ArticleModels> items = ArticleDAO.GetItems(setting, key, type, typeId, pageIndex, out totalRecord);
ViewBag.ItemPages = CardsDAO.GetPages(items.Select(item => item.CardNo));
ViewBag.ItemTypes = ArticleDAO.GetItemTypes(items.Select(item => item.ID));
ViewBag.UploadUrl = WorkV3.Golbal.UpdFileInfo.GetVPathByMenuID(siteId, menuId).TrimEnd('/') + "/";
ViewBag.Setting = setting;
return(View("NextStyle" + style, items));
}
public static ArticleSettingModels GetItem(long menuId)
{
string sql = $"Select * From ArticleSetting Where MenuID = { menuId }";
ArticleSettingModels setting = null;
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(WebInfo.Conn)) {
setting = conn.Query <ArticleSettingModels>(sql).SingleOrDefault();
}
if (setting == null)
{
setting = new ArticleSettingModels {
MenuID = menuId,
Types = "all",
PagingMode = "頁碼分頁",
PageSize = 20,
IssueSetting = "0", // 刊登期間內的當期資料
SortMode = "時間排序",
SortField = "IsNull(IssueDate, '1900/01/01') Desc",
ReplyStatus = "開放匿名回文",
ReplyTitle = "留言區",
ReplyPageSize = 10,
ExtendReadOpen = true,
ExtendReadTitle = "推薦閱讀I",
ExtendReadMode = 1,
ExtendReadOpen2 = false,
ExtendReadTitle2 = "推薦閱讀II",
ExtendReadMode2 = 1,
ReadMode = 0
}
}
;
return(setting);
}
public static IEnumerable <ArticleModels> GetRecommendItems2(ArticleSettingModels setting, long currentItemId, IEnumerable <long> currentItemTypeIds)
{
string sql =
"Select Top({0}) ID, CardNo, Type, Title, IssueDate, CustomIcon, Icon, (SELECT TOP(1) Contents FROM Paragraph WHERE SourceNo = A.ID AND Contents <> '' ORDER BY Sort) Summary " +
"From Article A Where {1} Order By {2}";
List <string> where = new List <string>();
where.Add("IsIssue = 1 AND MenuID > 0");
where.Add("ID <> " + currentItemId);
IEnumerable <int> issueSetting = setting.GetIssueSetting();
if (issueSetting.Count() == 1)
{
if (issueSetting.Contains(0))
{
where.Add("(IssueStart IS NULL OR IssueStart <= GETDATE()) AND (IssueEnd IS NULL OR IssueEnd >= GETDATE())");
}
else
{
where.Add("IssueEnd <= GETDATE()");
}
}
string orderBy = "NewID()";
List <long> menuIds = new List <long>();
if (setting.ExtendReadMode2 == 1)
{ // 與本篇「相同類別」的其他文章優先推薦。如果筆數不足，以勾選單元的隨機文章補足。
if (currentItemTypeIds?.Count() > 0)
{
orderBy = $"ISNULL((SELECT TOP(1) 1 FROM ArticleToType WHERE ArticleID = A.ID AND TypeID IN ({ string.Join(", ", currentItemTypeIds) })), 2), Case When MenuID = { setting.MenuID } Then 1 Else 2 End, NewID()";
menuIds.Add(setting.MenuID);
}
}
else if (setting.ExtendReadMode2 == 2)
{ // 與本篇 相同關鍵字 的其他文章優先推薦 如果筆數不足 以勾選單元的隨機文章補足。
SEOModels seo = SEODAO.GetItem(currentItemId);
if (seo != null && !string.IsNullOrWhiteSpace(seo.Keywords))
{
// WriteLogs("Keywords:" + seo.Keywords);
string[] keywords = seo.Keywords.Split(';');
string fullLikeStr = "";
for (int i = 0; i < keywords.Length; i++)
{
if (!string.IsNullOrWhiteSpace(keywords[i]))
{
if (!string.IsNullOrWhiteSpace(fullLikeStr))
{
fullLikeStr += " OR ";
}
fullLikeStr += string.Format(" (Keywords LIKE '%{0}%')", keywords[i].Replace("'", "").Replace("--", ""));
}
}
if (!string.IsNullOrWhiteSpace(fullLikeStr))
{
orderBy = string.Format(@"CASE WHEN ID in (
SELECT SourceNo FROM SEO WHERE ({0})
) THEN 1 ELSE 2 END, NewID()", fullLikeStr);
}
}
}
else
{ // 在勾選單元中，隨機選擇其他文章顯示。
}
IEnumerable <long> settingExtendReadMenus = setting.GetExtendReadMenus2();
if (settingExtendReadMenus?.Count() > 0)
{
menuIds.AddRange(settingExtendReadMenus);
}
if (menuIds.Count > 0)
{
where.Add($"MenuID In ({ string.Join(", ", menuIds.Distinct()) })");
}
sql = string.Format(sql, setting.ExtendReadRowCount2, string.Join(" AND ", where), orderBy);
//WriteLogs(setting.ExtendReadMode2.ToString());
//WriteLogs(sql);
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(WebInfo.Conn))
{
return(conn.Query <ArticleModels>(sql));
}
}
public ActionResult Index(CardsModels model, string key, string type, long?typeId, int?index)
{
ViewBag.SiteID = model.SiteID;
long menuId = CardsDAO.GetMenuID(model.No);
ViewBag.Menu = MenusDAO.GetInfo(model.SiteID, menuId);
ViewBag.SitePage = CardsDAO.GetPage((long)MenusDAO.GetFirstCardNo(menuId, "Article"));
SitePage page = CardsDAO.GetPage(model.No);
ArticleSettingModels setting = ArticleSettingDAO.GetItem(menuId);
#region 文章列表是否限制會員觀看
if (setting.ReadMode == 1 && !string.IsNullOrWhiteSpace(setting.ReadModeSet))
{
Member curUser = Member.Current;
string IdentityName = ""; //限制身分的名稱
IEnumerable <Areas.Backend.Models.CategoryModels> listReadModeCategorie = Areas.Backend.Models.DataAccess.CategoryDAO.GetItems(IdentityType, setting.ReadModeSet);
foreach (var readModeList in listReadModeCategorie)
{
IdentityName += readModeList.Title + "、";
}
IdentityName = IdentityName.TrimEnd('、');
ViewBag.IdentityName = IdentityName;
if (curUser == null)
{
ViewBag.CheckReadMode = false;
ViewBag.IsLogin = false;
}
else
{
bool checkReadMode = ArticleDAO.ListCheckReadMode(curUser.ID, IdentityType, listReadModeCategorie);
if (!checkReadMode)
{
ViewBag.CheckReadMode = false;
}
}
}
#endregion
if (setting.PagingMode == "不分頁")
{
setting.PageSize = int.MaxValue;
}
IEnumerable <ArticleTypesModels> types = ArticleTypesDAO.GetItems(menuId);
if (setting.Types != "all")
{
IEnumerable <long> allowTypeIds = setting.GetTypes();
types = types.Where(t => allowTypeIds.Contains(t.ID));
setting.Types = string.Join(",", types.Select(t => t.ID));
}
ViewBag.Types = types;
int pageIndex = index ?? 1;
int totalRecord;
IEnumerable <ArticleModels> items = ArticleDAO.GetItems(setting, key, type, typeId, pageIndex, out totalRecord);
ViewBag.ItemTypes = ArticleDAO.GetItemTypes(items.Select(item => item.ID));
ViewBag.ItemPages = CardsDAO.GetPages(items.Select(item => item.CardNo));
ViewBag.UploadUrl = UpdFileInfo.GetVPathByMenuID(model.SiteID, menuId).TrimEnd('/') + "/";
ViewBag.Pagination = new Pagination {
PageSize = setting.PageSize, PageIndex = pageIndex, TotalRecord = totalRecord
};
ViewBag.Setting = setting;
ViewBag.Type = type;
int style = model.StylesID == 0 ? 1 : model.StylesID;
// style = 6;
return(View("ListStyle" + style, items));
}
public ActionResult Content(CardsModels model)
{
long menuId = CardsDAO.GetMenuID(model.No);
ArticleSettingModels setting = ArticleSettingDAO.GetItem(menuId);
ViewBag.Setting = setting;
ArticleModels item = ArticleDAO.GetItemByCard(model.No);
item.ReplyCommentSetting = setting;
IEnumerable <ArticleTypesModels> types = item.GetTypes();
ViewBag.Types = types;
IEnumerable <ArticleSeriesModels> series = item.GetSeries();
ViewBag.Series = series;
IEnumerable <ArticleCategoryModels> readModeCategories = item.GetCategories(IdentityType);
ViewBag.ReadModeCategories = readModeCategories;
string uploadUrl = UpdFileInfo.GetVPathByMenuID(model.SiteID, menuId).TrimEnd('/') + "/";
RecommendHandler(item.ID, setting, uploadUrl, types);
RecommendHandler2(item.ID, setting, uploadUrl, types);
//string key = "AllArticleID_" + menuId;
//long[] allArticleIds = HttpRuntime.Cache[key] as long[];
//if(allArticleIds == null) {
// allArticleIds = ArticleDAO.GetAllIDs(menuId).ToArray();
// HttpRuntime.Cache.Add(key, allArticleIds, null, System.Web.Caching.Cache.NoAbsoluteExpiration, new TimeSpan(0, 30, 0), System.Web.Caching.CacheItemPriority.Normal, null);
//}
long[] allArticleIds = ArticleDAO.GetAllIDs(setting).ToArray();
int index = Array.IndexOf(allArticleIds, item.ID);
ViewBag.ListSitePageIndex = (index / setting.PageSize) + 1;
if (index <= 0)
{
ViewBag.Prev = null;
}
else
{
ArticleModels prev = ArticleDAO.GetItem(allArticleIds[index - 1]);
ViewBag.Prev = prev;
ViewBag.PrevSitePage = CardsDAO.GetPage(prev.CardNo);
}
ViewBag.ListSitePage = MenusDAO.GetListPage(menuId);
if (index == allArticleIds.Length - 1)
{
ViewBag.Next = null;
}
else
{
ArticleModels next = ArticleDAO.GetItem(allArticleIds[index + 1]);
ViewBag.Next = next;
ViewBag.NextSitePage = CardsDAO.GetPage(next.CardNo);
}
SitePage page = CardsDAO.GetPage(model.No);
long pageID = page.PageNo;
ViewBag.UploadUrl = uploadUrl;
ViewBag.SiteID = model.SiteID;
ViewBag.SiteSN = page.SiteSN;
ViewBag.MenuID = menuId;
ViewBag.PageID = pageID;
ViewBag.CollectionResult = MemberShipDAO.CheckMemberCollectionExist(pageID);
ViewBag.CollectionCount = MemberShipDAO.GetPageCollectionCount(pageID);
ViewBag.Style = model.StylesID == 0 ? 1 : model.StylesID;
ArticleDAO.AddItemClicks(item.ID);
int style = model.StylesID == 0 ? 1 : model.StylesID;
string ViewFileName = string.Format("~/Views/Article/ContentStyle{0}.cshtml", style);
if (!System.IO.File.Exists(Server.MapPath(ViewFileName)))
{
style = 1;
}
ViewFileName = string.Format("~/Views/Article/ContentStyle{0}.cshtml", style);
return(View(ViewFileName, item));
}
public static ViewModels.SEORelationModel GetContentSEO(long SiteID, long MenuID, long PageNo)
{
ViewModels.SEORelationModel seoModel = new ViewModels.SEORelationModel();
string newUploadUrl = WorkV3.Golbal.UpdFileInfo.GetVPathByMenuID(SiteID, MenuID).TrimEnd('/') + "/";
//WorkLib.WriteLog.Write(true, MenuID.ToString());
List <ZonesModels> zoneList = ZonesDAO.GetPageData(SiteID, PageNo);
if (zoneList != null && zoneList.Count > 0)
{
for (int i = 0; i < zoneList.Count; i++)
{
List <CardsModels> cardList = CardsDAO.GetZoneData(SiteID, zoneList[i].No);
if (cardList != null && cardList.Count > 0)
{
switch (cardList[0].CardsType)
{
case "Article":
ArticleModels articleModel = ArticleDAO.GetItemByCard(cardList[0].No);
if (articleModel != null)
{
var authors = ArticleDAO.GetItemPosters(articleModel.ID);
if (authors != null && authors.Count() > 0)
{
foreach (ArticlePosterModels auhtor in authors)
{
seoModel.Author += auhtor.Name + ";";
}
seoModel.Author = seoModel.Author.Trim(';');
}
IEnumerable <ArticleTypesModels> types = articleModel.GetTypes();
seoModel.Keywords = string.Join(";", types.Select(t => t.Name));
if (!string.IsNullOrEmpty(articleModel.Icon)) // 取得[內文/頁面細節/自行設定代表圖]
{
ResourceImagesModels imgModel = Newtonsoft.Json.JsonConvert.DeserializeObject <ResourceImagesModels>(articleModel.Icon);
seoModel.SocialImage = newUploadUrl + imgModel.Img;
}
else
{
if (articleModel.CustomIcon) // 取得[內文 主影片 自行上傳圖片]
{
if (!string.IsNullOrEmpty(articleModel.VideoImg))
{
ResourceImagesModels imgModel = Newtonsoft.Json.JsonConvert.DeserializeObject <ResourceImagesModels>(articleModel.VideoImg);
seoModel.SocialImage = newUploadUrl + imgModel.Img;
}
}
else
{
if (!string.IsNullOrEmpty(articleModel.VideoImg))
{
seoModel.SocialImage = articleModel.VideoImg; //[ 內文 主影片 影片截圖]
}
}
}
var paragraphs = ParagraphDAO.GetItems(articleModel.ID);
if (paragraphs != null && paragraphs.Count() > 0)
{
foreach (ParagraphModels paragraph in paragraphs)
{
//都沒取到圖, 則繼續取段落的圖
if (string.IsNullOrEmpty(seoModel.SocialImage))
{
if (paragraph.MatchType == "img")
{
IEnumerable <ResourceImagesModels> images = paragraph.GetImages().Where(m => m.IsShow);
if (images != null && images.Count() > 0)
{
ResourceImagesModels imgModel = images.FirstOrDefault();
seoModel.SocialImage = newUploadUrl + imgModel.Img;
}
}
}
if (!string.IsNullOrWhiteSpace(paragraph.Contents))
{
if (string.IsNullOrEmpty(seoModel.Description))
{
seoModel.Description = paragraph.Contents.TrimTags().Truncate(100);
}
}
}
}
}
break;
case "ArticleIntro":
ArticleIntroModels articleIntroModel = ArticleIntroDAO.GetItem(cardList[0].No);
if (articleIntroModel != null)
{
ArticleSettingModels setting = ArticleSettingDAO.GetItem(MenuID);
IEnumerable <ArticleTypesModels> types = ArticleTypesDAO.GetItems(MenuID);
if (setting.Types != "all")
{
IEnumerable <long> allowTypeIds = setting.GetTypes();
types = types.Where(t => allowTypeIds.Contains(t.ID));
seoModel.Keywords = string.Join(",", types.Select(t => t.Name));
}
else
{
seoModel.Keywords = string.Join(",", types.Select(t => t.Name));
}
if (!string.IsNullOrEmpty(articleIntroModel.Icon)) // 取得[內文/頁面細節/自行設定代表圖]
{
ResourceImagesModels imgModel = Newtonsoft.Json.JsonConvert.DeserializeObject <ResourceImagesModels>(articleIntroModel.Icon);
seoModel.SocialImage = newUploadUrl + imgModel.Img;
}
var paragraphs = ParagraphDAO.GetItems(articleIntroModel.ID);
if (paragraphs != null && paragraphs.Count() > 0)
{
foreach (ParagraphModels paragraph in paragraphs)
{
//都沒取到圖, 則繼續取段落的圖
if (string.IsNullOrEmpty(seoModel.SocialImage))
{
if (paragraph.MatchType == "img")
{
IEnumerable <ResourceImagesModels> images = paragraph.GetImages().Where(m => m.IsShow);
if (images != null && images.Count() > 0)
{
ResourceImagesModels imgModel = images.FirstOrDefault();
seoModel.SocialImage = newUploadUrl + imgModel.Img;
}
}
}
if (!string.IsNullOrWhiteSpace(paragraph.Contents))
{
if (string.IsNullOrEmpty(seoModel.Description))
{
seoModel.Description = paragraph.Contents.TrimTags().Truncate(100);
}
}
}
}
}
break;
}
}
}
}
return(seoModel);
}