public static List <PageCommentsModels> GetItems(long SiteID, string PageSN, int pageIndex, int rowCount, out int totalRecord)
{
List <PageCommentsModels> itemList = new List <PageCommentsModels>();
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
string query = $"Select Count(1) From PageComments Where PageSN='{PageSN.Replace("'", "")}' AND ShowStatus=1 AND ParentID IS NULL ";
DataTable countTable = db.GetDataTable(query);
totalRecord = 0;
if (countTable == null || countTable.Rows.Count <= 0)
{
return(itemList);
}
if (string.IsNullOrEmpty(countTable.Rows[0][0].ToString()))
{
return(itemList);
}
totalRecord = int.Parse(countTable.Rows[0][0].ToString());
string cond = $" PageSN='{PageSN.Replace("'", "")}' AND ShowStatus=1 AND ParentID IS NULL ";
string orderby = " Order By PostDate ASC ";
string sql =
$"Select TOP {rowCount} * From PageComments Where {cond} AND ID NOT IN (SELECT TOP {pageIndex * rowCount} ID FROM PageComments Where {cond} {orderby}) {orderby} ";
DataTable datas = db.GetDataTable(sql);
itemList = GetListItems(SiteID, datas);
return(itemList);
}
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);
}
public static IEnumerable <IntraIPlimitModel> GetItems(bool IsAddInit = true)
{
List <IntraIPlimitModel> items = new List <IntraIPlimitModel>();
string sql = "Select * From [IntraIPlimit] Order By IsSystemSet DESC, IP_Begin Asc";
List <string> where = new List <string>();
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetDataTable(sql);
if (IsAddInit)
{
if (datas.Rows.Count <= 0) // 若都是空的, 自動加入全域的一筆, 本筆不可刪除
{
IntraIPlimitModel initModel = new IntraIPlimitModel();
initModel.OpenStatus = OpenStatus.Open;
initModel.IP_Begin = "*";
initModel.IP_End = "*";
initModel.IP_BeginNum = 0;
initModel.IP_EndNum = 0;
initModel.CreateTime = DateTime.Now;
initModel.Creator = MemberDAO.SysCurrent.Id;
initModel.IsSystemSet = true;
SetItem(initModel);
datas = db.GetDataTable(sql);
}
}
foreach (DataRow dr in datas.Rows)
{
IntraIPlimitModel item = new IntraIPlimitModel();
item.ID = int.Parse(dr["ID"].ToString());
item.OpenStatus = (OpenStatus)(int.Parse(dr["OpenStatus"].ToString()));
item.IP_Begin = dr["IP_Begin"].ToString().Trim();
item.IP_End = dr["IP_End"].ToString().Trim();
item.IP_BeginNum = long.Parse(dr["IP_BeginNum"].ToString());
item.IP_EndNum = long.Parse(dr["IP_EndNum"].ToString());
item.Creator = long.Parse(dr["Creator"].ToString());
item.CreateTime = DateTime.Parse(dr["CreateTime"].ToString());
item.Modifier = string.IsNullOrEmpty(dr["Modifier"].ToString()) ? (long?)null : long.Parse(dr["Modifier"].ToString());
item.ModifyTime = string.IsNullOrEmpty(dr["ModifyTime"].ToString()) ? (DateTime?)null : DateTime.Parse(dr["ModifyTime"].ToString());
item.IsSystemSet = bool.Parse(dr["IsSystemSet"].ToString());
items.Add(item);
}
return(items);
}
public static IEnumerable <SearchMenuModel> GetSearchMenus(long siteId)
{
string sql =
"SELECT ID, Title, DataType Module, ParentID, " +
" CASE DataType " +
" WHEN 'Article' THEN (SELECT DefaultImg FROM ArticleSetting WHERE MenuID = M.ID) " +
// " WHEN 'Event' THEN (SELECT DefaultImg FROM EventSetting WHERE MenuID = M.ID) " + //Joe 20190916尚無此資料表故先註解
" ELSE NULL END Icon " +
$"FROM Menus M WHERE SiteID = { siteId } AND ShowStatus = 1 AND DataType IN ('ArticleIntro', 'Article', 'Event', 'CustomShops') " +
"ORDER BY (CASE AreaID WHEN 2 THEN 1 WHEN 1 THEN 2 ELSE AreaID END), Sort";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetDataTable(sql);
List <SearchMenuModel> menus = new List <SearchMenuModel>();
foreach (DataRow dr in datas.Rows)
{
string iconJson = dr["Icon"].ToString().Trim();
string icon = (iconJson == string.Empty) ? string.Empty : Newtonsoft.Json.JsonConvert.DeserializeObject <ResourceImagesModels>(iconJson).Img;
long id = (long)dr["ID"];
menus.Add(new SearchMenuModel {
ID = id,
ParentID = (long)dr["ParentID"],
Module = dr["Module"].ToString().Trim(),
Title = dr["Title"].ToString().Trim(),
UploadUrl = WorkV3.Golbal.UpdFileInfo.GetVPathByMenuID(siteId, id).TrimEnd('/') + "/",
Icon = icon
});
}
return(menus);
}
public static List <CategoryModels> GetItems(string Type, string Ids = "")
{
List <CategoryModels> items = new List <CategoryModels>();
string sql = "SELECT * FROM Categories WHERE 1=1 ";
if (!string.IsNullOrEmpty(Type))
{
sql += $"AND (Type='" + Type.Replace(",", "','") + "') ";
}
if (!string.IsNullOrWhiteSpace(Ids))
{
sql += $"AND ID in ({Ids}) ";
}
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetDataTable(sql);
foreach (DataRow dr in datas.Rows)
{
CategoryModels m = new CategoryModels();
m.ID = (long)dr["ID"];
m.Type = dr["Type"].ToString().Trim();
m.Title = dr["Title"].ToString().Trim();
m.RemarkText = dr["RemarkText"].ToString().Trim();
m.ShowStatus = Convert.ToBoolean(dr["ShowStatus"].ToString());
m.Icon = dr["Icon"].ToString().Trim();
m.Sort = Convert.ToInt32(dr["Sort"].ToString());
m.Image = dr["Image"].ToString().Trim();
m.MemberSession = (!string.IsNullOrWhiteSpace(dr["MemberSession"].ToString()) ? Convert.ToInt32(dr["MemberSession"].ToString()) : 0);
items.Add(m);
}
return(items);
}
/// <summary>
/// 找尋選單
/// </summary>
/// <param name="SiteID"></param>
/// <returns></returns>
public static IEnumerable <MenusModels> GetMenusItems(long SiteID)
{
List <MenusModels> items = new List <MenusModels>();
string sql = "Select ID, Title From Menus where {0} order by AreaID Asc, Sort desc";
List <string> where = new List <string>();
where.Add("SiteID = " + SiteID);
where.Add("[DataType] in('Article','Event')"); /*,'ArticleIntro'*/
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetDataTable(string.Format(sql, string.Join(" And ", where)));
if (datas != null)
{
foreach (DataRow dr in datas.Rows)
{
items.Add(new MenusModels
{
ID = (long)dr["Id"],
Title = dr["Title"].ToString()
});
}
}
return(items);
}
public static MemberShipRegSocialSetModels GetSocialItem(long SiteID, int Sort, WorkV3.Models.MemberType SocialType, string SocialTitle)
{
string sql = $"SELECT * FROM MemberShipRegSocialSet WHERE SiteID={SiteID} AND SocialType={(int)SocialType} ";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetDataTable(sql);
DateTime now = DateTime.Now;
if (datas == null || datas.Rows.Count <= 0)
{
MemberShipRegSocialSetModels m = new MemberShipRegSocialSetModels();
m.SiteID = SiteID;
m.SocialType = SocialType;
m.SocialTitle = SocialTitle;
m.Sort = Sort;
m.IsOpen = false; // default
m.SecretKey = "";
m.AppID = "";
m.Scope = "";
m.Creator = Areas.Backend.Models.DataAccess.MemberDAO.SysCurrent.Id;
m.CreateTime = now;
m.Modifier = WorkV3.Areas.Backend.Models.DataAccess.MemberDAO.SysCurrent.Id;
m.ModifyTime = now;
m.BackendIsOpen = false;
SQLData.TableObject tableObj = db.GetTableObject("MemberShipRegSocialSet");
tableObj["SiteID"] = m.SiteID;
tableObj["SocialType"] = (int)m.SocialType;
tableObj["SocialTitle"] = m.SocialTitle;
tableObj["IsOpen"] = m.IsOpen;
tableObj["SecretKey"] = m.SecretKey;
tableObj["AppID"] = m.AppID;
tableObj["Scope"] = m.Scope;
tableObj["Sort"] = m.Sort;
tableObj["Creator"] = m.Creator;
tableObj["CreateTime"] = m.CreateTime;
tableObj["Modifier"] = m.Modifier;
tableObj["ModifyTime"] = m.ModifyTime;
tableObj["BackendIsOpen"] = m.BackendIsOpen;
tableObj.Insert();
return(m);
}
else
{
DataRow dr = datas.Rows[0];
MemberShipRegSocialSetModels m = new MemberShipRegSocialSetModels();
m.SiteID = (long)dr["SiteID"];
m.SocialType = (WorkV3.Models.MemberType)((int)dr["SocialType"]);
m.SocialTitle = dr["SocialTitle"].ToString();
m.IsOpen = Convert.ToBoolean(dr["IsOpen"].ToString());
m.SecretKey = dr["SecretKey"].ToString();
m.AppID = dr["AppID"].ToString();
m.Scope = dr["Scope"].ToString();
m.Sort = (int)dr["Sort"];
m.Creator = (long)dr["Creator"];
m.CreateTime = (DateTime)dr["CreateTime"];
m.Modifier = (long)dr["Modifier"];
m.ModifyTime = (DateTime)dr["ModifyTime"];
return(m);
}
}
public static MemberShipRegSocialSetModels GetSocialItem(long SiteID, WorkV3.Models.MemberType SocialType)
{
string sql = $"SELECT * FROM MemberShipRegSocialSet WHERE SiteID={SiteID} AND SocialType={(int)SocialType} ";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetDataTable(sql);
DateTime now = DateTime.Now;
if (datas == null || datas.Rows.Count <= 0)
{
return(null);
}
else
{
DataRow dr = datas.Rows[0];
MemberShipRegSocialSetModels m = new MemberShipRegSocialSetModels();
m.SiteID = (long)dr["SiteID"];
m.SocialType = (WorkV3.Models.MemberType)((int)dr["SocialType"]);
m.SocialTitle = dr["SocialTitle"].ToString();
m.IsOpen = Convert.ToBoolean(dr["IsOpen"].ToString());
m.SecretKey = dr["SecretKey"].ToString();
m.AppID = dr["AppID"].ToString();
m.Scope = dr["Scope"].ToString();
m.Sort = (int)dr["Sort"];
m.Creator = (long)dr["Creator"];
m.CreateTime = (DateTime)dr["CreateTime"];
m.Modifier = (long)dr["Modifier"];
m.ModifyTime = (DateTime)dr["ModifyTime"];
return(m);
}
}
public static List <SysLogModels> GetAll()
{
List <SysLogModels> items = new List <SysLogModels>();
string sql = "SELECT s.*,m.LoginID as MemberLoginID,m.Name as MemberName,n.Title as MenuTitle FROM SysLog s JOIN Member m ON s.MemberID=m.ID LEFT JOIN Menus n ON n.ID=s.MenuID ";
sql += " ORDER BY s.AddTime DESC ";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetDataTable(sql);
foreach (DataRow dr in datas.Rows)
{
SysLogModels m = new SysLogModels();
m.Id = (long)dr["ID"];
m.MemberID = (long)dr["MemberID"];
m.MemberName = dr["MemberName"].ToString().Trim();
m.MemberLoginID = dr["MemberLoginID"].ToString().Trim();
m.MgrNo = Convert.ToByte(dr["MgrNo"]);
m.MgrNoName = ((SysMgrNoName)Convert.ToByte(dr["MgrNo"])).ToString();
m.Actions = Convert.ToByte(dr["Actions"]);
m.ActionsName = ((SysActionsName)Convert.ToByte(dr["Actions"])).ToString();
m.MenuID = Convert.ToInt64(m.MenuID ?? 0);
m.MenuTitle = dr["MenuTitle"].ToString().Trim();
m.ReMark = dr["ReMark"].ToString().Trim();
m.AddTime = (DateTime)dr["AddTime"];
m.IP = dr["IP"].ToString().Trim();
items.Add(m);
}
return(items);
}
public static Dictionary <long, List <ArticleTypesModels> > GetItemTypes(IEnumerable <long> itemIds)
{
Dictionary <long, List <ArticleTypesModels> > types = new Dictionary <long, List <ArticleTypesModels> >();
if (itemIds == null || itemIds.Count() == 0)
{
return(types);
}
string sql = "SELECT I.ArticleID, I.TypeID, T.Name FROM ArticleToType I JOIN ArticleTypes T ON I.TypeID = T.ID WHERE I.ArticleID IN ({0}) ORDER BY T.Sort";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetDataTable(string.Format(sql, string.Join(", ", itemIds)));
foreach (DataRow dr in datas.Rows)
{
long articleId = (long)dr["ArticleID"];
List <ArticleTypesModels> articleTypes;
if (!types.TryGetValue(articleId, out articleTypes))
{
articleTypes = new List <ArticleTypesModels>();
types.Add(articleId, articleTypes);
}
articleTypes.Add(new ArticleTypesModels {
ID = (long)dr["TypeID"],
Name = dr["Name"].ToString().Trim()
});
}
return(types);
}
public static ResourceFilesModels GetInfo(long SiteID, long SourceNo, byte SourceType, int AreaID, long Id, int Ver)
{
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
string Sql = "Select * From ResourceFiles Where Id=@Id and SiteID=@SiteID and SourceNo=@SourceNo and SourceType=@SourceType and Ver=@Ver and AreaID=@AreaID";
SQLData.ParameterCollection para = new SQLData.ParameterCollection();
para.Add("@ID", Id);
para.Add("@SiteID", SiteID);
para.Add("@SourceNo", SourceNo);
para.Add("@SourceType", SourceType);
para.Add("@Ver", Ver);
para.Add("@AreaID", AreaID);
DataTable dt = db.GetDataTable(Sql, para);
ResourceFilesModels _TempRow = null;
if (dt.Rows.Count > 0)
{
_TempRow = new ResourceFilesModels();
_TempRow = CreateData(dt.Rows[0]);
}
return(_TempRow);
}
public static List <ResourceFilesModels> GetInfoAll(long SiteID, long SourceNo, long SourceType, int AreaID, int Ver)
{
string Sql = "Select * From ResourceFiles Where SiteID=@SiteID and SourceNo=@SourceNo and SourceType=@SourceType and Ver=@Ver and AreaID=@AreaID";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
SQLData.ParameterCollection para = new SQLData.ParameterCollection();
para.Add("@SiteID", SiteID);
para.Add("@SourceNo", SourceNo);
para.Add("@SourceType", SourceType);
para.Add("@Ver", Ver);
para.Add("@AreaID", AreaID);
DataTable dt = db.GetDataTable(Sql, para);
List <ResourceFilesModels> nLists = new List <ResourceFilesModels>();
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
ResourceFilesModels _TempRow = new ResourceFilesModels();
_TempRow = CreateData(dt.Rows[i]);
nLists.Add(_TempRow);
}
}
return(nLists);
}
public static IEnumerable <AdsStatisticsLabelsModel> GetShowLabelLine(DateTime startDate, DateTime endDate)
{
List <AdsStatisticsLabelsModel> items = new List <AdsStatisticsLabelsModel>();
string sql = "Select * From AdsStatisticsLabels Where ShowStatus=1 AND LabelDate>=@StartDate AND LabelDate<=@EndDate Order By LabelDate Asc";
SQLData.ParameterCollection paraList = new SQLData.ParameterCollection();
paraList.Add("@StartDate", startDate);
paraList.Add("@EndDate", endDate);
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetDataTable(sql, paraList);
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 List <MemberShipRegEmailManagersModels> GetMemberShipRegManagers(long SiteID)
{
List <MemberShipRegEmailManagersModels> modelList = new List <MemberShipRegEmailManagersModels>();
string sql = $"SELECT * FROM MemberShipRegEmailManagers WHERE SiteID={SiteID} ";
DateTime now = DateTime.Now;
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetDataTable(sql);
if (datas != null && datas.Rows.Count > 0)
{
for (int i = 0; i < datas.Rows.Count; i++)
{
MemberShipRegEmailManagersModels m = new MemberShipRegEmailManagersModels();
m.ID = datas.Rows[i]["ID"].ToString();
m.SiteID = SiteID;
m.IsManager = (bool)datas.Rows[i]["IsManager"];
m.ManagerID = datas.Rows[i]["ManagerID"].ToString();
m.Email = datas.Rows[i]["Email"].ToString();
m.Sort = (int)datas.Rows[i]["Sort"];
if (m.IsManager)
{
MemberModels mem = Models.DataAccess.ManagerDAO.GetItem(long.Parse(m.ManagerID));
if (mem != null)
{
m.ManagerName = mem.Name;
m.Email = mem.Email;
}
}
modelList.Add(m);
}
}
return(modelList);
}
public static Dictionary <long, SitePage> GetPages(IEnumerable <long> cardNos)
{
if (cardNos == null || cardNos.Count() == 0)
{
return(null);
}
string sql =
"SELECT S.ID SiteID, S.SN SiteSN, S.Title SiteName, P.No PageNo, P.SN PageSN, P.MenuID, M.SN MenuSN, M.Title MenuName, C.No CardNo " +
"FROM Pages P JOIN Sites S ON P.SiteID = S.ID JOIN Zones Z ON P.No = Z.PageNo JOIN Cards C ON C.ZoneNo = Z.No JOIN Menus M ON P.MenuID = M.ID " +
$"WHERE C.No In ({ string.Join(", ", cardNos) })";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetDataTable(sql);
if (datas == null || datas.Rows.Count == 0)
{
return(null);
}
Dictionary <long, SitePage> pages = new Dictionary <long, SitePage>();
foreach (DataRow dr in datas.Rows)
{
pages.Add((long)dr["CardNo"], new SitePage {
SiteID = (long)dr["SiteID"], SiteSN = dr["SiteSN"].ToString().Trim(), SiteName = dr["SiteName"].ToString().Trim(),
MenuName = dr["MenuName"].ToString().Trim(), PageNo = (long)dr["PageNo"], PageSN = dr["PageSN"].ToString().Trim(), MenuID = (long)dr["MenuID"], MenuSN = dr["MenuSN"].ToString().Trim()
});
}
return(pages);
}
/// <summary>
/// 檢查是否已投過票, 依 IP 及 MemberID (若非 NULL 時) 目前限制為 10 分鐘, return: true 已投過, 時效未過 | false: 未投過或時效已過
/// </summary>
/// <param name="commentID"></param>
/// <param name="LogType"></param>
/// <param name="MemberID"></param>
/// <returns></returns>
public static bool IsAddGoodLogOverTime(long commentID, ViewModels.CommentLogType LogType, long?MemberID)
{
//因應 cc 所提, 先不限制時間, 故 always false 2018-04-18 charlie_shan
return(false);
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
string AddCol = "";
SQLData.ParameterCollection paras = new SQLData.ParameterCollection();
paras.Add("@CommentID", commentID);
paras.Add("@LogType", (int)LogType);
paras.Add("@IP", WorkLib.GetItem.IPAddr());
if (MemberID.HasValue)
{
AddCol = " AND MemberShipID=@MemberShipID ";
paras.Add("@MemberShipID", MemberID.Value);
}
string sql = string.Format(@" SELECT TOP 1 * FROM PageCommentLogs WHERE CommentID=@CommentID AND LogType=@LogType AND IP=@IP {0} Order By AddTime DESC "
, AddCol);
DataTable logTable = db.GetDataTable(sql, paras);
if (logTable == null || logTable.Rows.Count <= 0)
{
return(false);
}
DateTime LastAddTime = DateTime.Parse(logTable.Rows[0]["AddTime"].ToString());
if (DateTime.Now.Subtract(LastAddTime).TotalMinutes > 10)
{
return(false);
}
return(true);
}
public static PageCommentsModels GetSingleItem(long SiteID, string ID)
{
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
string cond = $" ID='{ID.Replace("'", "")}' AND ShowStatus=1 ";
string sql =
$"Select * From PageComments Where {cond} ";
DataTable datas = db.GetDataTable(sql);
if (datas != null)
{
foreach (DataRow dr in datas.Rows)
{
PageCommentsModels item = new PageCommentsModels();
item.ID = long.Parse(dr["ID"].ToString());
item.CommentID = dr["ID"].ToString();
item.PageSN = dr["PageSN"].ToString();
item.ParentID = string.IsNullOrEmpty(dr["ParentID"].ToString()) ? (long?)null : (long)dr["ParentID"];
item.Title = dr["Title"].ToString().Trim();
item.PosterName = dr["PosterName"].ToString().Trim();
item.PostDate = (DateTime)dr["PostDate"];
item.PostDateDiff = GetDateDiffNow(item.PostDate);
item.ShowStatus = Convert.ToBoolean(dr["ShowStatus"].ToString());
item.MemberShipID = string.IsNullOrEmpty(dr["MemberShipID"].ToString()) ? (long?)null : (long)dr["MemberShipID"];
item.CommentContent = dr["CommentContent"].ToString().Trim();
item.IP = dr["IP"].ToString().Trim();
item.IPNum = dr["IPNum"].ToString().Trim();
item.GoodCount = int.Parse(dr["GoodCount"].ToString());
item.Modifier = string.IsNullOrEmpty(dr["Modifier"].ToString()) ? (long?)null : (long)dr["Modifier"];
item.ModifyTime = (DateTime)dr["ModifyTime"];
item.ChildCommentList = GetChildItems(SiteID, item.ID);
return(item);
}
}
return(null);
}
public static string GetAddressFieldValue(FieldAddress item)
{
if (item == null)
{
return(string.Empty);
}
if (item.Regions == null || item.Regions.Length == 0)
{
return(item.Address);
}
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
string sql = $"Select ID, Name From WorldRegion Where ID In ({ string.Join(", ", item.Regions) })";
IEnumerable <DataRow> datas = db.GetDataTable(sql).AsEnumerable();
string address = string.Empty;
foreach (int regionId in item.Regions)
{
address += datas.Where(dr => (int)dr["ID"] == regionId).Select(dr => dr["Name"].ToString()).FirstOrDefault();
}
return(address + item.Address);
}
/// <summary>
/// 取得 model 內容
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static SocialRelationModels GetRelationItem(long id)
{
SocialRelationModels item = null;
string sql = $"Select * from [SocialRelations] Where ID={id}";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetDataTable(sql);
if (datas != null && datas.Rows.Count > 0)
{
DataRow dr = datas.Rows[0];
item = new SocialRelationModels()
{
ID = dr["ID"].ToString(),
SiteID = (long)dr["SiteID"],
RelationType = (RelationType)((int)dr["RelationType"]),
SocialType = (WorkV3.Models.MemberType)((int)dr["SocialType"]),
SocialTitle = dr["SocialTitle"].ToString(),
IsOpen = (bool)dr["IsOpen"],
ShowType = (ShowType)((int)dr["ShowType"]),
Sort = (int)dr["Sort"],
LinkTitle = dr["LinkTitle"].ToString(),
LinkUrl = dr["LinkUrl"].ToString(),
Creator = (long)dr["Creator"],
CreateTime = (DateTime)dr["CreateTime"],
Modifier = (long)dr["Modifier"],
ModifyTime = (DateTime)dr["ModifyTime"]
};
}
return(item);
}
/// <summary>
/// 取得 model 內容
/// </summary>
/// <param name="siteID"></param>
/// <returns></returns>
public static SocialSettingModels GetItem(long siteID)
{
SocialSettingModels item = null;
string sql = $"Select * from [SocialSetting] Where SiteID={siteID}";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetDataTable(sql);
if (datas != null && datas.Rows.Count > 0)
{
DataRow dr = datas.Rows[0];
item = new SocialSettingModels()
{
SiteID = (long)dr["SiteID"],
IsOpen = (bool)dr["IsOpen"],
SocialDefaultImage = dr["SocialDefaultImage"].ToString(),
IsHeaderOpenChannel = (bool)dr["IsHeaderOpenChannel"],
IsFooterOpenChannel = (bool)dr["IsFooterOpenChannel"],
IsEDMOpenChannel = (bool)dr["IsEDMOpenChannel"],
Creator = (long)dr["Creator"],
CreateTime = (DateTime)dr["CreateTime"],
Modifier = (long)dr["Modifier"],
ModifyTime = (DateTime)dr["ModifyTime"]
};
}
return(item);
}
public static Models.PageCommentLogsModels GetGoodLogs(long commentID, ViewModels.CommentLogType LogType, long MemberID)
{
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
SQLData.ParameterCollection paras = new SQLData.ParameterCollection();
paras.Add("@CommentID", commentID);
paras.Add("@MemberShipID", MemberID);
paras.Add("@LogType", (int)LogType);
string sql = @" SELECT * FROM PageCommentLogs WHERE CommentID=@CommentID AND MemberShipID=@MemberShipID AND LogType=@LogType";
DataTable logTable = db.GetDataTable(sql, paras);
if (logTable != null && logTable.Rows.Count > 0)
{
DataRow dr = logTable.Rows[0];
PageCommentLogsModels item = new PageCommentLogsModels();
item.ID = long.Parse(dr["ID"].ToString());
item.CommentID = long.Parse(dr["CommentID"].ToString());
item.MemberShipID = long.Parse(dr["MemberShipID"].ToString());
item.LogType = int.Parse(dr["LogType"].ToString());
item.Browser = dr["Browser"].ToString().Trim();
item.UserAgent = dr["UserAgent"].ToString().Trim();
item.IP = dr["IP"].ToString().Trim();
item.IPNum = dr["IPNum"].ToString().Trim();
item.AddTime = (DateTime)dr["AddTime"];
return(item);
}
return(null);
}
public static IEnumerable <string> GetKeywords(long siteId, long menuId)
{
string sql = $"SELECT MenuID, Keywords FROM SEO Where Keywords <> '' AND MenuID IN (SELECT ID FROM Menus WHERE SiteID = { siteId })";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetDataTable(sql);
Dictionary <string, int> wordList = new Dictionary <string, int>();
foreach (DataRow dr in datas.Rows)
{
long mId = (long)dr["MenuID"];
string[] words = dr["Keywords"].ToString().Trim().Split(';');
foreach (string w in words)
{
if (!wordList.ContainsKey(w))
{
wordList[w] = 0;
}
wordList[w] += (mId == menuId ? 3 : 2);
}
}
return(wordList.OrderByDescending(w => w.Value).Select(w => w.Key));
}
/// <summary>
/// 返回指定 parentId 及上層選單的相關信息
/// </summary>
/// <param name="parentMenuIds">指定的 parentId</param>
/// <returns>返回一個字符串數組列表，每個 Item 是一個數組，[0] 表示 parentMenuId，[1] 表示 parentMenuName，[2] 表示 parentMenuId 再上一級選單的名稱</returns>
public static List <string[]> GetRootMenus(IEnumerable <long> parentMenuIds)
{
if (parentMenuIds?.Count() == 0)
{
return(null);
}
string sql = $"Select ID, Title, ParentID From Menus Where ID In ({ string.Join(", ", parentMenuIds.Distinct()) })";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetDataTable(sql);
List <string[]> rootMenus = new List <string[]>();
List <long> rootMenuIds = new List <long>();
foreach (DataRow dr in datas.Rows)
{
long id = (long)dr["ID"];
long parentId = (long)dr["ParentID"];
rootMenus.Add(new string[] { id.ToString(), dr["Title"].ToString().Trim(), parentId == 0 ? null : parentId.ToString() });
if (parentId != 0)
{
rootMenuIds.Add(parentId);
}
}
if (rootMenuIds.Count > 0)
{
sql = $"Select ID, Title From Menus Where ID In ({ string.Join(", ", rootMenuIds.Distinct()) })";
foreach (DataRow dr in db.GetDataTable(sql).Rows)
{
string id = dr["ID"].ToString().Trim();
string title = dr["Title"].ToString().Trim();
foreach (string[] item in rootMenus)
{
if (item[2] == id)
{
item[2] = title;
}
}
}
}
return(rootMenus);
}
/// <summary>
/// 判断日期是否为假日
/// </summary>
/// <param name="date"></param>
/// <returns></returns>
public bool IsHoliday(string date, int type)
{
string strSQL = "select 1 from HolidaySet where [Date]=@date And Type = @type;";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
SQLData.ParameterCollection para = new SQLData.ParameterCollection();
para.Add("@date", date);
para.Add("@type", type);
return(db.GetDataTable(strSQL, para).Rows.Count > 0);
}
public static bool SetSelectedItems(long MemberShipID, string Type, List <string> CategoryIDList)
{
string sel_sql = $"SELECT * FROM MemberShipSetting WHERE MemberShipID={MemberShipID.ToString()} AND Type='{ Type.Replace(",", "','") }' ";
string addIDList = "", delIDList = "";
List <string> existList = new List <string>();
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetDataTable(sel_sql);
if (datas != null && datas.Rows.Count > 0)
{
foreach (DataRow row in datas.Rows)
{
if (CategoryIDList.Contains(row["CategoryID"].ToString()))
{
existList.Add(row["CategoryID"].ToString());
continue;
}
else
{
delIDList += row["CategoryID"].ToString() + ",";
}
}
}
foreach (string categoryID in CategoryIDList)
{
if (!existList.Contains(categoryID))
{
addIDList += categoryID + ",";
}
}
addIDList = addIDList.Trim(',');
delIDList = delIDList.Trim(',');
string exeCommandStr = "";
if (!string.IsNullOrEmpty(delIDList))
{
exeCommandStr = $" DELETE MemberShipSetting WHERE MemberShipID={MemberShipID.ToString()} AND Type='{ Type.Replace("'", "") }' AND CategoryID IN ({delIDList});";
}
if (!string.IsNullOrEmpty(addIDList))
{
string[] addIDs = addIDList.Split(',');
foreach (string id in addIDs)
{
exeCommandStr += $"INSERT INTO MemberShipSetting (MemberShipID, Type, CategoryID, CreateTime, ModifyTime) VALUES ({MemberShipID.ToString()}, '{ Type.Replace("'", "") }', {id}, GETDATE(), GETDATE()); ";
}
}
if (!string.IsNullOrEmpty(exeCommandStr))
{
int exeCount = db.ExecuteNonQuery(exeCommandStr);
}
return(true);
}
public static List <PageCommentsModels> GetChildItems(long SiteID, long ParentID)
{
List <PageCommentsModels> itemList = new List <PageCommentsModels>();
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
string sql =
$"Select * From PageComments Where ParentID={ParentID} AND ShowStatus=1 Order By PostDate ASC ";
DataTable datas = db.GetDataTable(sql);
itemList = GetListItems(SiteID, datas);
return(itemList);
}
private static string GetSearchSql(FormItemSearch search)
{
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
string sql = "SELECT ID, CreateDate, CheckStatus, CheckDate, IsBack, SN, Remark, Email, Phone, Mobile, IDCard, CheckInDate, IsProcess, ProcessRemark, ProcessTime FROM FormItem I WHERE {0} ORDER BY {1}";
List <string> where = new List <string>();
where.Add("FormID = " + search.FormID);
where.Add("IsTemp = 0");
if (!string.IsNullOrWhiteSpace(search.Key))
{
string key = $"N'%{ search.Key.Replace("'", "''") }%'";
List <string> orSql = new List <string>();
orSql.Add($"Exists(Select 1 From FieldValue Where FormItemID = I.ID AND Value Like { key })");
string query = $"Select UserNo From UserFlag Where Flag Like { key } AND UserNo <> '' AND SiteID = (Select SiteID From Form Where ID = { search.FormID })";
IEnumerable <string> userNo = db.GetDataTable(query).AsEnumerable().Select(dr => $"N'{ dr["UserNo"].ToString().Trim() }'");
if (userNo?.Count() > 0)
{
orSql.Add(string.Format("Email In ({0}) OR Mobile In ({0}) OR Phone In ({0}) OR IDCard In ({0})", string.Join(", ", userNo)));
}
where.Add($"({ string.Join(" OR ", orSql) })");
}
if (search.CheckStatusList?.Length > 0)
{
where.Add($"CheckStatus IN ({ string.Join(", ", search.CheckStatusList.Select(s => (int)s)) })");
}
if (search.FillModes?.Count() == 1)
{
where.Add($"IsBack = { search.FillModes[0] }");
}
if (search.Start != null)
{
where.Add($"CreateDate >= '{ search.Start.ToString("yyyy/MM/dd HH:mm") }'");
}
if (search.End != null)
{
where.Add($"CreateDate <= '{ search.End.ToString("yyyy/MM/dd HH:mm") }'");
}
string[] allowOrders = { "CheckStatus", "CreateDate", "CreateDate Desc" };
string order = allowOrders.Contains(search.Order) ? search.Order : "CheckStatus";
return(string.Format(sql, string.Join(" AND ", where), order));
}
//public static MemberModels.LoginStatus SysLoginCheck(string LoginID, string PassWord)
//{
// //DENISE 待補
// string sql = "isSysOnly != false";
// MemberModels member = new MemberModels
// {
// Id = 510,
// isSysOnly = true,
// MemName = "Denise",
// Email = "*****@*****.**"
// };
// HttpContext.Current.Session[WebInfo.SysMemSkey] = member;
// return MemberModels.LoginStatus.Success;
//}
#region Login Check
// 20180516 neil 新增選擇攔位 IsChangedPassword, IsSupremeAuthority
public static string LoginCheck(string LoginID, string PassWord)
{
string sql = "select * from Member where LoginID='{0}'";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable dt = db.GetDataTable(string.Format(sql, LoginID, PassWord));
if (dt.Rows.Count > 0 && dt.Rows[0] != null)
{
string Pwd = (string)dt.Rows[0]["Password"];
string Status = dt.Rows[0]["MStatus"].ToString();
if (Status == "0")
{
if (Pwd == PassWord)
{
MemberModels member = new MemberModels
{
Id = (long)dt.Rows[0]["id"],
isSysOnly = (bool)dt.Rows[0]["isSysOnly"],
LoginID = dt.Rows[0]["LoginID"].ToString(),
Name = dt.Rows[0]["Name"].ToString(),
Img = dt.Rows[0]["Img"].ToString(),
GroupId = (long)dt.Rows[0]["GroupId"],
Email = dt.Rows[0]["Email"].ToString(),
IsChangedPassword = dt.Rows[0]["IsChangedPassword"] == null ? false : (bool)dt.Rows[0]["IsChangedPassword"],
IsSupremeAuthority = dt.Rows[0]["IsSupremeAuthority"] == null ? false : (bool)dt.Rows[0]["IsSupremeAuthority"],
};
String key = System.Guid.NewGuid().ToString();
HttpContext.Current.Session[key] = member;
HttpContext.Current.Session[WebInfo.SysMemSkey] = member;
return(key);
}
else
{
return(null);
}
}
else
{
string isSuspension = "isSuspension";//20190917 Joe 停權判斷
return(isSuspension);
}
}
else
{
return(null);
}
}
private void TwHandler(SQLData.Database db, StringBuilder sql)
{
sql.Append(GetInsertSql(null, 1, "台灣", 1));
int twId = id;
string query = "Select Title, ZipCode From TwZipCode Where ParentId = 0";
DataTable datas = db.GetDataTable(query);
int citySort = 0;
foreach (DataRow dr in datas.Rows)
{
sql.Append(GetInsertSql(twId, 3, dr["Title"].ToString().Trim(), ++citySort));
int cityId = id;
query = "Select Title, ZipCode From TwZipCode Where ParentID = " + dr["ZipCode"].ToString();
DataTable districts = db.GetDataTable(query);
int countySort = 0;
foreach (DataRow districtDr in districts.Rows)
{
sql.Append(GetInsertSql(cityId, 4, districtDr["Title"].ToString().Trim(), ++countySort));
}
}
}
public static List <MemberShipRegSocialSetModels> GetSocialItems(long SiteID)
{
List <MemberShipRegSocialSetModels> items = new List <MemberShipRegSocialSetModels>();
string sql = $"SELECT * FROM MemberShipRegSocialSet WHERE SiteID={SiteID} And BackendIsOpen = 1 ORDER BY Sort ";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetDataTable(sql);
if (datas == null || datas.Rows.Count <= 0)
{
Dictionary <WorkV3.Models.MemberType, string> DefaultSocialList = new Dictionary <WorkV3.Models.MemberType, string>();
DefaultSocialList.Add(WorkV3.Models.MemberType.FB, "Facebook");
//DefaultSocialList.Add(WorkV3.Models.MemberType.Twitter, "Twitter"); Joe 20190930 尚無功能
DefaultSocialList.Add(WorkV3.Models.MemberType.Google, "Google");
//DefaultSocialList.Add(WorkV3.Models.MemberType.Yahoo, "Yahoo");
//DefaultSocialList.Add(WorkV3.Models.MemberType.Weibo, "微博");
//DefaultSocialList.Add(WorkV3.Models.MemberType.QQ, "QQ");
//DefaultSocialList.Add(WorkV3.Models.MemberType.Baidu, "百度");
int itemSort = 0;
foreach (WorkV3.Models.MemberType defaultSocialKey in DefaultSocialList.Keys)
{
itemSort++;
MemberShipRegSocialSetModels m = GetSocialItem(SiteID, itemSort, defaultSocialKey, DefaultSocialList[defaultSocialKey]);
items.Add(m);
}
return(items);
}
else
{
foreach (DataRow dr in datas.Rows)
{
MemberShipRegSocialSetModels m = new MemberShipRegSocialSetModels();
m.SiteID = (long)dr["SiteID"];
m.SocialType = (WorkV3.Models.MemberType)((int)dr["SocialType"]);
m.SocialTitle = dr["SocialTitle"].ToString().Trim();
m.IsOpen = Convert.ToBoolean(dr["IsOpen"].ToString());
m.Sort = Convert.ToInt32(dr["Sort"].ToString());
m.SecretKey = dr["SecretKey"].ToString().Trim();
m.AppID = dr["AppID"].ToString().Trim();
m.Scope = dr["Scope"].ToString().Trim();
m.Creator = (long)dr["Creator"];
m.CreateTime = (DateTime)dr["CreateTime"];
m.Modifier = (long)dr["Modifier"];
m.ModifyTime = (DateTime)dr["ModifyTime"];
items.Add(m);
}
}
return(items);
}