public string UpdateUnivDepartment(string DeptNo, string DeptName, string DeptDescription)
{
//Return Msg
string resMsg = "修改成功！";
// admin check
if (getUserStatusNo() != "1")
{
resMsg = "修改失敗，權限錯誤！";
}
else
{
string sqlStr = "UPDATE [dbo].[Department] " +
"SET [DeptName] = N'" + DeptName + "', " +
"[DeptDescription] = N'" + DeptDescription + "', " +
"[ModifyTime] = " + DBC.ChangeTimeZone() + ", " +
"[ModifyUser] = " + SqlVal2(Request.Cookies["account"]) +
"WHERE [DeptNo] = '" + DeptNo + "'";
var check = _DB_Execute(sqlStr);
//修改是否成功
if (check != 1)
{
resMsg = "修改失敗，若持續發生此問題，請與我們聯繫。";
}
}
//Return Msg
return(resMsg);
}
/// <summary>
/// 更新
/// </summary>
/// <param name="Model"></param>
/// <returns></returns>
public bool UpdateNews(NewsModel Model)
{
string sqlStr = string.Format(
@"UPDATE [dbo].[News] " +
"SET [NewsTypeNo] = {0}, " +
"[NewsTitle] = {1}, " +
"[NewsContent] = {2}, " +
"[ModifyTime] = {3}, " +
"[ModifyUser] = {4} " +
"WHERE [NewsNo] = {5}",
SqlVal2(Model.NewsTypeNo),
SqlVal2(Model.NewsTitle),
SqlVal2(Model.NewsContent.Replace("\n", "<br>")),
DBC.ChangeTimeZone(),
SqlVal2(Request.Cookies["account"]),
SqlVal2(Model.NewsNo));
var check = _DB_Execute(sqlStr);
//修改是否成功
if (check == 1)
{
//成功
return(true);
}
else
{
//失敗
return(false);
}
}
/// <summary>
/// 修改科系
/// </summary>
/// <param name="DeptNo"></param>
/// <param name="DeptName"></param>
/// <param name="DeptDescription"></param>
/// <returns></returns>
public string UpdateUnivDepartment(string DeptNo, string DeptName, string DeptDescription)
{
// admin check
if (getUserStatusNo() != "1")
{
return(null);
}
string resMsg = "";
string sqlStr = "UPDATE [dbo].[Department] " +
"SET [DeptName] = N'" + DeptName + "', " +
"[DeptDescription] = N'" + DeptDescription + "', " +
"[ModifyTime] = " + DBC.ChangeTimeZone() + ", " +
"[ModifyUser] = " + SqlVal2(Request.Cookies["account"]) +
"WHERE [DeptNo] = '" + DeptNo + "'";
var check = _DB_Execute(sqlStr);
//修改是否成功
if (check == 1)
{
resMsg = "success";
}
else
{
resMsg = "fail";
}
return(resMsg);
}
public string NewFeedBack(string TypeNo, string Title, string Content)
{
string resMsg = "";
if (TypeNo == null)
{
resMsg = "回饋類型不可為空!!";
}
else if (Title == null || Title.Length > 50)
{
resMsg = "未輸入標題或長度超出限制!!";
}
// 長度限制
else if (Content != null && Content.Length > 200)
{
resMsg = "回饋內容超出長度限制!!";
}
else
{
//SQL Insert
var sqlStr = string.Format(
@"INSERT INTO [dbo].[FeedBack](" +
"[FeedbackTypeNo]," +
"[FeedbackUser]," +
"[FeedbackTitle]," +
"[FeedbackContent]," +
"[CreateTime]" +
")VALUES(" +
"{0}," +
"{1}," +
"{2}," +
"{3}," +
"{4}",
SqlVal2(TypeNo),
SqlVal2(Request.Cookies["account"]),
SqlVal2(Title),
SqlVal2(Content),
DBC.ChangeTimeZone() + ")"
);
var check = _DB_Execute(sqlStr);
//新增是否成功
if (check == 1)
{
resMsg = "success";
}
else
{
resMsg = "fail";
}
}
return(resMsg);
}
/// <summary>
/// 更新點擊次數
/// </summary>
/// <param name="NewsNo"></param>
/// <returns></returns>
private bool UpdateNewsHits(string NewsNo)
{
if (Request.Cookies["account"] == null)
{
return(true);
}
//NewsTable
string updateSqlStr = string.Format(
@"UPDATE [dbo].[News] " +
"SET [NewsHits] = [NewsHits] + 1 " +
"WHERE [NewsNo] = " + SqlVal2(NewsNo));
var check = _DB_Execute(updateSqlStr);
if (check != 1)
{
return(false);
}
//NewsHits
string sql = string.Format(
@"INSERT INTO [dbo].[NewsHits] (
[NewsNo],
[Account],
[CreateTime])
VALUES({0}, {1}, {2})",
SqlVal2(NewsNo),
SqlVal2(Request.Cookies["account"]),
DBC.ChangeTimeZone()
);
check = _DB_Execute(sql);
if (check != 1)
{
return(false);
}
return(true);
}
public bool UpdateGrad(string year, string countryDeptNo, string graduationNumber)
{
// admin check
if (getUserStatusNo() != "1")
{
return(false);
}
int gradNumInt;
if (!int.TryParse(graduationNumber, out gradNumInt) || gradNumInt < 0)
{
return(false);
}
var sqlStr = string.Format("UPDATE [dbo].[Graduation] " +
"SET [GraduationNumber] = {0} " +
",[ModifyTime] = {1} " +
",[CreateUser] = {2} " +
"WHERE " +
"CountryDeptNo={3} AND GraduationYear={4}",
SqlVal2(graduationNumber),
DBC.ChangeTimeZone(),
SqlVal2(Request.Cookies["account"]),
SqlVal2(countryDeptNo),
SqlVal2(year));
var check = _DB_Execute(sqlStr);
//是否成功
if (check == 1)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 新增國家科系
/// </summary>
/// <param name="CountryNo"></param>
/// <param name="DeptNo"></param>
/// <returns></returns>
private bool InsertCountryDept(string CountryNo, string DeptNo)
{
//檢查是否有重複的了
if (CheckInsertCountryDept(CountryNo, DeptNo))
{
return(false);
}
else
{
// 新增國家與科系的關聯
var sqlStr = string.Format("INSERT INTO [dbo].[CountryDepartment] (" +
"[CountryNo], " +
"[DeptNo], " +
"[CreateTime], " +
"[ModifyTime], " +
"[CreateUser] " +
") " +
"VALUES " +
"({0}, " +
" {1}, " +
" {2}, " +
" {3}, " +
" {4})",
CountryNo, DeptNo, DBC.ChangeTimeZone(), DBC.ChangeTimeZone(), SqlVal2(Request.Cookies["account"]));
var check = _DB_Execute(sqlStr);
//新增是否成功
if (check == 1)
{
return(true);
}
else
{
return(false);
}
}
}
public ActionResult NewNews(NewsModel Model)
{
string resMsg = "";
string checkMsg = "";
// 長度限制
if (string.IsNullOrEmpty(Model.NewsTitle) ||
string.IsNullOrEmpty(Model.NewsContent) ||
Model.NewsContent.Length > 200 ||
Model.NewsTitle.Length > 50)
{
resMsg = "標題或內容不符合長度限制!! 標題與內容不可為空，且標題要在50字內，內容不可超過200字";
checkMsg = "false";
}
else
{
checkMsg = "true";
//SQL Insert
var sqlStr = string.Format(
@"INSERT INTO [dbo].[News](" +
"[NewsTypeNo]," +
"[NewsTitle]," +
"[NewsContent]," +
"[NewsHits]," +
"[CreateTime]," +
"[ModifyTime]," +
"[NewsStart]," +
"[NewsEnd]," +
"[CreateUser]" +
")VALUES(" +
"{0}," +
"{1}," +
"{2}," +
"{3}," +
"{4}," +
"{5}," +
"{6}," +
"{7}," +
"{8})",
SqlVal2(Model.NewsTypeNo),
SqlVal2(Model.NewsTitle),
SqlVal2(Model.NewsContent.Replace("\n", "<br>")),
0,
DBC.ChangeTimeZone(),
DBC.ChangeTimeZone(),
SqlVal2(Model.NewsStart),
SqlVal2(Model.NewsEnd),
SqlVal2(Request.Cookies["account"])
);
var check = _DB_Execute(sqlStr);
//新增是否成功
if (check == 1)
{
resMsg = "新增成功";
//return View("NewNews", "Success!!");
}
else
{
resMsg = "Failed";
}
}
//return View("NewNews", "Fail :(");
ViewData["result"] = resMsg;
ViewData["NewsTypes"] = NewsTypes;
ViewData["checkMsg"] = checkMsg;
TempData["Message"] = resMsg;
if (checkMsg == "false" || resMsg == "Failed")
{
return(View(Model));
}
/*else
* {
* //return RedirectToAction("ShowNews");
* return View(Model);
* }
*/
return(View(Model));
}
/// <summary>
/// 撈DB最新消息
/// </summary>
/// <param name="NewsNo"></param>
/// <param name="GetCount"></param>
/// <returns></returns>
private DataTable GetNews(string NewsNo, string GetCount = "")
{
//若取得數量不為空
if (!string.IsNullOrEmpty(GetCount))
{
GetCount = string.Format("TOP({0})", GetCount);
}
//若取得NewsNo不為空
if (!string.IsNullOrEmpty(NewsNo))
{
var sqlStr = string.Format("" +
"SELECT NewsNo, [dbo].[News].NewsTypeNo, TypeName, NewsTitle, NewsContent, NewsHits, Convert(varchar(10), NewsStart,111) as NewsStart , Convert(varchar(10), NewsEnd,111) as NewsEnd " +
"FROM [dbo].[News] INNER JOIN [dbo].[NewsType] on [dbo].[News].NewsTypeNo = [dbo].[NewsType].NewsTypeNo " +
"where NewsNo = {0}", NewsNo
);
string resMsg = "";
var check = UpdateNewsHits(NewsNo);
//修改是否成功
if (check)
{
resMsg = "success";
}
else
{
resMsg = "fail";
}
ViewData["result"] = resMsg;
//Return
return(_DB_GetData(sqlStr));
}
else
{
//SQL 順便做有效時間塞選
var sqlStr = string.Format("SELECT {0} " +
"NewsNo," +
"NType.NewsTypeNo," +
"TypeName," +
"NewsTitle," +
"NewsContent," +
"NewsHits," +
"Convert(varchar(10), NewsStart, 111) as NewsStart," +
"Convert(varchar(10), NewsEnd, 111) as NewsEnd " +
"FROM [dbo].[News] as News INNER JOIN[dbo].[NewsType] as NType on NType.NewsTypeNo = News.NewsTypeNo " +
"where NewsEnd >= (SELECT convert(varchar, {1}, 111))" +
"ORDER BY NewsStart,News.CreateTime DESC", GetCount, DBC.ChangeTimeZone()
);
//Return
return(_DB_GetData(sqlStr));
}
}
public string InsertUnivDepartment(string DeptName, string DeptDescription)
{
// admin check
if (getUserStatusNo() != "1")
{
return("權限錯誤！");
}
//Return Msg
string resMsg = "新增成功！";
if (DeptName == null || DeptName.Length > 50)
{
resMsg = "未輸入科系或長度超過限制!!";
}
else if (DeptDescription != null && DeptDescription.Length > 200)//長度限制
{
resMsg = "敘述超出長度限制!!";
}
else
{
//檢查科系名稱是否已經存在
var sqlStr = string.Format("SELECT DeptNo From [dbo].[Department] WHERE DeptName={0}", SqlVal2(DeptName));
var data = _DB_GetData(sqlStr);
if (data.Rows.Count > 0)
{
//科系名稱已存在
resMsg = "新增失敗，該科系已存在！";
}
else
{
//SQL Insert
sqlStr = string.Format(
@"INSERT INTO [dbo].[Department](" +
"[DeptName]," +
"[DeptDescription]," +
"[CreateTime]," +
"[ModifyTime]," +
"[CreateUser] " +
") " +
"VALUES(" +
"{0}," +
"{1}," +
"{2}," +
"{3}," +
"{4}",
SqlVal2(DeptName),
SqlVal2(DeptDescription),
DBC.ChangeTimeZone(),
DBC.ChangeTimeZone(),
SqlVal2(Request.Cookies["account"]) + ")"
);
//執行是否成功
if (_DB_Execute(sqlStr) != 1)
{
resMsg = "新增失敗，若持續發生此問題，請聯絡我們。";
}
}
}
//Return
return(resMsg);
}
/// <summary>
/// 修改會員資料
/// </summary>
/// <param name="Model"></param>
/// <returns></returns>
public bool UpdateMember(MemberModels Model)
{
var sqlStr = "";
DateTime Temp = new DateTime();
//檢查年分
if (Model.Birthday != null)
{
Temp = (DateTime)Model.Birthday;
if (Temp.Year <= 1970)
{
return(false);
}
}
if (!string.IsNullOrEmpty(Model.StatusNo))
{
sqlStr = string.Format(
@"UPDATE [dbo].[Member] " +
"SET [Actualname] = {0}, " +
"[Username] = {1}, " +
"[Birthday] = {2}, " +
"[Gender] = {3}, " +
"[StatusNo] = {4}, " +
"[ModifyTime] = {5} " +
"WHERE [Account] = {6}",
SqlVal2(Model.Actualname),
SqlVal2(Model.Username),
SqlVal2(Model.Birthday),
SqlVal2(Model.Gender),
SqlVal2(Model.StatusNo),
DBC.ChangeTimeZone(),
SqlVal2(Model.Account)
);
}
else
{
sqlStr = string.Format(
@"UPDATE [dbo].[Member] " +
"SET [Actualname] = {0}, " +
"[Username] = {1}, " +
"[Birthday] = {2}, " +
"[Gender] = {3}, " +
"[ModifyTime] = {4} " +
"WHERE [Account] = {5}",
SqlVal2(Model.Actualname),
SqlVal2(Model.Username),
SqlVal2(Model.Birthday),
SqlVal2(Model.Gender),
DBC.ChangeTimeZone(),
SqlVal2(Model.Account)
);
}
var check = _DB_Execute(sqlStr);
//修改是否成功
if (check == 1)
{
//成功
return(true);
}
else
{
//失敗
return(false);
}
}
public bool InsertGrad(string year, string countryDeptNo, string gradNum)
{
if (getUserStatusNo() != "1")
{ // admin check
return(false);
}
else
{
// 檢查是否重複新增
var sqlSelect = string.Format("select 1 from [dbo].[Graduation] " +
"where GraduationYear={0} and CountryDeptNo={1}", SqlVal2(year), SqlVal2(countryDeptNo));
var dataSelect = _DB_GetData(sqlSelect);
if (dataSelect.Rows.Count > 0)
{
return(false);
}
}
int gradNumInt;
if (!int.TryParse(gradNum, out gradNumInt) || gradNumInt < 0)
{
return(false);
}
var sqlStr = string.Format("INSERT INTO [dbo].[Graduation] " +
"([CountryDeptNo] " +
",[GraduationYear] " +
",[GraduationNumber] " +
",[CreateTime] " +
",[ModifyTime] " +
",[CreateUser]) " +
"VALUES( " +
" {0} " +
",{1} " +
",{2} " +
",{3} " +
",{4} " +
",{5}) ", SqlVal2(countryDeptNo), SqlVal2(year), SqlVal2(gradNum), DBC.ChangeTimeZone(), DBC.ChangeTimeZone(), SqlVal2(Request.Cookies["account"]));
var check = _DB_Execute(sqlStr);
//新增是否成功
if (check == 1)
{
return(true);
}
else
{
return(false);
}
}
public ActionResult ChangePassword(AccountModels Model)
{
//把驗證碼寫進資料庫
//sql where
var sqlWhere = string.Format("Account = {0}", SqlVal2(Model.Account));
//sql str
var sqlStr = string.Format("UPDATE Member SET Password = {0}, PwdChangeCheck = {1}, ModifyTime = {2} where {3} and 1=1",
SqlVal2(SHA256_Encryption(Model.Password)), SqlVal2("0"), DBC.ChangeTimeZone(), sqlWhere);
//SQL Check Update成功(True)或失敗(False)
if (_DB_Execute(sqlStr) == 1)
{
// 刪除cookie，預設使用者關閉瀏覽器時清除
Response.Cookies.Delete("userName");
Response.Cookies.Delete("account");
//修改成功，重新登入
return(RedirectToAction("Login", "Account", new AccountModels()
{
Account = Model.Account
}));
}
else
{
//修改失敗，回傳
return(View(new AccountModels()
{
ok = false, ResultMessage = "修改失敗"
}));
}
}
public bool PasswordMailVerify(Member Model)
{
//SQL Insert Member
var sqlStr = string.Format("select Account,Username from [dbo].[Member] where Account = {0}", SqlVal2(Model.Account));
//SQL Check
var data = _DB_GetData(sqlStr);
//資料庫內是否有此帳號
if (data.Rows.Count > 0)
{
//AutoMail實體化
AutoMailClass mail = new AutoMailClass();
#region 亂數密碼
string ranNumber = "0123456789";
string ranUpper = "ABCDEFGHJKLMNOPQRSTUVWXYZ";
string ranLower = "abcdefghijkmnopqrstuvwxyz";
string ranSymbol = "!@#$%^&*";
//密碼長度
int passwordLength = 10;
//密碼 char
char[] chars = new char[passwordLength];
//Random 亂數實體化
Random rnd = new Random();
//開始亂數
for (int i = 0; i < passwordLength; i++)
{
if (i % 5 == 0)
{
chars[i] = ranNumber[rnd.Next(0, ranNumber.Length)];
}
else if (i % 3 == 0)
{
chars[i] = ranUpper[rnd.Next(0, ranUpper.Length)];
}
else if (i % 2 == 0)
{
chars[i] = ranLower[rnd.Next(0, ranLower.Length)];
}
else
{
chars[i] = ranSymbol[rnd.Next(0, ranSymbol.Length)];
}
}
//New Password
string pwd = new string(chars);
#endregion
//發送新密碼
if (mail.ForgetPasswordSend(Model.Account, data.Rows[0].ItemArray.GetValue(1).ToString(), pwd))
{
//把新密碼寫進資料庫
//sql where
var sqlWhere = string.Format("Account = {0}", SqlVal2(Model.Account));
//sql str
sqlStr = string.Format("UPDATE Member SET Password = {0}, PwdChangeCheck = {1}, ModifyTime = {2} where {3} and 1=1",
SqlVal2(SHA256_Encryption(pwd)), SqlVal2("1"), DBC.ChangeTimeZone(), sqlWhere);
//SQL Check Update成功(True)或失敗(False)
return(_DB_Execute(sqlStr) == 1 ? true : false);
}
else
{
//信件發送失敗
return(false);
}
}
else
{
//寄送失敗 找不到此帳號
return(false);
}
}
public ActionResult Register(Member Model)
{
Model.ok = true;
Model.MailCheck = "0";
Model.StatusNo = "0";
Model.PwdChangeCheck = "0";
//SQL Insert Member
var sqlStr = string.Format(
@"INSERT INTO [dbo].[Member] (" +
"[Account]," +
"[Password]," +
"[Username]," +
"[Actualname]," +
"[Gender]," +
"[Birthday]," +
"[MailCheck]," +
"[PwdChangeCheck]," +
"[CreateTime]," +
"[AccountStart]," +
"[StatusNo]" +
")VALUES(" +
"{0}," +
"{1}," +
"{2}," +
"{3}," +
"{4}," +
"{5}," +
"{6}," +
"{7}," +
"{8}," +
"{9}," +
"{10}",
SqlVal2(Model.Account),
SqlVal2(SHA256_Encryption(Model.Password)),
SqlVal2(Model.Username),
SqlVal2(Model.Actualname),
SqlVal2(Model.Gender),
SqlVal2(Model.Birthday),
SqlVal2(Model.MailCheck),
SqlVal2(Model.PwdChangeCheck),
DBC.ChangeTimeZone(),
DBC.ChangeTimeZone(),
SqlVal2(Model.StatusNo) + ")"
);
//SQL Check
var check = _DB_Execute(sqlStr);
//新增是否成功
if (check == 1)
{
//信箱驗證
if (RegisterMailVerify(Model))//Model.Account, Model.Username
{
Model.ResultMessage = "註冊成功";
//註冊成功
return(RedirectToAction("MailVerify", "Account", new Verify()
{
Account = Model.Account, Username = Model.Username
}));
}
else
{
Model.ok = false;
Model.ResultMessage = "驗證信發送失敗";
}
}
else
{
Model.ok = false;
Model.ResultMessage = "註冊失敗";
//註冊失敗
//return View(Model);
}
return(View(Model));
}
