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