public static IEnumerable <ArticleModels> GetRecommendItems(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.ExtendReadMode == 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.ExtendReadMode == 2)
{ // 與本篇 相同關鍵字 的其他文章優先推薦 如果筆數不足 以勾選單元的隨機文章補足。
SEOModels seo = SEODAO.GetItem(currentItemId);
if (seo != null && !string.IsNullOrWhiteSpace(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.GetExtendReadMenus();
if (settingExtendReadMenus?.Count() > 0)
{
menuIds.AddRange(settingExtendReadMenus);
}
if (menuIds.Count > 0)
{
where.Add($"MenuID In ({ string.Join(", ", menuIds.Distinct()) })");
}
sql = string.Format(sql, setting.ExtendReadRowCount, string.Join(" AND ", where), orderBy);
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(WebInfo.Conn)) {
return(conn.Query <ArticleModels>(sql));
}
}
