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 void SvaeFormSetting(long FormID, long CardNo)
{
string sql = $"Delete FormSet Where CardNo = {CardNo}";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
db.ExecuteNonQuery(sql);
sql = $"Insert FormSet(CardNo, FormID) Values({CardNo}, {FormID})";
db.ExecuteNonQuery(sql);
}
public static void SaveTemplate(long id, string name)
{
if (string.IsNullOrWhiteSpace(name))
{
return;
}
FormModel item = GetItem(id);
item.ID = WorkLib.GetItem.NewSN();
item.SourceID = null;
SetItem(item);
string sql = $"Update Form Set TemplateName = N'{ name.Replace("'", "''") }' Where ID = { item.ID }";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
db.ExecuteNonQuery(sql);
IEnumerable <FormAdmin> admins = GetItemAdmins(id);
foreach (FormAdmin admin in admins)
{
admin.ID = 0;
}
SetItemAdmins(item.ID, admins);
IEnumerable <FieldModel> fields = FieldDAO.GetItems(id);
foreach (FieldModel field in fields)
{
field.ID = WorkLib.GetItem.NewSN();
field.ParentID = item.ID;
FieldDAO.SetItem(field);
}
}
public static bool DeleteItem(long ID)
{
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
string sql = $"DELETE PageComments Where ID = {ID}";
return(db.ExecuteNonQuery(sql) > 0);
}
/// <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 static void WriteReadLog(long mailId, long formItemId)
{
string sql = $"Update FormMailLog Set ReadDate = GetDate() Where MailID = { mailId } AND FormItemID = { formItemId } AND ReadDate Is Null";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
db.ExecuteNonQuery(sql);
}
public static void WriteLog(long mailId, long memberId)
{
string sql = $"Insert MemberShipSendMailLog(MailID, MemberShipID) Values({ mailId }, { memberId })";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
db.ExecuteNonQuery(sql);
}
public static void SetItem(long siteId, string flag, params string[] userNo)
{
if (string.IsNullOrWhiteSpace(flag) || userNo == null || userNo.Length == 0)
{
return;
}
System.Text.StringBuilder sql = new System.Text.StringBuilder();
string fmt =
$"IF Not Exists(Select 1 From UserFlag Where SiteID = { siteId } AND UserNo = N'{{0}}' AND Flag = N'{ flag.Replace("'", "''") }')\n" +
$" Insert UserFlag(SiteID, UserNo, Flag) Values({ siteId }, N'{{0}}', N'{ flag.Replace("'", "''") }')\n";
foreach (string no in userNo)
{
if (!string.IsNullOrWhiteSpace(no))
{
sql.AppendFormat(fmt, no.Replace("'", "''"));
}
}
if (sql.Length == 0)
{
return;
}
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
db.ExecuteNonQuery(sql.ToString());
}
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 WriteReadLog(long mailId, long memberId)
{
string sql = $"Update MemberShipSendMailLog Set ReadDate = GetDate() Where MailID = { mailId } AND MemberShipID = { memberId } AND ReadDate Is Null";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
db.ExecuteNonQuery(sql);
}
public static void Click(long id)
{
string sql = "Update MainVision Set Clicks = Clicks + 1 Where ID = " + id;
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
db.ExecuteNonQuery(sql);
}
public static void Complete(long id, FormCheckStatus status)
{
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
string sql = $"Update FormItem Set IsTemp = 0, CheckStatus = { (int)status } Where ID = { id }";
db.ExecuteNonQuery(sql);
}
public static void ChangeStatus(long id)
{
string sql = "Update Categories Set ShowStatus = CASE WHEN ShowStatus=0 THEN 1 ELSE 0 END Where ID = " + id;
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
db.ExecuteNonQuery(sql);
}
public static void WriteLog(long mailId, long formItemId)
{
string sql = $"Insert FormMailLog(MailID, FormItemID) Values({ mailId }, { formItemId })";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
db.ExecuteNonQuery(sql);
}
public static bool UpdateSocialItemSort(long SiteID, WorkV3.Models.MemberType socialType, int sort)
{
string sql = $"UPDATE MemberShipRegSocialSet SET Sort={sort} WHERE SiteID={SiteID} AND SocialType={(int)socialType};";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
db.ExecuteNonQuery(sql);
return(true);
}
public static bool UpdateColumnItemSort(long SiteID, string columnName, int sort)
{
string sql = $"UPDATE MemberShipRegColumnSet SET Sort={sort} WHERE SiteID={SiteID} AND ColumnName='{columnName.Replace("'", "")}';";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
db.ExecuteNonQuery(sql);
return(true);
}
public static bool DeleteManager(long ID)
{
string sql = $"DELETE MemberShipRegEmailManagers WHERE ID={ID} ";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
db.ExecuteNonQuery(sql);
return(true);
}
public static bool UpdatePageSetSort(long SiteID, string PageSN, int sort)
{
string sql = $"UPDATE MemberShipPageSet SET Sort={sort} WHERE SiteID={SiteID} AND PageSN='{PageSN.Replace("'", "")}';";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
db.ExecuteNonQuery(sql);
return(true);
}
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);
}
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 bool UpdateSocialItemSet(long SiteID, WorkV3.Models.MemberType socialType, bool isOpen)
{
string isOpenSet = "0";
isOpenSet = isOpen ? "1" : "0";
string sql = $"UPDATE MemberShipRegSocialSet SET IsOpen={isOpenSet} WHERE SiteID={SiteID} AND SocialType={(int)socialType}";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
db.ExecuteNonQuery(sql);
return(true);
}
public static bool UpdatePageSet(long SiteID, string PageSN, bool isOpen)
{
string isOpenSet = "0", isNeedValueSet = "0";
isOpenSet = isOpen ? "1" : "0";
string sql = $"UPDATE MemberShipPageSet SET IsOpen={isOpenSet} WHERE SiteID={SiteID} AND PageSN='{PageSN.Replace("'", "")}'";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
db.ExecuteNonQuery(sql);
return(true);
}
public static void SetCheckStatus(IEnumerable <long> ids, FormCheckStatus status)
{
if (ids == null || ids.Count() == 0)
{
return;
}
string sql = $"Update FormItem Set CheckStatus = { (int)status }, CheckDate = GetDate() Where ID IN ({ string.Join(", ", ids) })";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
db.ExecuteNonQuery(sql);
}
public static void CheckIn(long formId, IEnumerable <long> ids)
{
if (ids == null || ids.Count() == 0)
{
return;
}
string sql = $"Update FormItem Set CheckInDate = GetDate() Where FormID = { formId } AND ID IN ({ string.Join(", ", ids) })";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
db.ExecuteNonQuery(sql);
}
public static void Process(long[] ids, bool isProcess, string remark)
{
if (ids == null || ids.Length == 0)
{
return;
}
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
string sql = $"Update FormItem Set IsProcess = { (isProcess ? 1 : 0) }, ProcessTime = GetDate(), ProcessRemark = N'{ (remark ?? string.Empty).Replace("'", "''") }' Where ID IN ({ string.Join(", ", ids) })";
db.ExecuteNonQuery(sql);
}
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 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 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 Delete(long id)
{
string sql =
$"Delete Form Where ID = { id }\n" +
$"Delete FormAdmin Where FormID = { id }\n" +
$"Delete FieldValue Where FormItemID IN (Select ID From FormItem Where FormID = { id })\n" +
$"Delete FormItem Where FormID = { id }\n" +
$"Delete Field Where ParentID = { id }\n";
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
db.ExecuteNonQuery(sql);
}
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);
}