/// <summary>
/// 資料列表
/// </summary>
/// <param name="search"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="recordCount"></param>
/// <returns></returns>
public static IEnumerable <CustomLineNewsModels> GetItems(CustomLineNewsSearchModels search, int pageSize, int pageIndex, out int recordCount)
{
List <CustomLineNewsModels> items = new List <CustomLineNewsModels>();
if (search == null)
{
recordCount = 0;
return(items);
}
string sql = "Select ID,[SelectDate], (Select Count(*) from [CustomLINENewsData] Where [SourceID] =L.ID) AS Total from [CustomLINENews] L Where {0} Order By SelectDate Desc";
List <string> where = new List <string>();
where.Add("SiteID = " + search.SiteID);
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetPageData(string.Format(sql, string.Join(" And ", where)), pageSize, pageIndex, out recordCount);
if (datas != null)
{
foreach (DataRow dr in datas.Rows)
{
items.Add(new CustomLineNewsModels
{
ID = (long)dr["ID"],
Total = (int)dr["Total"],
SelectDate = (DateTime)dr["SelectDate"]
});
}
}
return(items);
}
public static IEnumerable <ArticleModels> GetItems(ArticleSearchModels search, int pageSize, int pageIndex, out int recordCount)
{
List <ArticleModels> items = new List <ArticleModels>();
if (search == null)
{
recordCount = 0;
return(items);
}
string sql = "Select ID, CardNo, Type, Title, Link, Archive, IsIssue, IssueStart, IssueEnd, Creator From Article A Where {0} Order By Sort, CreateTime Desc";
List <string> where = new List <string>();
where.Add("MenuID = " + search.MenuID);
if (!string.IsNullOrWhiteSpace(search.Key))
{
string key = string.Format("Like N'%{0}%'", search.Key.Replace("'", "''"));
where.Add(string.Format("(Title {0} OR Exists(Select 1 From Paragraph Where SourceNo = A.ID And (Title {0} OR Contents {0})))", key));
}
if (search.Types != null && search.Types.Count() > 0)
{
where.Add(string.Format("ID In (Select ArticleID From ArticleToType Where TypeID In ({0}))", string.Join(", ", search.Types)));
}
if (search.IssueStart != null)
{
where.Add(string.Format("IssueEnd >= '{0:yyyy/MM/dd HH:mm}'", search.IssueStart));
}
if (search.IssueEnd != null)
{
where.Add(string.Format("IssueStart <= '{0:yyyy/MM/dd HH:mm}'", search.IssueEnd));
}
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetPageData(string.Format(sql, string.Join(" And ", where)), pageSize, pageIndex, out recordCount);
foreach (DataRow dr in datas.Rows)
{
items.Add(new ArticleModels {
ID = (long)dr["ID"],
CardNo = (long)dr["CardNo"],
Type = dr["Type"].ToString().Trim(),
Title = dr["Title"].ToString().Trim(),
Link = dr["Link"].ToString().Trim(),
Archive = dr["Archive"].ToString().Trim(),
IsIssue = (bool)dr["IsIssue"],
IssueStart = dr["IssueStart"] as DateTime?,
IssueEnd = dr["IssueEnd"] as DateTime?,
Creator = (long)dr["Creator"]
});
}
return(items);
}
public static List <ArticleModels> GetItems(ArticleSetModels articleSet, int pageIndex = 1)
{
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 {0} Order By {1}";
List <string> where = new List <string>();
where.Add("IsIssue = 1 And MenuID > 0"); // 去除掉模板
List <string> orSql = new List <string>();
IEnumerable <long> menus = articleSet.GetMenus();
if (menus?.Count() > 0)
{
orSql.Add($"MenuID In ({ string.Join(", ", menus) })");
}
IEnumerable <long> types = articleSet.GetTypes();
if (types?.Count() > 0)
{
orSql.Add($"ID IN (Select ArticleID From ArticleToType Where TypeID In ({ string.Join(", ", types) }))");
}
if (orSql.Count > 0)
{
where.Add($"({ string.Join(" OR ", orSql) })");
}
IEnumerable <int> issueSetting = articleSet.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 whereSql = string.Join(" AND ", where);
sql = string.Format(sql, whereSql, articleSet.SortField);
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
int totalRecord;
DataTable datas = db.GetPageData(sql, articleSet.PageSize, pageIndex, out totalRecord);
return(GetListItems(datas));
}
public static IEnumerable <AdsStatisticsLabelsModel> GetItems(AdsStatisticsLabelsSearchModel search, int pageSize, int pageIndex, out int recordCount)
{
List <AdsStatisticsLabelsModel> items = new List <AdsStatisticsLabelsModel>();
if (search == null)
{
recordCount = 0;
return(items);
}
string sql = "Select * From AdsStatisticsLabels Where 1=1 {0} Order By LabelDate Desc";
List <string> where = new List <string>();
if (!string.IsNullOrWhiteSpace(search.Keyword))
{
where.Add(string.Format(" ( Title Like N'%{0}%' )", search.Keyword.Replace("'", "''")));
}
if (search.StartDate.HasValue)
{
where.Add(string.Format(" LabelDate >= '{0:yyyy/MM/dd HH:mm}' ", search.StartDate));
}
if (search.EndDate.HasValue)
{
where.Add(string.Format(" LabelDate <= '{0:yyyy/MM/dd HH:mm}' ", search.EndDate));
}
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetPageData(string.Format(sql, where.Count > 0? " And " + string.Join(" And ", where):""), pageSize, pageIndex, out recordCount);
if (datas != null)
{
foreach (DataRow dr in datas.Rows)
{
items.Add(new AdsStatisticsLabelsModel
{
ID = (long)dr["ID"],
Title = dr["Title"].ToString().Trim(),
LabelColor = dr["LabelColor"].ToString().Trim(),
LabelDate = (DateTime)dr["LabelDate"],
ShowStatus = (bool)dr["ShowStatus"],
CreateTime = DateTime.Parse(dr["CreateTime"].ToString()),
Creator = (long)dr["Creator"],
ModifyTime = DateTime.Parse(dr["ModifyTime"].ToString()),
Modifier = (long)dr["Modifier"]
});
}
}
return(items);
}
public static IEnumerable <PointsModel> GetItems(long siteId, long memberShipID, int pageSize, int pageIndex, out int recordCount)
{
List <PointsModel> items = new List <PointsModel>();
string sql = "Select * From Points Where {0} Order By CreateTime desc";
List <string> where = new List <string>();
where.Add(" SiteID = " + siteId + " and MemberShipID = " + memberShipID);
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetPageData(string.Format(sql, string.Join(" And ", where)), pageSize, pageIndex, out recordCount);
foreach (DataRow dr in datas.Rows)
{
int pointType = 0;
if (string.IsNullOrWhiteSpace(dr["PointType"].ToString()))
{
if (dr["Point"].ToString().StartsWith("-"))
{
pointType = 1;
}
}
else
{
pointType = (int)dr["PointType"];
}
items.Add(new PointsModel
{
ID = (long)dr["ID"],
SiteID = (long)dr["SiteID"],
MemberShipID = (long)dr["MemberShipID"],
Remark = dr["Remark"].ToString().Trim(),
Description = dr["Description"].ToString().Trim(),
Point = (decimal)dr["Point"],
IsManually = (bool)dr["IsManually"],
CreateTime = (DateTime)dr["CreateTime"],
PointType = pointType
});
}
return(items);
}
/// <summary>
/// 後端編輯頁文章列表
/// </summary>
public static IEnumerable <CustomArticleListItemModel> GetItems(long menuId, string key, int pageSize, int pageIndex, out int recordCount)
{
List <CustomArticleListItemModel> items = new List <CustomArticleListItemModel>();
string sql =
"Select A.ID ArticleID, A.Title, M.Name Creator, S.SN SiteSN, P.SN PageSN " +
"From Article A " +
" JOIN Cards ON A.CardNo = Cards.No " +
" JOIN Zones Z ON Cards.ZoneNo = Z.NO " +
" JOIN Pages P ON Z.PageNo = P.No " +
" JOIN Member M ON A.Creator = M.ID " +
" JOIN Sites S ON A.SiteID = S.ID " +
"Where {0} Order By A.CreateTime Desc";
List <string> where = new List <string>();
where.Add("A.IsIssue = 1 AND A.MenuID > 0");
where.Add($"A.ID Not In (Select ArticleID From CustomArticle Where MenuID = { menuId })");
if (!string.IsNullOrWhiteSpace(key))
{
key = string.Format("Like N'%{0}%'", key.Replace("'", "''"));
where.Add(string.Format("(A.Title {0} OR Exists(Select 1 From Paragraph Where SourceNo = A.ID And (Title {0} OR Contents {0})))", key));
}
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetPageData(string.Format(sql, string.Join(" And ", where)), pageSize, pageIndex, out recordCount);
foreach (DataRow dr in datas.Rows)
{
items.Add(new CustomArticleListItemModel {
MenuID = 0,
ArticleID = (long)dr["ArticleID"],
Title = dr["Title"].ToString().Trim(),
Creator = dr["Creator"].ToString().Trim(),
SiteSN = dr["SiteSN"].ToString().Trim(),
PageSN = dr["PageSN"].ToString().Trim()
});
}
return(items);
}
/// <summary>
/// 前端列表頁文章列表
/// </summary>
public static IEnumerable <ArticleModels> GetItems(long menuId, int pageSize, int pageIndex, out int recordCount)
{
List <CustomArticleListItemModel> items = new List <CustomArticleListItemModel>();
string sql =
$"Select ID, CardNo, Type, Title, Link, IsOpenNew, Archive, IssueDate, CustomIcon, Icon, (SELECT TOP(1) Contents FROM Paragraph WHERE SourceNo = A.ID AND Contents <> '' ORDER BY Sort) Summary " +
$"From Article A Left Join CustomArticle C ON A.ID = C.ArticleID AND C.MenuID = { menuId } " +
$"Where {{0}} Order By IsNull(C.Sort, { int.MaxValue }), A.CreateTime Desc";
List <string> where = new List <string>();
where.Add("A.IsIssue = 1");
where.Add("(A.IssueStart IS NULL OR A.IssueStart <= GETDATE()) AND (A.IssueEnd IS NULL OR A.IssueEnd >= GETDATE())");
where.Add("A.MenuID > 0");
sql = string.Format(sql, string.Join(" AND ", where));
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetPageData(sql, pageSize, pageIndex, out recordCount);
return(GetListItems(datas));
}
public static List <ArticleModels> GetItems(long menuId, long?typeId, int pageSize, int pageIndex, out int recordCount)
{
List <ArticleModels> items = new List <ArticleModels>();
string typeWhere = string.Empty;
if (typeId != null)
{
typeWhere = "AND ID IN (Select ArticleID From ArticleToType Where TypeID = " + typeId;
}
string sql =
"Select 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 IsIssue = 1 AND MenuID = { menuId } AND (IssueStart IS NULL OR IssueStart <= GETDATE()) AND (IssueEnd IS NULL OR IssueEnd >= GETDATE()) " +
typeWhere + " Order By Sort, CreateTime Desc";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetPageData(sql, pageSize, pageIndex, out recordCount);
if (datas != null && datas.Rows.Count > 0)
{
foreach (DataRow dr in datas.Rows)
{
items.Add(new ArticleModels {
ID = (long)dr["ID"],
CardNo = (long)dr["CardNo"],
Type = dr["Type"].ToString().Trim(),
Title = dr["Title"].ToString().Trim(),
IssueDate = dr["IssueDate"] as DateTime?,
Icon = dr["Icon"].ToString().Trim(),
Summary = dr["Summary"].ToString().Trim()
});
}
}
return(items);
}
public static IEnumerable <FormItem> GetItems(FormItemSearch search, int pageSize, int pageIndex, out int recordCount)
{
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetPageData(GetSearchSql(search), pageSize, pageIndex, out recordCount);
List <FormItem> items = new List <FormItem>();
if (datas == null || datas.Rows.Count == 0)
{
return(items);
}
foreach (DataRow dr in datas.Rows)
{
items.Add(new FormItem {
ID = (long)dr["ID"], FormID = search.FormID, CreateDate = (DateTime)dr["CreateDate"], CheckStatus = (byte)dr["CheckStatus"], CheckDate = dr["CheckDate"] as DateTime?,
IsBack = (bool)dr["IsBack"], SN = dr["SN"] as int?, Remark = dr["Remark"].ToString().Trim(), Email = dr["Email"].ToString().Trim(), Phone = dr["Phone"].ToString().Trim(),
Mobile = dr["Mobile"].ToString().Trim(), IDCard = dr["IDCard"].ToString().Trim(), CheckInDate = dr["CheckInDate"] as DateTime?,
IsProcess = dr["IsProcess"] == DBNull.Value? false: (bool)dr["IsProcess"], ProcessRemark = dr["ProcessRemark"].ToString(), ProcessTime = dr["ProcessTime"] as DateTime?
});
}
return(items);
}
/// <summary>
/// 新增資料列表
/// </summary>
/// <param name="search"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="recordCount"></param>
/// <returns></returns>
public static IEnumerable <CustomLineNewsDataModels> GetDataSearchItems(CustomLineNewsSearchModels search, int pageSize, int pageIndex, out int recordCount)
{
List <CustomLineNewsDataModels> items = new List <CustomLineNewsDataModels>();
recordCount = 0;
if (search == null)
{
return(items);
}
#region 找資料
if (search.SiteID <= 0 | search.SelectMenuID <= 0)
{
return(items);
}
string DataType = "";
string sql;
List <string> where = new List <string>();
where.Add("SiteID = " + search.SiteID);
where.Add("MenuID = " + search.SelectMenuID);
MenusModels M = MenusDAO.GetInfo(search.SiteID, search.SelectMenuID);
if (M.DataType != null)
{
DataType = M.DataType;
switch (DataType.ToLower())
{
case "events":
sql = "Select ID as SelectID, Title From Events Where {0} Order By sort Asc ";
if (!string.IsNullOrWhiteSpace(search.Key))
{
string key = string.Format("Like N'%{0}%'", search.Key.Replace("'", "''"));
//where.Add(string.Format("(Title {0} OR Exists(Select 1 From Paragraph Where SourceNo = A.ID And (Title {0} OR Contents {0})))", key));
where.Add(string.Format("Title {0}", key));
}
break;
case "article":
default:
if (!string.IsNullOrWhiteSpace(search.Key))
{
string key = string.Format("Like N'%{0}%'", search.Key.Replace("'", "''"));
//where.Add(string.Format("(Title {0} OR Exists(Select 1 From Paragraph Where SourceNo = A.ID And (Title {0} OR Contents {0})))", key));
where.Add(string.Format("Title {0}", key));
}
sql = "Select ID as SelectID, Title From Article Where {0} Order By sort Asc";
break;
}
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetPageData(string.Format(sql, string.Join(" And ", where)), pageSize, pageIndex, out recordCount);
if (datas != null)
{
foreach (DataRow dr in datas.Rows)
{
items.Add(new CustomLineNewsDataModels
{
SelectMenuID = M.ID,
MenuTitle = M.Title,
SelectID = (long)dr["SelectID"],
Title = dr["Title"].ToString()
});
}
}
}
#endregion
return(items);
}
