public JsonResult UploadPersonPhotoFile(HttpPostedFileBase File)
{
if (File != null && File.ContentLength > 0)
{
string Path = string.Format("{0}/{1}", GetItem.UpdPath(), "Manager");
if (!System.IO.Directory.Exists(Path))
{
System.IO.Directory.CreateDirectory(Path);
}
string saveName = WorkV3.Golbal.UpdFileInfo.SaveFiles(File, Path);
MemberModels mem = MemberDAO.SysCurrent;
string sql = "UPDATE Member SET Img=@Img WHERE LoginID=@ID";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
SQLData.ParameterCollection paraList = new SQLData.ParameterCollection();
paraList.Add("@ID", mem.LoginID);
paraList.Add("@Img", saveName);
int exeCount = db.ExecuteNonQuery(sql, paraList);
if (exeCount > 0)
{
return(Json("success"));
}
}
return(Json("fail"));
}
public static void SetItem(FieldValue item)
{
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
SQLData.TableObject tableObj = db.GetTableObject("FieldValue");
tableObj.GetDataFromObject(item);
string sql = $"Select 1 From FieldValue Where FormItemID = { item.FormItemID } AND FieldID = { item.FieldID }";
bool isNew = db.GetFirstValue(sql) == null;
if (isNew)
{
tableObj.Insert();
}
else
{
tableObj.Remove("FormItemID");
tableObj.Remove("FieldID");
SQLData.ParameterCollection paras = new SQLData.ParameterCollection();
paras.Add("@FormItemID", item.FormItemID);
paras.Add("@FieldID", item.FieldID);
tableObj.Update(paras);
}
}
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 void SetItem(ResourceImagesModels item)
{
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
SQLData.TableObject tableObj = db.GetTableObject("ResourceImages");
tableObj.GetDataFromObject(item);
string sql = "Select 1 From ResourceImages Where ID = " + item.ID;
bool isNew = db.GetFirstValue(sql) == null;
if (isNew)
{
tableObj.Insert();
}
else
{
string[] removeFields = { "ID", "SiteID", "SourceNo", "SourceType", "Ver", "AreaID", "Creator", "CreateTime" };
foreach (string f in removeFields)
{
tableObj.Remove(f);
}
SQLData.ParameterCollection keys = new SQLData.ParameterCollection();
keys.Add("@ID", item.ID);
keys.Add("@SiteID", item.SiteID);
keys.Add("@SourceNo", item.SourceNo);
keys.Add("@SourceType", item.SourceType);
keys.Add("@Ver", item.Ver);
keys.Add("@AreaID", item.AreaID);
tableObj.Update(keys);
}
}
/// <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 void SetCardInfo(CardsModels card)
{
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
SQLData.TableObject tableObj = db.GetTableObject("Cards");
tableObj.GetDataFromObject(card);
string sql = $"Select 1 From Cards Where No = { card.No } AND Lang = '{ card.Lang.Replace("'", "''") }' AND Ver = { card.Ver }";
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
{
string[] removeFields = { "No", "Lang", "Ver", "Creator", "CreateTime" };
foreach (string field in removeFields)
{
tableObj.Remove(field);
}
tableObj["Modifier"] = WorkV3.Areas.Backend.Models.DataAccess.MemberDAO.SysCurrent.Id;
tableObj["ModifyTime"] = DateTime.Now;
SQLData.ParameterCollection keys = new SQLData.ParameterCollection();
keys.Add("@No", card.No);
keys.Add("@Lang", card.Lang);
keys.Add("@Ver", card.Ver);
tableObj.Update(keys);
}
}
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 void Save_Single(MenusModels data)
{
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
SQLData.TableObject TableObj = db.GetTableObject("Menus");
TableObj.Add("Title", data.Title);
//TableObj.Add("ParentID", data.ParentID);
TableObj.Add("ShowStatus", data.ShowStatus);
//TableObj.Add("Sort", data.Sort);
TableObj.Add("Modifier", MemberDAO.SysCurrent.Id);
TableObj.Add("ModifyTime", DateTime.Now.ToString(WebInfo.DateTimeFmt));
SQLData.ParameterCollection para = new SQLData.ParameterCollection();
para.Add("@ID", data.ID);
TableObj.Update(para);
PagesModels mPage = PagesDAO.GetPageInfo(data.SiteID, data.ID);
if (mPage != null)
{
mPage.Title = data.Title;
PagesDAO.SetPageInfo(mPage);
}
}
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);
}
/// <summary>
/// 新增檔案選單
/// </summary>
/// <param name="data"></param>
public static void Save_Menu(ResourceFilesModels data)
{
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
string Sql =
"IF EXISTS (SELECT 1 FROM ResourceFiles WHERE ID=@ID and SiteID=@SiteID and SourceNo=@SourceNo and SourceType=@SourceType and Ver=@Ver and AreaID=@AreaID)" +
" UPDATE ResourceFiles SET FileType=@FileType, FileInfo=@FileInfo, FileSize=@FileSize, FileMimeType=@FileMimeType, Descriptions=@Descriptions, Detail=@Detail, ClickType=@ClickType, Modifier=@Modifier, ModifyTime=GetDate() WHERE ID=@ID and SiteID=@SiteID and SourceNo=@SourceNo and SourceType=@SourceType and Ver=@Ver and AreaID=@AreaID" +
" ELSE" +
" INSERT INTO ResourceFiles(ID,SiteID,SourceNo, SourceType,Ver,AreaID,FileType,FileInfo,FileSize,FileMimeType,Descriptions,Detail,ClickType,Creator,CreateTime)VALUES(@ID,@SiteID,@SourceNo,@SourceType,@Ver,@AreaID,@FileType,@FileInfo,@FileSize,@FileMimeType,@Descriptions,@Detail,@ClickType,@Creator,GetDate())";
SQLData.ParameterCollection para = new SQLData.ParameterCollection();
para.Add("@ID", data.Id);
para.Add("@SiteID", data.SiteID);
para.Add("@SourceNo", data.SourceNo);
para.Add("@SourceType", data.SourceType);
para.Add("@Ver", data.Ver);
para.Add("@AreaID", data.AreaID);
para.Add("@FileType", data.FileType);
para.Add("@FileInfo", data.FileInfo);
para.Add("@FileSize", data.FileSize);
para.Add("@FileMimeType", data.FileMimeType);
para.Add("@Descriptions", data.Descriptions);
para.Add("@Detail", data.Detail);
para.Add("@ClickType", data.ClickType);
para.Add("@Creator", MemberDAO.SysCurrent.Id);
para.Add("@Modifier", MemberDAO.SysCurrent.Id);
db.ExecuteNonQuery(Sql, para);
}
public long MenuAddPage(long SiteID, long MenuID, long MenuSN, string Title, bool appendIdToName = false)
{
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
long NewPageNO = GetItem.NewSN();
string sql =
"INSERT Pages(No,Lang,Ver,SiteID,MenuID,SN,Title,ShowStatus,PublishStatus,Creator,CreateTime) VALUES(@No,@Lang,@Ver,@SiteID,@MenuID,@SN,@Title,@ShowStatus,@PublishStatus,@Creator,Getdate());\n";
SQLData.ParameterCollection para = new SQLData.ParameterCollection();
para.Add("@No", NewPageNO);
para.Add("@Lang", "zh-tw");
para.Add("@Ver", 1);
para.Add("@MenuID", MenuID);
para.Add("@SiteID", SiteID);
para.Add("@SN", MenuSN);
para.Add("@PublishStatus", 1);
para.Add("@ShowStatus", 1);
db.ExecuteNonQuery(sql, para);
if (appendIdToName)
{
sql = " Update Pages Set SN = SN + '_'+ NO Where No = @NO ;\n";
}
SQLData.ParameterCollection para2 = new SQLData.ParameterCollection();
para2.Add("@No", NewPageNO);
db.ExecuteNonQuery(sql, para2);
return(NewPageNO);
}
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 void SaveInfo(MenusModels data)
{
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
SQLData.TableObject TableObj = db.GetTableObject("Menus");
//TableObj.GetDataFromObject(data);
TableObj.Add("Title", data.Title);
TableObj.Add("ShowStatus", data.ShowStatus);
SQLData.ParameterCollection para = new SQLData.ParameterCollection();
if (data.ID == 0)
{
TableObj.Add("ID", GetItem.NewSN());
TableObj.Add("SN", data.SN);
TableObj.Add("SiteID", data.SiteID);
TableObj.Add("AreaID", data.AreaID);
TableObj.Add("ParentID", data.ParentID);
TableObj.Add("DataType", data.DataType);
TableObj.Add("Sort", data.Sort);
TableObj.Add("Creator", MemberDAO.SysCurrent.Id);
TableObj.Add("CreateTime", DateTime.Now.ToString(WebInfo.DateTimeFmt));
TableObj.Insert();
}
else
{
TableObj.Add("Modifier", MemberDAO.SysCurrent.Id);
TableObj.Add("ModifyTime", DateTime.Now.ToString(WebInfo.DateTimeFmt));
para.Add("@ID", data.ID);
TableObj.Update(para);
}
}
public static void DelMemberShipRegManagers(long SiteID)
{
SQLData.ParameterCollection paraList = new SQLData.ParameterCollection();
paraList.Add("@SiteID", SiteID);
string sql = $"DELETE MemberShipRegEmailManagers WHERE SiteID=@SiteID AND IsManager=1 ";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
db.ExecuteNonQuery(sql, paraList);
}
/// <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 void SetItemStatus(long ID, bool ShowStatus)
{
AdsStatisticsLabelsModel item = new AdsStatisticsLabelsModel();
SQLData.ParameterCollection paraList = new SQLData.ParameterCollection();
paraList.Add("@ID", ID);
paraList.Add("@ShowStatus", ShowStatus);
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
string updateSQL = " UPDATE AdsStatisticsLabels SET ShowStatus=@ShowStatus WHERE ID=@ID ";
db.ExecuteNonQuery(updateSQL, paraList);
}
public static void SetStatus(long SiteID, bool Robot)
{
SiteSeoSettingModels item = new SiteSeoSettingModels();
SQLData.ParameterCollection paraList = new SQLData.ParameterCollection();
paraList.Add("@SiteID", SiteID);
paraList.Add("@Robot", Robot);
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
string updateSQL = " UPDATE SiteSeoSetting SET Robot=@Robot WHERE SiteID=@SiteID ";
db.ExecuteNonQuery(updateSQL, paraList);
}
public static void SetRelationStatus(long ID, bool IsOpen)
{
SocialRelationModels item = new SocialRelationModels();
SQLData.ParameterCollection paraList = new SQLData.ParameterCollection();
paraList.Add("@ID", ID);
paraList.Add("@IsOpen", IsOpen);
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
string updateSQL = " UPDATE SocialRelations SET IsOpen=@IsOpen WHERE ID=@ID ";
db.ExecuteNonQuery(updateSQL, paraList);
}
public static bool UpdateColumnItemOptionSet(long SiteID, string columnName, string otherOption)
{
SQLData.ParameterCollection paraList = new SQLData.ParameterCollection();
paraList.Add("@SiteID", SiteID);
paraList.Add("@ColumnName", columnName);
paraList.Add("@OtherOption", otherOption);
string sql = $"UPDATE MemberShipRegColumnSet SET OtherOption=@OtherOption WHERE SiteID=@SiteID AND ColumnName=@ColumnName ";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
db.ExecuteNonQuery(sql, paraList);
return(true);
}
public static bool UpdateSocialItemSet(long SiteID, WorkV3.Models.MemberType socialType, string SecretKey, string AppID, string Scope, bool isOpen)
{
string sql = $"UPDATE MemberShipRegSocialSet SET IsOpen=@IsOpen, SecretKey=@SecretKey, AppID=@AppID, Scope=@Scope WHERE SiteID=@SiteID AND SocialType=@SocialType";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
SQLData.ParameterCollection paraList = new SQLData.ParameterCollection();
paraList.Add("@SiteID", SiteID);
paraList.Add("@SocialType", (int)socialType);
paraList.Add("@IsOpen", isOpen);
paraList.Add("@SecretKey", SecretKey);
paraList.Add("@AppID", AppID);
paraList.Add("@Scope", Scope);
db.ExecuteNonQuery(sql, paraList);
return(true);
}
public static void Save_Folder(MenusModels data)
{
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
SQLData.TableObject TableObj = db.GetTableObject("Menus");
TableObj.Add("Title", data.Title);
TableObj.Add("ShowStatus", data.ShowStatus);
TableObj.Add("DataTypeValue", data.DataTypeValue);
TableObj.Add("Modifier", MemberDAO.SysCurrent.Id);
TableObj.Add("ModifyTime", DateTime.Now.ToString(WebInfo.DateTimeFmt));
SQLData.ParameterCollection para = new SQLData.ParameterCollection();
para.Add("@ID", data.ID);
TableObj.Update(para);
}
public static void DelAll(long SiteID, long SourceNo, long SourceType, int Ver, int AreaID)
{
//
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
string sql = "Delete ResourceFiles Where SiteID=@SiteID and SourceNo=@SourceNo and SourceType=@SourceType and Ver=@Ver and AreaID=@AreaID";
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);
db.ExecuteNonQuery(sql, para);
}
public static void UpdatePageInfo(PagesModels page)
{
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
SQLData.TableObject tableObj = db.GetTableObject("Pages");
tableObj.GetDataFromObject(page);
tableObj.Remove("No");
tableObj["Modifier"] = WorkV3.Areas.Backend.Models.DataAccess.MemberDAO.SysCurrent.Id;
tableObj["ModifyTime"] = DateTime.Now;
SQLData.ParameterCollection keys = new SQLData.ParameterCollection();
keys.Add("@No", page.No);
tableObj.Update(keys);
}
public static void SetItem(ResourceVideosModels item)
{
item.Spec = item.Spec ?? string.Empty;
item.Duration = item.Duration ?? string.Empty;
item.Screenshot = item.Screenshot ?? string.Empty;
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
SQLData.TableObject tableObj = db.GetTableObject("ResourceVideos");
tableObj.GetDataFromObject(item);
string sql = "Select 1 From ResourceVideos Where ID = " + item.ID;
bool isNew = db.GetFirstValue(sql) == null;
if (isNew)
{
tableObj["Creator"] = MemberDAO.SysCurrent.Id;
tableObj["CreateTime"] = DateTime.Now;
tableObj.Insert();
}
else
{
string[] removeFields = { "ID", "SiteID", "SourceNo", "SourceType", "Ver", "AreaID", "Code", "Creator", "CreateTime" };
foreach (string f in removeFields)
{
tableObj.Remove(f);
}
tableObj["Modifier"] = MemberDAO.SysCurrent.Id;
tableObj["ModifyTime"] = DateTime.Now;
SQLData.ParameterCollection keys = new SQLData.ParameterCollection();
keys.Add("@ID", item.ID);
keys.Add("@SiteID", item.SiteID);
keys.Add("@SourceNo", item.SourceNo);
keys.Add("@SourceType", item.SourceType);
keys.Add("@Ver", item.Ver);
keys.Add("@AreaID", item.AreaID);
tableObj.Update(keys);
}
}
public static void Insert_Single(MenusModels data)
{
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
SQLData.TableObject TableObj = db.GetTableObject("Menus");
SQLData.ParameterCollection para = new SQLData.ParameterCollection();
TableObj.Add("Title", data.Title);
TableObj.Add("ParentID", 0);
TableObj.Add("ShowStatus", data.ShowStatus);
TableObj.Add("Sort", 0);
TableObj.Add("ID", data.ID);
TableObj.Add("SN", data.SN);
TableObj.Add("SiteID", data.SiteID);
TableObj.Add("AreaID", data.AreaID);
TableObj.Add("DataType", data.DataType);
TableObj.Add("Creator", MemberDAO.SysCurrent.Id);
TableObj.Add("CreateTime", DateTime.Now.ToString(WebInfo.DateTimeFmt));
TableObj.Add("DisableDelete", data.DisableDelete);
TableObj.Insert();
}
/// <summary>
/// 取得統計資料
/// </summary>
/// <returns></returns>
public static DataTable GetMemberLoginStatisticsTable(DateTime startDate, DateTime endDate)
{
SQLData.ParameterCollection paraList = new SQLData.ParameterCollection();
paraList.Add("@StartDate", startDate);
paraList.Add("@EndDate", endDate);
string Sql = @" SELECT LogDate, SUM(MemberCount) AS MemberCount, SUM(LoginCount) AS LoginCount FROM
(
SELECT Convert(nvarchar(10), CreateTime, 111) AS LogDate, SUM(1) AS MemberCount,0 AS LoginCount
FROM MemberShip WHERE Convert(nvarchar(10), CreateTime, 111)>=@StartDate AND Convert(nvarchar(10), CreateTime, 111)<=@EndDate
GROUP BY Convert(nvarchar(10), CreateTime, 111)
UNION ALL
SELECT Convert(nvarchar(10), AddTime, 111) AS LogDate, 0 AS MemberCount, Count(1) AS LoginCount
FROM [MemberShipLoginLogs] WHERE Convert(nvarchar(10), AddTime, 111)>=@StartDate AND Convert(nvarchar(10), AddTime, 111)<=@EndDate
GROUP BY Convert(nvarchar(10), AddTime, 111)
) AS LogData
GROUP BY LogDate
ORDER BY LogDate ASC";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable dt = db.GetDataTable(Sql, paraList);
return(dt);
}
public static int DeleteGoodLogs(long commentID, long logID)
{
int goodCountResult = 0;
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
string AddCol = "", AddColPara = "";
SQLData.ParameterCollection paras = new SQLData.ParameterCollection();
paras.Add("@ID", commentID);
paras.Add("@LogID", logID);
string sql = string.Format(@" UPDATE PageComments SET GoodCount=GoodCount-1 WHERE ID=@ID;
DELETE PageCommentLogs WHERE ID=@LogID;
SELECT GoodCount FROM PageComments WHERE ID=@ID", AddCol, AddColPara);
DataTable logTable = db.GetDataTable(sql, paras);
if (logTable != null && logTable.Rows.Count > 0)
{
if (!string.IsNullOrEmpty(logTable.Rows[0][0].ToString()))
{
goodCountResult = int.Parse(logTable.Rows[0][0].ToString());
}
}
return(goodCountResult);
}
public static int AddGoodLogs(long commentID, ViewModels.CommentLogType LogType, string Browser, string UserAgent, long?MemberID)
{
int goodCountResult = 0;
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
string AddCol = "", AddColPara = "";
SQLData.ParameterCollection paras = new SQLData.ParameterCollection();
paras.Add("@ID", commentID);
paras.Add("@NewLogID", WorkLib.GetItem.NewSN());
paras.Add("@LogType", (int)LogType);
paras.Add("@Browser", Browser);
paras.Add("@UserAgent", UserAgent);
paras.Add("@IP", WorkLib.GetItem.IPAddr());
paras.Add("@IPNum", WorkLib.GetItem.GetIPNum().ToString());
if (MemberID.HasValue)
{
AddCol = ", MemberShipID";
AddColPara = ", @MemberShipID";
paras.Add("@MemberShipID", MemberID.Value);
}
string sql = string.Format(@" UPDATE PageComments SET GoodCount=GoodCount+1 WHERE ID=@ID;
INSERT PageCommentLogs (ID, CommentID{0}, LogType, Browser, UserAgent, IP, IPNum, AddTime)
VALUES (@NewLogID, @ID{1}, @LogType, @Browser, @UserAgent, @IP, @IPNum, GETDATE());
SELECT GoodCount FROM PageComments WHERE ID=@ID", AddCol, AddColPara);
DataTable logTable = db.GetDataTable(sql, paras);
if (logTable != null && logTable.Rows.Count > 0)
{
if (!string.IsNullOrEmpty(logTable.Rows[0][0].ToString()))
{
goodCountResult = int.Parse(logTable.Rows[0][0].ToString());
}
}
return(goodCountResult);
}
public static void SetZoneInfo(ZonesModels zone)
{
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
SQLData.TableObject tableObj = db.GetTableObject("Zones");
tableObj.GetDataFromObject(zone);
string sql = $"Select 1 From Zones Where No = { zone.No } AND Ver = { zone.Ver } AND SiteID = { zone.SiteID } AND PageNo = { zone.PageNo }";
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
{
string[] removeFields = { "No", "Ver", "SiteID", "PageNo", "Creator", "CreateTime" };
foreach (string field in removeFields)
{
tableObj.Remove(field);
}
tableObj["Modifier"] = WorkV3.Areas.Backend.Models.DataAccess.MemberDAO.SysCurrent.Id;
tableObj["ModifyTime"] = DateTime.Now;
SQLData.ParameterCollection keys = new SQLData.ParameterCollection();
keys.Add("@No", zone.No);
keys.Add("@Ver", zone.Ver);
keys.Add("@SiteID", zone.SiteID);
keys.Add("@PageNo", zone.PageNo);
tableObj.Update(keys);
}
}
public int GetKeywordQueriedPagedItemCount(int index, JObject search)
{
using (var cn = new SqlConnection(WebInfo.Conn))
{
SQLData.ParameterCollection param = new SQLData.ParameterCollection();
List <string> where = new List <string>();
if (search["search"] != null && search["search"].ToString() != "")
{
where.Add("([Text] like '%'+@search+'%')");
param.Add("@search", search["search"].ToString());
}
if (search["leastSearch"] != null && search["leastSearch"].ToString() != "")
{
where.Add("[Count] >= @LeastCount");
param.Add("@LeastCount", search["leastSearch"].ToString());
}
if (search["maxSearch"] != null && search["maxSearch"].ToString() != "")
{
where.Add("[Count] <= @MaxCount");
param.Add("@MaxCount", search["maxSearch"].ToString());
}
string strSource = "";
if ((search["isSet"] != null && search["isSet"].ToString() != "false") ^ (search["isNotSet"] != null && search["isNotSet"].ToString() != "false"))
{
if (search["isSet"] != null && search["isSet"].ToString() != "false")
{
strSource = @" (SELECT * FROM KeywordQueried WHERE ID IN ( SELECT KeywordQueriedID FROM Keywords)) ";
}
else
{
strSource = @" (SELECT * FROM KeywordQueried WHERE ID NOT IN ( SELECT KeywordQueriedID FROM Keywords)) ";
}
}
else
{
strSource = "[KeywordQueried]";
}
StringBuilder sql = new StringBuilder();
if ((search["searchStart"] != null && search["searchStart"].ToString() != "") || (search["searchEnd"] != null && search["searchEnd"].ToString() != ""))
{
List <string> joinWhere = new List <string>();
if (search["searchStart"] != null && search["searchStart"].ToString() != "")
{
joinWhere.Add("[QueryTime] >= @searchStart");
param.Add("@searchStart", search["searchStart"].ToString());
}
if (search["searchEnd"] != null && search["searchEnd"].ToString() != "")
{
joinWhere.Add("[QueryTime] <= @searchEnd");
param.Add("@searchEnd", search["searchEnd"].ToString());
}
sql.Append($@"SELECT Count(*)
FROM (SELECT *
FROM {strSource}
WHERE ID IN (SELECT ID FROM KeywordQueriedLogs WHERE { string.Join(" AND ", joinWhere) })) a
LEFT JOIN Keywords b on a.id = b.KeywordQueriedID ");
sql.Append($"WHERE { string.Join(" AND ", where) } ");
}
else
{
sql.Append($"SELECT Count(*) FROM {strSource} a left join Keywords b on a.id = b.KeywordQueriedID ");
if (where.Count > 0)
{
sql.Append($"WHERE { string.Join(" AND ", where) } ");
}
}
return((int)db.GetFirstValue(sql.ToString(), param));
}
}
