public void clearLogActivity(string activity, out Error error)
{
error = null;
string strSQL = "";
SqlParameter sqlParam;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
THC_Library.DataBase.DataBaseControl dbCtl = new THC_Library.DataBase.DataBaseControl();
try
{
dbCtl.Open();
dbCtl.BeginTransaction();
strSQL = "delete from event_user_records where EUR002=@EUR002";
sqlParam = new SqlParameter("@EUR002", SqlDbType.Int);
sqlParam.Value = activity;
paraList.Add(sqlParam);
dbCtl.ExecuteCommad(strSQL, paraList);
dbCtl.CommintTransaction();
}
catch (Exception ex)
{
dbCtl.RollBackTransaction();
error = new Error();
error.Number = 305;
error.ErrorMessage = ex.ToString();
}
finally
{
dbCtl.Close();
}
}
public void requestResetPassword(string acc, out string access_code, out Error error)
{
error = null;
access_code = Guid.NewGuid().ToString().Replace("-", "").Trim();;
SqlParameter sqlParam;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "insert into password_temp (PT001,PT002,PT003) values " +
"(@PT001,@PT002,@PT003) ";
THC_Library.DataBase.DataBaseControl dbCtl = new THC_Library.DataBase.DataBaseControl();
try
{
sqlParam = new SqlParameter("@PT001", SqlDbType.VarChar);
sqlParam.Value = acc;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@PT002", SqlDbType.Char);
sqlParam.Value = access_code;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@PT003", SqlDbType.VarChar);
sqlParam.Value = DateTime.Now.ToString("yyyy/MM/dd");
paraList.Add(sqlParam);
dbCtl.Open();
dbCtl.ExecuteCommad(strSQL, paraList);
}
catch (Exception ex)
{
error = new Error();
error.Number = 301;
error.ErrorMessage = ex.ToString();
}
}
public int updateAccountMobil(string acccount, string timestamp, string mobil, out Error error)
{
error = null;
int iUpdateCount = 0;
SqlParameter sqlParam;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "update consumer_member set CM008=@CM008 " +
"where CM002=@CM002 and CM016=@CM016";
//CM008 手機
THC_Library.DataBase.DataBaseControl dbCtl = new THC_Library.DataBase.DataBaseControl();
try
{
paraList.Clear();
sqlParam = new SqlParameter("@CM008", mobil);
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@CM002", acccount);
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@CM016", SqlDbType.BigInt);
sqlParam.Value = long.Parse(timestamp);
paraList.Add(sqlParam);
dbCtl.Open();
iUpdateCount = dbCtl.ExecuteCommad(strSQL, paraList);
}
catch (Exception ex)
{
error = new Error();
error.Number = 100;
error.ErrorMessage = ex.ToString(); //"資料更新系統錯誤";
}
finally
{
dbCtl.Close();
}
return(iUpdateCount);
}
public JsonResult doc(string code)
{
string retJson = "";
if (string.IsNullOrEmpty(code))
{
retJson = "{\"RESPONSE\" : \"ABORT\"}";
return(Json(retJson, "application/json", JsonRequestBehavior.AllowGet));
}
if (code != "adDDFasF")
{
retJson = "{\"RESPONSE\" : \"ABORT\"}";
return(Json(retJson, "application/json", JsonRequestBehavior.AllowGet));
}
IList <System.Data.SqlClient.SqlParameter> paraList =
new System.Collections.Generic.List <System.Data.SqlClient.SqlParameter>();
string strSQL = "update qr_record set QRC012=0,QRC013=NULL,QRC016=NULL";
THC_Library.DataBase.DataBaseControl dbCtl = new THC_Library.DataBase.DataBaseControl();
try
{
dbCtl.Open();
dbCtl.ExecuteCommad(strSQL, paraList);
retJson = "{\"RESPONSE\" : \"DONE\"}";
}
catch (Exception ex)
{
retJson = "{\"RESPONSE\" : \"ERROR\"}";
}
finally
{
dbCtl.Close();
}
return(Json(retJson, "application/json", JsonRequestBehavior.AllowGet));
}
public void doResetPassword(string acc, string access_code, string pwd, out Error error)
{
error = null;
bool bCodeExist = false;
SqlParameter sqlParam;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select PT001 from password_temp where PT001=@PT001 and PT002=@PT002";
THC_Library.DataBase.DataBaseControl dbCtl = new THC_Library.DataBase.DataBaseControl();
try
{
sqlParam = new SqlParameter("@PT001", SqlDbType.VarChar);
sqlParam.Value = acc;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@PT002", SqlDbType.Char);
sqlParam.Value = access_code;
paraList.Add(sqlParam);
dbCtl.Open();
IDataReader dataReader = dbCtl.GetReader(strSQL, paraList);
if (dataReader.Read())
{
bCodeExist = true;
}
dataReader.Close();
if (bCodeExist)
{
byte[] pwdBytes = System.Text.Encoding.Default.GetBytes(pwd); //將字串來源轉為Byte[]
System.Security.Cryptography.MD5 md5 = System.Security.Cryptography.MD5.Create(); //使用MD5
pwdBytes = md5.ComputeHash(pwdBytes); //進行加密
pwd = Convert.ToBase64String(pwdBytes); //將加密後的字串從byte[]轉回string
dbCtl.BeginTransaction();
strSQL = "update consumer_member set CM007=@CM007 where CM002=@CM002";
paraList.Clear();
sqlParam = new SqlParameter("@CM007", SqlDbType.VarChar);
sqlParam.Value = pwd;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@CM002", SqlDbType.VarChar);
sqlParam.Value = acc;
paraList.Add(sqlParam);
dbCtl.ExecuteCommad(strSQL, paraList);
strSQL = "delete password_temp where PT001=@PT001";
paraList.Clear();
sqlParam = new SqlParameter("@PT001", SqlDbType.VarChar);
sqlParam.Value = acc;
paraList.Add(sqlParam);
dbCtl.ExecuteCommad(strSQL, paraList);
dbCtl.CommintTransaction();
}
else
{
throw new THC_Library.THCException(330, "無效的授權");
}
}
catch (THCException thcEx)
{
dbCtl.RollBackTransaction();
error = new Error();
error.Number = thcEx.Number;
error.ErrorMessage = thcEx.Message;
}
catch (Exception ex)
{
dbCtl.RollBackTransaction();
error = new Error();
error.Number = 301;
error.ErrorMessage = ex.ToString();
}
}
public long loginFromActivity(string acc, string tk, out Error error)
{
error = null;
long newTicket = -1;
IDataReader dataReader;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select * from consumer_member where CM002=@CM002 and CM016=@CM016";
THC_Library.DataBase.DataBaseControl dbCtl = new THC_Library.DataBase.DataBaseControl();
paraList.Add(new SqlParameter("@CM002", acc));
paraList.Add(new SqlParameter("@CM016", tk));
bool bchkSession = false;
try
{
dbCtl.Open();
dataReader = dbCtl.GetReader(strSQL, paraList);
if (dataReader.Read())
{
bchkSession = true;
}
dataReader.Close();
if (bchkSession)
{
newTicket = DateTime.Now.Ticks;
strSQL = "update consumer_member set CM016=@CM016 where CM002=@CM002";
paraList.Clear();
paraList.Add(new SqlParameter("@CM016", newTicket));
paraList.Add(new SqlParameter("@CM002", acc));
dbCtl.ExecuteCommad(strSQL, paraList);
}
else
{
THCException thcEx = new THCException(BASE_ERROR + 7, "無效的登入");
throw thcEx;
}
}
catch (THCException thcEx)
{
error = new Error();
error.Number = thcEx.Number;
error.ErrorMessage = thcEx.Message;
}
catch (Exception ex)
{
error = new Error();
error.Number = BASE_ERROR + 6;
error.ErrorMessage = "重啟登入系統錯誤";
}
finally
{
dbCtl.Close();
}
return(newTicket);
}
public long verifyFaceBookAccount(string mail, string name, string gender, out string account, out Error error)
{
error = null;
account = "";
long lgTimestamp = -1;
bool bHasAccount = false;
string strAcc = "";
string strMail = "";
SqlParameter sqlParam;
IDataReader dataReader;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select CM002,CM017 from consumer_member where CM003=@CM003";
THC_Library.DataBase.DataBaseControl dbCtl = new THC_Library.DataBase.DataBaseControl();
try
{
sqlParam = new SqlParameter("@CM003", mail);
paraList.Add(sqlParam);
dbCtl.Open();
dataReader = dbCtl.GetReader(strSQL, paraList);
if (dataReader.Read())
{
strAcc = dataReader["CM002"].ToString();
strMail = dataReader["CM017"].ToString();
bHasAccount = true;
}
dataReader.Close();
if (bHasAccount)
{
lgTimestamp = DateTime.Now.Ticks;
strSQL = "update consumer_member set CM016=@CM016 where CM002=@CM002";
paraList.Clear();
sqlParam = new SqlParameter("@CM016", lgTimestamp);
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@CM002", strAcc);
paraList.Add(sqlParam);
dbCtl.ExecuteCommad(strSQL, paraList);
account = strAcc;
}
else
{
//以FB註冊會員資料
strSQL = "insert into consumer_member (CM002,CM003,CM006,CM007,CM012,CM014,CM016,CM017) values " +
"(@CM002,@CM003,@CM006,@CM007,@CM012,@CM014,@CM016,@CM017);SELECT CAST(scope_identity() AS int);";
paraList.Clear();
sqlParam = new SqlParameter("@CM002", mail);
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@CM003", mail);
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@CM006", name);
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@CM007", "");
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@CM012", gender);
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@CM014", DateTime.Now);
paraList.Add(sqlParam);
lgTimestamp = DateTime.Now.Ticks;
sqlParam = new SqlParameter("@CM016", lgTimestamp);
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@CM017", SqlDbType.VarChar);
sqlParam.Value = mail;
paraList.Add(sqlParam);
object accKey = dbCtl.ExecuteScalar(strSQL, paraList);
int iaccKey = Convert.ToInt32(accKey);
account = mail;
}
}
catch (SqlException sqlEx)
{
error = new Error();
if (sqlEx.Number == 2601)
{
error.Number = 101;
error.ErrorMessage = "帳號已註冊";
}
else
{
error.Number = 100;
error.ErrorMessage = sqlEx.ToString(); //"系統錯誤";
}
}
catch (Exception ex)
{
error = new Error();
error.Number = 100;
error.ErrorMessage = ex.ToString();// "系統錯誤";
}
finally
{
dbCtl.Close();
}
return(lgTimestamp);
}
public int updateAccount(string acccount, string timestamp, string mobil, string gender, string age,
string iid, string addr, byte[] image, out Error error)
{
error = null;
int iUpdateCount = 0;
SqlParameter sqlParam;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "update consumer_member set CM008=@CM008,CM009=@CM009,CM010=@CM010,CM012=@CM012,CM013=@CM013,CM018=@CM018 " +
"where CM002=@CM002 and CM016=@CM016";
//CM008 手機 CM009 地址 CM010 身分證號 CM012 性別 CM013 年齡
THC_Library.DataBase.DataBaseControl dbCtl = new THC_Library.DataBase.DataBaseControl();
try
{
paraList.Clear();
sqlParam = new SqlParameter("@CM008", mobil);
paraList.Add(sqlParam);
if (addr == null)
{
sqlParam = new SqlParameter("@CM009", DBNull.Value);
}
else
{
sqlParam = new SqlParameter("@CM009", addr);
}
paraList.Add(sqlParam);
if (iid == null)
{
sqlParam = new SqlParameter("@CM010", DBNull.Value);
}
else
{
sqlParam = new SqlParameter("@CM010", iid);
}
paraList.Add(sqlParam);
if (gender == null)
{
sqlParam = new SqlParameter("@CM012", DBNull.Value);
}
else
{
sqlParam = new SqlParameter("@CM012", gender);
}
paraList.Add(sqlParam);
if (age == null)
{
sqlParam = new SqlParameter("@CM013", DBNull.Value);
}
else
{
sqlParam = new SqlParameter("@CM013", age);
}
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@CM002", acccount);
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@CM016", SqlDbType.BigInt);
sqlParam.Value = long.Parse(timestamp);
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@CM018", SqlDbType.Image);
if (image == null)
{
sqlParam.Value = DBNull.Value;
}
else
{
sqlParam.Value = image;
}
paraList.Add(sqlParam);
dbCtl.Open();
iUpdateCount = dbCtl.ExecuteCommad(strSQL, paraList);
}
catch (Exception ex)
{
error = new Error();
error.Number = 100;
error.ErrorMessage = ex.ToString(); //"資料更新系統錯誤";
}
finally
{
dbCtl.Close();
}
return(iUpdateCount);
}
public long verifyAccount(string mail, string pwd, out int state, out Error error)
{
error = null;
state = 0; // 1 帳號不存在 2 密碼錯誤
long lgTimestamp = -1;
bool bReturn = false;
SqlParameter sqlParam;
IDataReader dataReader;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select CM007 from consumer_member where CM002=@CM002";
THC_Library.DataBase.DataBaseControl dbCtl = new THC_Library.DataBase.DataBaseControl();
try
{
byte[] pwdBytes = System.Text.Encoding.Default.GetBytes(pwd); //將字串來源轉為Byte[]
System.Security.Cryptography.MD5 md5 = System.Security.Cryptography.MD5.Create(); //使用MD5
pwdBytes = md5.ComputeHash(pwdBytes); //進行加密
pwd = Convert.ToBase64String(pwdBytes); //將加密後的字串從byte[]轉回string
sqlParam = new SqlParameter("@CM002", mail);
paraList.Add(sqlParam);
dbCtl.Open();
dataReader = dbCtl.GetReader(strSQL, paraList);
if (dataReader.Read())
{
string realPwd = dataReader["CM007"].ToString();
if (string.Compare(realPwd, pwd) == 0)
{
state = 0;
bReturn = true;
}
else
{
state = 2;
}
}
else
{
state = 1;
}
dataReader.Close();
if (bReturn)
{
lgTimestamp = DateTime.Now.Ticks;
strSQL = "update consumer_member set CM016=@CM016 where CM002=@CM002";
paraList.Clear();
sqlParam = new SqlParameter("@CM016", lgTimestamp);
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@CM002", mail);
paraList.Add(sqlParam);
dbCtl.ExecuteCommad(strSQL, paraList);
}
}
catch (SqlException sqlEx)
{
error = new Error();
if (sqlEx.Number == 2601)
{
error.Number = 101;
error.ErrorMessage = "帳號已註冊";
}
else
{
error.Number = 100;
error.ErrorMessage = "系統錯誤";
}
}
catch (Exception ex)
{
error = new Error();
error.Number = 100;
error.ErrorMessage = "系統錯誤";
}
finally
{
dbCtl.Close();
}
return(lgTimestamp);
}
public void asyncActivity(string activity, out Error error)
{
error = null;
string strSQL = "";
SqlParameter sqlParam;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
THC_Library.DataBase.DataBaseControl dbCtl = new THC_Library.DataBase.DataBaseControl();
try
{
dynamic arrayJson = Newtonsoft.Json.JsonConvert.DeserializeObject(activity);
dynamic activityObj = arrayJson[0];
dbCtl.Open();
dbCtl.BeginTransaction();
paraList.Clear();
strSQL = "delete from activity_event where AE001=@AE001";
sqlParam = new SqlParameter("@AE001", SqlDbType.Int);
sqlParam.Value = activityObj.AE001;
paraList.Add(sqlParam);
dbCtl.ExecuteCommad(strSQL, paraList);
paraList.Clear();
sqlParam = new SqlParameter("@AE001", SqlDbType.Int);
sqlParam.Value = activityObj.AE001;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@AE002", SqlDbType.NVarChar);
sqlParam.Value = activityObj.AE002;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@AE003", SqlDbType.NVarChar);
sqlParam.Value = activityObj.AE003;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@AE004", SqlDbType.NVarChar);
sqlParam.Value = activityObj.AE004;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@AE005", SqlDbType.VarChar);
sqlParam.Value = activityObj.AE005;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@AE006", SqlDbType.VarChar);
sqlParam.Value = activityObj.AE006;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@AE007", SqlDbType.Int);
sqlParam.Value = activityObj.AE007;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@AE008", SqlDbType.NVarChar);
sqlParam.Value = activityObj.AE008;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@AE009", SqlDbType.VarChar);
sqlParam.Value = activityObj.AE009;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@AE010", SqlDbType.Char);
sqlParam.Value = activityObj.AE010;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@AE011", SqlDbType.NVarChar);
sqlParam.Value = activityObj.AE011;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@AE012", SqlDbType.SmallInt);
sqlParam.Value = activityObj.AE012;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@AE013", SqlDbType.VarChar);
sqlParam.Value = activityObj.AE013;
paraList.Add(sqlParam);
strSQL = "insert into activity_event values (@AE001,@AE002,@AE003,@AE004,@AE005,@AE006," +
"@AE007,@AE008,@AE009,@AE010,@AE011,@AE012,@AE013)";
dbCtl.ExecuteCommad(strSQL, paraList);
dbCtl.CommintTransaction();
}
catch (Exception ex)
{
dbCtl.RollBackTransaction();
error = new Error();
error.Number = 305;
error.ErrorMessage = ex.ToString();
}
finally
{
dbCtl.Close();
}
}