public DataTable getEventRewards(string event_no, out THC_Library.Error error)
{
error = null;
DataTable resultTable = null;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select AEP001,AEP002,AEP003,AEP004,AEP005,AEP006,AEP007,AEP009,AEP011,AEP012,AEP013 " + //(SUBSTRING(AEP013,0,20) + '.....') as AEP013 " +
"from activity_rewards where AEP002=@AEP002";
paraList.Add(new SqlParameter("@AEP002", event_no));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
resultTable = dbCtl.GetDataTable(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(resultTable);
}
public DataTable getUserInfo(string account, out THC_Library.Error error)
{
error = null;
DataTable resultTable = null;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select AU001,AU002,AU003,AU004 from activity_user where AU001=@AU001";
paraList.Add(new SqlParameter("@AU001", account));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
resultTable = dbCtl.GetDataTable(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(resultTable);
}
public void AsyneEvent(string event_no, out THC_Library.Error error)
{
error = null;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select * from activity_event where AE002=@AE002";
paraList.Add(new SqlParameter("@AE002", event_no));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
DataTable eventTable = dbCtl.GetDataTable(strSQL, paraList);
string eventJson = Newtonsoft.Json.JsonConvert.SerializeObject(eventTable);
string jsonResult = THC_Library.APPCURL.AnscyActivity(eventJson);
dynamic resultObj = Newtonsoft.Json.JsonConvert.DeserializeObject(jsonResult);
if (resultObj.Number != 0)
{
throw new Exception(resultObj.ErrorMessage.ToString());
}
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
}
public DataTable getTimeTemptrue(string event_no, string days, out THC_Library.Error error)
{
error = null;
DataTable resultTable = null;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select CAST(EUR004 AS DATE) as DATE,AVG(EUR009) as TEMP,count(*) as COUNT from event_user_records " +
"where EUR002=@EUR002 and DATEDIFF(day,EUR004,GETDATE()) < " + days + " " +
"group by CAST(EUR004 AS DATE)";
paraList.Add(new SqlParameter("@EUR002", event_no));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
resultTable = dbCtl.GetDataTable(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(resultTable);
}
public DataTable getScanGender(string event_no, out THC_Library.Error error)
{
error = null;
DataTable resultTable = null;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select EUR007 as GENDER,count(EUR003) as VALUE from event_user_records " +
"where EUR002=@EUR002 group by EUR007";
paraList.Add(new SqlParameter("@EUR002", event_no));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
resultTable = dbCtl.GetDataTable(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(resultTable);
}
public bool checkLocalAccount(string account, out Error error)
{
error = null;
bool bExisted = false;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select CM002 from consumer_member where CM002=@CM002";
paraList.Add(new SqlParameter("@CM002", account));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
IDataReader dataReader = dbCtl.GetReader(strSQL, paraList);
if (dataReader.Read())
{
bExisted = true;
}
dataReader.Close();
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(bExisted);
}
public DataTable getWeather(out THC_Library.Error error)
{
error = null;
DataTable resultTable = null;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select * from weather";
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
resultTable = dbCtl.GetDataTable(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(resultTable);
}
public DataTable getMyEvent(string event_no, out THC_Library.Error error)
{
error = null;
DataTable resultTable = null;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select * from activity_event where AE002=@AE002";
paraList.Add(new SqlParameter("@AE002", event_no));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
resultTable = dbCtl.GetDataTable(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(resultTable);
}
public void clearLoginTime(string account, out THC_Library.Error error)
{
error = null;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "update activity_user set AU004=NULL where AU001=@AU001";
paraList.Add(new SqlParameter("@AU001", account));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
dbCtl.ExecuteCommad(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
}
public int deleteUser(string account, out THC_Library.Error error)
{
error = null;
int iExcuteCount = -1;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "delete from activity_user where AU001=@AU001";
paraList.Add(new SqlParameter("@AU001", account));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
iExcuteCount = dbCtl.ExecuteCommad(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(iExcuteCount);
}
public void updateEvent(string event_no, string page, out THC_Library.Error error)
{
error = null;
SqlParameter sqlParam;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "update activity_event set AE013=@AE013 where AE002=@AE002";
DataBaseControl dbCtl = new DataBaseControl();
try
{
sqlParam = new SqlParameter("@AE013", SqlDbType.VarChar);
sqlParam.Value = page;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@AE002", SqlDbType.NVarChar);
sqlParam.Value = event_no;
paraList.Add(sqlParam);
dbCtl.Open();
dbCtl.ExecuteCommad(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
}
public DataTable getScanCount_InDay_7(string event_no, out THC_Library.Error error)
{
error = null;
DataTable resultTable = null;
DateTime datNow = DateTime.Now;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select count(EUR003) as VALUE, CAST(EUR004 AS DATE) as DATE from event_user_records " +
"where EUR002=@EUR002 and DATEDIFF(day,EUR004,GETDATE()) < 7 " +
"group by CAST(EUR004 AS DATE)";
paraList.Add(new SqlParameter("@EUR002", event_no));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
resultTable = dbCtl.GetDataTable(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(resultTable);
}
public void changePassword(string account, string old, string new1, string new2, out THC_Library.Error error)
{
error = null;
byte[] pwdBytes = System.Text.Encoding.Default.GetBytes(old);
System.Security.Cryptography.MD5 md5 = System.Security.Cryptography.MD5.Create();
pwdBytes = md5.ComputeHash(pwdBytes);
string strOldPwd = Convert.ToBase64String(pwdBytes);
pwdBytes = System.Text.Encoding.Default.GetBytes(new1);
md5 = System.Security.Cryptography.MD5.Create();
pwdBytes = md5.ComputeHash(pwdBytes);
string strNewPwd = Convert.ToBase64String(pwdBytes);
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select AU003 from activity_user where AU001=@AU001";
paraList.Add(new SqlParameter("@AU001", account));
DataBaseControl dbCtl = new DataBaseControl();
try
{
if (new1 != new2)
{
throw new Exception("新密碼不相符");
}
dbCtl.Open();
IDataReader dataReader = dbCtl.GetReader(strSQL, paraList);
dataReader.Read();
string strOld = dataReader["AU003"].ToString();
dataReader.Close();
if (strOldPwd != strOld)
{
throw new Exception("舊密碼輸入錯誤");
}
strSQL = "update activity_user set AU003=@AU003 where AU001=@AU001";
paraList.Clear();
paraList.Add(new SqlParameter("@AU003", strNewPwd));
paraList.Add(new SqlParameter("@AU001", account));
dbCtl.ExecuteCommad(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
}
public int updateUser(string account, string name, string access_code, out THC_Library.Error error)
{
error = null;
int iExcuteCount = -1;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select AU003 from activity_user where AU001=@AU001";
paraList.Add(new SqlParameter("@AU001", account));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
IDataReader dataReader = dbCtl.GetReader(strSQL, paraList);
dataReader.Read();
string orgPassword = dataReader["AU003"].ToString();
dataReader.Close();
if (orgPassword == access_code)
{
strSQL = "update activity_user set AU002=@AU002 where AU001=@AU001";
paraList.Clear();
paraList.Add(new SqlParameter("@AU002", name));
paraList.Add(new SqlParameter("@AU001", account));
}
else
{
byte[] pwdBytes = System.Text.Encoding.Default.GetBytes(access_code);
System.Security.Cryptography.MD5 md5 = System.Security.Cryptography.MD5.Create();
pwdBytes = md5.ComputeHash(pwdBytes);
string strPwd = Convert.ToBase64String(pwdBytes);
strSQL = "update activity_user set AU002=@AU002,AU003=@AU003 where AU001=@AU001";
paraList.Clear();
paraList.Add(new SqlParameter("@AU002", name));
paraList.Add(new SqlParameter("@AU003", strPwd));
paraList.Add(new SqlParameter("@AU001", account));
}
iExcuteCount = dbCtl.ExecuteCommad(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(iExcuteCount);
}
/// <summary>
/// 參與率次數比例
/// </summary>
public DataTable getScanRate(string event_no, string counter, out string total, out THC_Library.Error error)
{
error = null;
total = "";
//select EUR005,count(EUR005) as cc from event_user_records
//where EUR002=1033
//group by EUR005
//having count(EUR005) > 2
error = null;
DataTable resultTable = null;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select EUR005 as acc,count(EUR005) as cc,CM007 as tel from event_user_records " +
"left join consumer_member on EUR005=CM002 " +
"where EUR002=@EUR002 group by EUR005,CM007 having count(EUR005) >= @counter " +
"order by cc";
paraList.Add(new SqlParameter("@EUR002", event_no));
paraList.Add(new SqlParameter("@counter", counter));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
resultTable = dbCtl.GetDataTable(strSQL, paraList);
strSQL = "select count(distinct EUR005) from event_user_records where EUR002=@EUR002";
paraList.Clear();
paraList.Add(new SqlParameter("@EUR002", event_no));
IDataReader dataReader = dbCtl.GetReader(strSQL, paraList);
dataReader.Read();
total = dataReader[0].ToString();
dataReader.Close();
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(resultTable);
return(null);
}
public void ClearEvent(string event_no, out THC_Library.Error error)
{
error = null;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select AE001 from activity_event where AE002=@AE002";
paraList.Add(new SqlParameter("@AE002", event_no));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
IDataReader dataReader = dbCtl.GetReader(strSQL, paraList);
dataReader.Read();
string eventKey = dataReader[0].ToString();
dataReader.Close();
paraList.Clear();
strSQL = "update qr_record set QRC012=0,QRC013=NULL,QRC014=NULL,QRC016=NULL " +
"where QRC002=@QRC002;delete from event_user_records where EUR003=@EUR003";
paraList.Add(new SqlParameter("@QRC002", event_no));
paraList.Add(new SqlParameter("@EUR003", event_no));
dbCtl.BeginTransaction();
string jsonResult = THC_Library.APPCURL.ClearRecordLogActivity(eventKey);
dynamic resultObj = Newtonsoft.Json.JsonConvert.DeserializeObject(jsonResult);
if (resultObj.Number != 0)
{
throw new Exception(resultObj.ErrorMessage.ToString());
}
dbCtl.ExecuteCommad(strSQL, paraList);
dbCtl.CommintTransaction();
}
catch (Exception ex)
{
dbCtl.RollBackTransaction();
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
}
public void getScanRate(string event_no, out float scan, out float total, out float rate, out THC_Library.Error error)
{
error = null;
scan = 0;
total = 0;
rate = 0;
IDataReader dataReader;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select count(*) from qr_record where QRC002=@QRC002 and QRC013 is not NULL";
paraList.Add(new SqlParameter("@QRC002", event_no));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
dataReader = dbCtl.GetReader(strSQL, paraList);
dataReader.Read();
scan = float.Parse(dataReader[0].ToString());
dataReader.Close();
paraList.Clear();
strSQL = "select AE007 from activity_event where AE002=@AE002";
paraList.Add(new SqlParameter("@AE002", event_no));
dataReader = dbCtl.GetReader(strSQL, paraList);
dataReader.Read();
total = float.Parse(dataReader[0].ToString());
dataReader.Close();
rate = (float)Math.Round((double)(scan / total), 4);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
}
public DataTable getRewardEarnList(string event_no, out THC_Library.Error error)
{
//: "QRC001", bVisible: false },
//{ "title": "序號", "data": "QRC004", bVisible: true },
//{ "title": "獎項碼", "data": "QRC008", bVisible: true },
//{ "title": "獎項名稱", "data": "QRC011", bVisible: true },
//{ "title": "掃描時間", "data": "QRC013", bVisible: true },
//{ "title": "實際碼", "data": "QRC015", bVisible: true },
//{ "title": "得獎帳號", "data": "QRC016", bVisible: true }
error = null;
DataTable resultTable = null;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select QRC001,QRC004,QRC008,QRC011,QRC013,QRC015,QRC016 from qr_record " +
"where QRC002=@QRC002 and QRC016 IS NOT NULL";
SqlParameter sqlParam = new SqlParameter("@QRC002", SqlDbType.NVarChar);
sqlParam.Value = event_no;
paraList.Add(sqlParam);
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
resultTable = dbCtl.GetDataTable(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(resultTable);
}
public DataTable exportRewardEarn(string event_no, out THC_Library.Error error)
{
error = null;
DataTable resultTable = null;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select QRC001,QRC004,QRC008,QRC011,QRC013,QRC015,QRC016 from qr_record " +
"where QRC002=@QRC002 and QRC016 IS NOT NULL";
SqlParameter sqlParam = new SqlParameter("@QRC002", SqlDbType.NVarChar);
sqlParam.Value = event_no;
paraList.Add(sqlParam);
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
resultTable = dbCtl.GetDataTable(strSQL, paraList);
//foreach (DataRow row in resultTable.Rows)
//{
//}
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(resultTable);
}
public DataTable getTimeCountByArea(string event_no, string days, out THC_Library.Error error)
{
// select CAST(EUR004 AS DATE) as DATE,count(*),WH004 as COUNT
//from event_user_records left join weather on EUR008=WH001
// where EUR002=1024 and DATEDIFF(day,EUR004,GETDATE()) < 21
// group by CAST(EUR004 AS DATE), WH004
error = null;
DataTable resultTable = null;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select CAST(EUR004 AS DATE) as DATE,count(*) as COUNT,WH004 as AREA " +
"from event_user_records left join weather on EUR008=WH001 " +
"where EUR002=@EUR002 and DATEDIFF(day,EUR004,GETDATE()) < " + days + " " +
" group by CAST(EUR004 AS DATE), WH004 order by DATE";
paraList.Add(new SqlParameter("@EUR002", event_no));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
resultTable = dbCtl.GetDataTable(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(resultTable);
}
public int updateRewardInfo(string reward_key, string name, string memo, string vender,
string img, string vdate, string sms, out THC_Library.Error error)
{
error = null;
int iAffrect = 0;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "update activity_rewards set AEP005=@AEP005,AEP007=@AEP007,AEP009=@AEP009,AEP011=@AEP011,AEP012=@AEP012,AEP013=@AEP013 " +
"where AEP001=@AEP001";
paraList.Add(new SqlParameter("@AEP005", name));
paraList.Add(new SqlParameter("@AEP007", memo));
paraList.Add(new SqlParameter("@AEP009", vender));
paraList.Add(new SqlParameter("@AEP011", img));
paraList.Add(new SqlParameter("@AEP012", vdate));
paraList.Add(new SqlParameter("@AEP013", sms));
paraList.Add(new SqlParameter("@AEP001", reward_key));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
iAffrect = dbCtl.ExecuteCommad(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(iAffrect);
}
public int addNewAccount(string account, string name, string access_code, out THC_Library.Error error)
{
error = null;
int iExcuteCount = -1;
byte[] pwdBytes = System.Text.Encoding.Default.GetBytes(access_code);
System.Security.Cryptography.MD5 md5 = System.Security.Cryptography.MD5.Create();
pwdBytes = md5.ComputeHash(pwdBytes);
string strPwd = Convert.ToBase64String(pwdBytes);
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "insert into activity_user (AU001,AU002,AU003) values (@AU001,@AU002,@AU003)";
paraList.Add(new SqlParameter("@AU001", account));
paraList.Add(new SqlParameter("@AU002", name));
paraList.Add(new SqlParameter("@AU003", strPwd));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
iExcuteCount = dbCtl.ExecuteCommad(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(iExcuteCount);
}
public bool go(string ac, string code, string tk, string ml, string city, string lat, string lng, out int event_no,
out string gender, out string age, out string mobil, out string iid, out string addr,
out THC_Library.Reward.RewardConvertor rwd, out int logkey, out THC_Library.Error error)
{
error = null;
rwd = null;
event_no = -1;
gender = "";
age = "";
mobil = "";
iid = "";
addr = "";
logkey = -1;
DateTime datNow = DateTime.Now;
DateTime datNowDate = new DateTime(datNow.Year, datNow.Month, datNow.Day);
//int iIdentityKey;
//int eventKey = -1;
string eventName = "";
DateTime startTime = DateTime.MaxValue;
DateTime endTime = DateTime.MinValue;
SqlParameter sqlParam;
IDataReader dataReader;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
DataBaseControl dbCtl = new DataBaseControl();
//paraList.Add(new SqlParameter("@EQCH002", event_key));
string strSQL = "select * from activity_event where AE002=@AE002";
paraList.Add(new SqlParameter("@AE002", ac));
bool bRightEvent = false;
bool bKeyExist = false;
bool bWin = false;
try
{
dbCtl.Open();
dataReader = dbCtl.GetReader(strSQL, paraList);
if (dataReader.Read())
{
bRightEvent = true;
event_no = int.Parse(dataReader["AE001"].ToString());
eventName = dataReader["AE003"].ToString();
startTime = Convert.ToDateTime(dataReader["AE005"]);
endTime = Convert.ToDateTime(dataReader["AE006"]);
}
dataReader.Close();
if (!bRightEvent)
{
throw new THC_Library.CodeRenderException(THC_Library.CodeRenderException.INVAILD_ACTIVITY, "無效的活動");
}
else
{
if (startTime.Subtract(datNowDate).TotalDays > 0)
{
//未開始
THC_Library.CodeRenderException codeException =
new THC_Library.CodeRenderException(THC_Library.CodeRenderException.ACTIVITY_NOT_START, "活動尚未開始");
codeException.AdditionalMessage = string.Format("{0} 活動期間 {1} - {2}", eventName, startTime, endTime);
throw codeException;
}
if (endTime.Subtract(datNowDate).TotalDays < 0)
{
//結束
THC_Library.CodeRenderException codeException = new THC_Library.CodeRenderException(THC_Library.CodeRenderException.ACTIVITY_FINISHED, "活動已結束");
codeException.AdditionalMessage = string.Format("{0} 活動期間 {1} - {2}", eventName, startTime, endTime);
throw codeException;
}
}
//確認登入
bool bLoginChecked = false;
string jsonString = THC_Library.APPCURL.GetAccountInfo(ml, tk);
dynamic jsonResult = Newtonsoft.Json.JsonConvert.DeserializeObject(jsonString);
if (jsonResult.Number == 0)
{
//AccountInfo
bLoginChecked = true;
mobil = jsonResult.Mobil;
addr = jsonResult.Address;
iid = jsonResult.IId;
gender = jsonResult.Gender;
age = jsonResult.Age;
}
else
{
//Result
bLoginChecked = false;
}
if (!bLoginChecked)
{
THC_Library.CodeRenderException codeException =
new THC_Library.CodeRenderException(THC_Library.CodeRenderException.LOGIN_INVALID, "無效登入");
throw codeException;
}
//
//strSQL = "select * from qr_record where QRC015=@QRC015";
strSQL = "select qr_record.*,AEP003,AEP005,AEP007,AEP011,AEP012 " +
"from qr_record left join activity_rewards on QRC018=AEP001 where QRC015=@QRC015";
paraList.Clear();
paraList.Add(new SqlParameter("@QRC015", code));
dataReader = dbCtl.GetReader(strSQL, paraList);
object EC = "";
string rwdType = "";
if (dataReader.Read())
{
rwdType = dataReader["AEP003"].ToString();
int iScanCounter = int.Parse(dataReader["QRC012"].ToString());
if (iScanCounter == 0)
{
//未掃描過
//中獎與否
EC = dataReader["QRC008"];
if (EC != DBNull.Value && EC.ToString().Length > 0)
{
bWin = true;
THC_Library.Reward.RewardConvertor rwdConvertor;
if (dataReader["AEP003"].ToString() == "0")
{
//虛擬
rwdConvertor = new THC_Library.Reward.Edenred();
THC_Library.Reward.Edenred edenred = rwdConvertor as THC_Library.Reward.Edenred;
edenred.RewardName = dataReader["AEP005"].ToString(); //dataReader["QRC011"].ToString();
edenred.RewardType = THC_Library.Reward.RewardType.ElectricCoupon;
edenred.CouponNumber = EC.ToString();
if (dataReader["QRC018"] != DBNull.Value)
{
edenred.ValidPeriod = dataReader["AEP012"].ToString();
edenred.RewardImage = dataReader["AEP011"].ToString();
//string strJSon = dataReader["QRC017"].ToString();
//if (strJSon.Length > 0)
//{
// dynamic jsonReward = Newtonsoft.Json.JsonConvert.DeserializeObject(strJSon);
// edenred.ValidPeriod = jsonReward.v_date;
// edenred.RewardImage = jsonReward.img;
//}
}
}
else
{
//實體
rwdConvertor = new THC_Library.Reward.Phyicalenred();
THC_Library.Reward.Phyicalenred phyenred = rwdConvertor as THC_Library.Reward.Phyicalenred;
phyenred.RewardName = dataReader["AEP005"].ToString();
phyenred.RewardType = THC_Library.Reward.RewardType.PhyicalReward;
phyenred.CouponNumber = EC.ToString();
if (dataReader["QRC018"] != DBNull.Value)
{
phyenred.Description = dataReader["AEP007"].ToString();
phyenred.RewardImage = dataReader["AEP011"].ToString();
//string strJSon = dataReader["QRC017"].ToString();
//if (strJSon.Length > 0)
//{
// dynamic jsonReward = Newtonsoft.Json.JsonConvert.DeserializeObject(strJSon);
// phyenred.Description = jsonReward.desc;
// phyenred.RewardImage = jsonReward.img;
//}
}
}
rwd = rwdConvertor;
}
bKeyExist = true;
}
else
{
//重覆掃描
DateTime lastTime;
DateTime.TryParse(dataReader["QRC013"].ToString(), out lastTime);
dataReader.Close();
THC_Library.CodeRenderException codeException =
new THC_Library.CodeRenderException(THC_Library.CodeRenderException.REPEAT_SCAN, lastTime.ToString("yyyy/MM/dd HH:mm"));
codeException.AdditionalMessage = lastTime.ToString("yyyy/MM/dd HH:mm"); //string.Format("上次掃描時間 <br/>{0}", lastTime.ToString("MM/dd HH:mm:ss"));
throw codeException;
}
}
dataReader.Close();
if (!bKeyExist)
{
//掃描的 code 不再發行裡面
throw new THC_Library.CodeRenderException(THC_Library.CodeRenderException.INVAILD_CODE, "無效的發碼");
}
if (!bWin)
{
//取得地區溫度與天氣代碼
Int16 iTemp = 15;
int iWeather = 3200;
if (city.Length > 0)
{
strSQL = "select WH002,WH003 from weather where WH001 like @WH001 + '%'";
paraList.Clear();
sqlParam = new SqlParameter("@WH001", SqlDbType.NVarChar);
sqlParam.Value = city;
paraList.Add(sqlParam);
dataReader = dbCtl.GetReader(strSQL, paraList);
if (dataReader.Read())
{
if (!Int16.TryParse(dataReader["WH002"].ToString(), out iTemp))
{
iTemp = 15;
}
if (!int.TryParse(dataReader["WH003"].ToString(), out iWeather))
{
iWeather = 3200;
}
}
dataReader.Close();
}
else
{
strSQL = "select WH002,WH003 from weather where WH001='平均'";
paraList.Clear();
dataReader = dbCtl.GetReader(strSQL, paraList);
if (dataReader.Read())
{
if (!Int16.TryParse(dataReader["WH002"].ToString(), out iTemp))
{
iTemp = 15;
}
}
dataReader.Close();
}
dbCtl.BeginTransaction();
//未中獎 log 紀錄
strSQL = "insert into event_user_records (EUR002,EUR003,EUR004,EUR005,EUR006,EUR007,EUR008,EUR009,EUR010,EUR011,EUR012) values " +
"(@EUR002,@EUR003,@EUR004,@EUR005,@EUR006,@EUR007,@EUR008,@EUR009,@EUR010,@EUR011,@EUR012);";
paraList.Clear();
sqlParam = new SqlParameter("@EUR002", SqlDbType.Int);
sqlParam.Value = event_no;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@EUR003", SqlDbType.VarChar);
sqlParam.Value = code;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@EUR004", SqlDbType.DateTime);
sqlParam.Value = datNow;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@EUR005", SqlDbType.VarChar);
sqlParam.Value = ml; //帳號
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@EUR006", SqlDbType.Char);
sqlParam.Value = age; //年紀
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@EUR007", SqlDbType.Char);
sqlParam.Value = gender; //性別
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@EUR008", SqlDbType.NVarChar);
sqlParam.Value = city; //地區
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@EUR009", SqlDbType.SmallInt);
sqlParam.Value = iTemp; //溫度
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@EUR010", SqlDbType.Int);
sqlParam.Value = iWeather; //天氣
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@EUR011", SqlDbType.Float);
sqlParam.Value = lat; //緯度
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@EUR012", SqlDbType.Float);
sqlParam.Value = lng; //經度
paraList.Add(sqlParam);
dbCtl.ExecuteScalar(strSQL, paraList);
//未中獎
strSQL = "update qr_record set QRC012=QRC012+1, QRC013=@QRC013 where QRC015=@QRC015;";
paraList.Clear();
sqlParam = new SqlParameter("@QRC013", SqlDbType.DateTime);
sqlParam.Value = datNow;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@QRC015", SqlDbType.VarChar);
sqlParam.Value = code;
paraList.Add(sqlParam);
dbCtl.ExecuteCommad(strSQL, paraList);
dbCtl.CommintTransaction();
jsonString = THC_Library.APPCURL.ScanRecord(event_no.ToString(), code,
datNow.ToString(), ml, age, gender, city, iTemp.ToString(), iWeather.ToString(),
lat, lng, rwdType, tk);
jsonResult = Newtonsoft.Json.JsonConvert.DeserializeObject(jsonString);
}
}
catch (THC_Library.CodeRenderException codeex)
{
dbCtl.RollBackTransaction();
error = new THC_Library.Error();
error.Number = codeex.Number;
error.ErrorMessage = codeex.AdditionalMessage;
}
catch (Exception ex)
{
dbCtl.RollBackTransaction();
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = "系統發生異常錯誤，請稍後再上線使用。";//ex.Message;
}
finally
{
dbCtl.Close();
}
return(bWin);
}
public int updateRewardInfoWithFile(string reward_key, string name, string memo, string vender, string img,
string win_desc, string vdate, string sms, string filepath, HttpPostedFileBase file, out THC_Library.Error error)
{
error = null;
int iAffrect = 0;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "";
if (file == null)
{
strSQL = "update activity_rewards set AEP005=@AEP005,AEP007=@AEP007,AEP009=@AEP009,AEP012=@AEP012,AEP013=@AEP013," +
"AEP014=@AEP014 where AEP001=@AEP001";
paraList.Add(new SqlParameter("@AEP005", name));
paraList.Add(new SqlParameter("@AEP007", memo));
paraList.Add(new SqlParameter("@AEP009", vender));
paraList.Add(new SqlParameter("@AEP012", vdate));
paraList.Add(new SqlParameter("@AEP013", sms));
paraList.Add(new SqlParameter("@AEP014", win_desc));
paraList.Add(new SqlParameter("@AEP001", reward_key));
}
else
{
strSQL = "update activity_rewards set AEP005=@AEP005,AEP007=@AEP007,AEP009=@AEP009,AEP011=@AEP011,AEP012=@AEP012,AEP013=@AEP013," +
"AEP014=@AEP014 where AEP001=@AEP001";
paraList.Add(new SqlParameter("@AEP005", name));
paraList.Add(new SqlParameter("@AEP007", memo));
paraList.Add(new SqlParameter("@AEP009", vender));
paraList.Add(new SqlParameter("@AEP011", file.FileName));
paraList.Add(new SqlParameter("@AEP012", vdate));
paraList.Add(new SqlParameter("@AEP013", sms));
paraList.Add(new SqlParameter("@AEP014", win_desc));
paraList.Add(new SqlParameter("@AEP001", reward_key));
}
DataBaseControl dbCtl = new DataBaseControl();
try
{
//var fileName = System.IO.Path.GetFileName(rwdFile.FileName);
//var fileExtension = System.IO.Path.GetExtension(rwdFile.FileName);
if (file != null)
{
var path = System.IO.Path.Combine(filepath, file.FileName);
file.SaveAs(path);
}
dbCtl.Open();
iAffrect = dbCtl.ExecuteCommad(strSQL, paraList);
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(iAffrect);
}
public bool done(string ac, string code, string tk, string ml, string city, string lat, string lng,
string coupnumber, string logkey, out int event_key, out THC_Library.Error error)
{
error = null;
event_key = -1;
IDataReader dataReader;
SqlParameter sqlParam;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select AE001,AE003 from activity_event where AE002=@AE002";
paraList.Add(new SqlParameter("@AE002", ac));
DataBaseControl dbCtl = new DataBaseControl();
DateTime datNow = DateTime.Now;
string eventName;
string mobil = "";
string gender = "";
string age = "";
bool bRightEvent = false;
try
{
dbCtl.Open();
dataReader = dbCtl.GetReader(strSQL, paraList);
if (dataReader.Read())
{
bRightEvent = true;
event_key = int.Parse(dataReader["AE001"].ToString());
eventName = dataReader["AE003"].ToString();
}
dataReader.Close();
if (!bRightEvent)
{
throw new THC_Library.CodeRenderException(THC_Library.CodeRenderException.INVAILD_ACTIVITY, "無效的活動");
}
//確認登入
bool bLoginChecked = false;
string jsonString = THC_Library.APPCURL.GetAccountInfo(ml, tk);
dynamic jsonResult = Newtonsoft.Json.JsonConvert.DeserializeObject(jsonString);
if (jsonResult.Number == 0)
{
//AccountInfo
bLoginChecked = true;
mobil = jsonResult.Mobil;
gender = jsonResult.Gender;
age = jsonResult.Age;
//addr = jsonResult.Address;
//iid = jsonResult.IId;
}
else
{
//Result
bLoginChecked = false;
}
if (!bLoginChecked)
{
THC_Library.CodeRenderException codeException =
new THC_Library.CodeRenderException(THC_Library.CodeRenderException.LOGIN_INVALID, "無效登入");
throw codeException;
}
//QRC009 char(1) 獎項型態
//QRC010 tinyint 獎項層級
//QRC011 nvarchar(20) 獎項名稱
//QRC012 int 掃描次數
//AEP003 char(1) 獎項型態
//AEP004 tinyint 獎項層級
//AEP005 nvarchar(20) 獎項名稱
//strSQL = "select QRC009,QRC011,QRC012,QRC013 from qr_record where QRC008=@QRC008 and QRC015=@QRC015";
strSQL = "select QRC008,QRC012,QRC013,AEP003,AEP004,AEP005,AEP011,AEP013,AEP014 " +
"from qr_record left join activity_rewards on QRC018=AEP001 " +
"where QRC008=@QRC008 and QRC015=@QRC015";
paraList.Clear();
paraList.Add(new SqlParameter("@QRC008", coupnumber));
paraList.Add(new SqlParameter("@QRC015", code));
dataReader = dbCtl.GetReader(strSQL, paraList);
string rwardType = "";
string rwardName = "";
string rwardImg = "";
string rwardEC = "";
string winDesc = "";
string SMSContent = "";
if (dataReader.Read())
{
rwardEC = dataReader["QRC008"].ToString();
rwardType = dataReader["AEP003"].ToString();
rwardImg = dataReader["AEP011"].ToString();
rwardName = dataReader["AEP005"].ToString();
SMSContent = dataReader["AEP013"].ToString();
winDesc = dataReader["AEP014"].ToString();
int iScanCounter = int.Parse(dataReader["QRC012"].ToString());
if (iScanCounter > 0)
{
DateTime lastTime;
DateTime.TryParse(dataReader["QRC013"].ToString(), out lastTime);
dataReader.Close();
THC_Library.CodeRenderException codeException =
new THC_Library.CodeRenderException(THC_Library.CodeRenderException.REPEAT_SCAN, lastTime.ToString("yyyy/MM/dd HH:mm"));
codeException.AdditionalMessage = lastTime.ToString("yyyy/MM/dd HH:mm");
throw codeException;
}
}
else
{
dataReader.Close();
THC_Library.CodeRenderException codeException =
new THC_Library.CodeRenderException(THC_Library.CodeRenderException.INVAILD_CODE, "無效的發碼");
throw codeException;
}
dataReader.Close();
//取得地區溫度與天氣代碼
Int16 iTemp = 15;
int iWeather = 3200;
if (city.Length > 0)
{
strSQL = "select WH002,WH003 from weather where WH001 like @WH001 + '%'";
paraList.Clear();
sqlParam = new SqlParameter("@WH001", SqlDbType.NVarChar);
sqlParam.Value = city;
paraList.Add(sqlParam);
dataReader = dbCtl.GetReader(strSQL, paraList);
if (dataReader.Read())
{
if (!Int16.TryParse(dataReader["WH002"].ToString(), out iTemp))
{
iTemp = 15;
}
if (!int.TryParse(dataReader["WH003"].ToString(), out iWeather))
{
iWeather = 3200;
}
}
dataReader.Close();
}
else
{
strSQL = "select WH002,WH003 from weather where WH001='平均'";
paraList.Clear();
dataReader = dbCtl.GetReader(strSQL, paraList);
if (dataReader.Read())
{
if (!Int16.TryParse(dataReader["WH002"].ToString(), out iTemp))
{
iTemp = 15;
}
}
dataReader.Close();
}
dbCtl.BeginTransaction();
strSQL = "insert into event_user_records (EUR002,EUR003,EUR004,EUR005,EUR006,EUR007,EUR008,EUR009,EUR010,EUR011,EUR012) values " +
"(@EUR002,@EUR003,@EUR004,@EUR005,@EUR006,@EUR007,@EUR008,@EUR009,@EUR010,@EUR011,@EUR012);" +
"SELECT CAST(scope_identity() AS int);";
paraList.Clear();
sqlParam = new SqlParameter("@EUR002", SqlDbType.Int);
sqlParam.Value = event_key;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@EUR003", SqlDbType.VarChar);
sqlParam.Value = code;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@EUR004", SqlDbType.DateTime);
sqlParam.Value = datNow;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@EUR005", SqlDbType.VarChar);
sqlParam.Value = ml; //帳號
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@EUR006", SqlDbType.Char);
sqlParam.Value = age; //年紀
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@EUR007", SqlDbType.Char);
sqlParam.Value = gender; //性別
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@EUR008", SqlDbType.NVarChar);
sqlParam.Value = city; //地區
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@EUR009", SqlDbType.SmallInt);
sqlParam.Value = iTemp; //溫度
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@EUR010", SqlDbType.Int);
sqlParam.Value = iWeather; //天氣
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@EUR011", SqlDbType.Float);
sqlParam.Value = lat; //緯度
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@EUR012", SqlDbType.Float);
sqlParam.Value = lng; //經度
paraList.Add(sqlParam);
object newId = dbCtl.ExecuteScalar(strSQL, paraList);
//中獎
strSQL = "update qr_record set QRC012=QRC012+1, QRC013=@QRC013,QRC014=@QRC014,QRC016=@QRC016 where QRC015=@QRC015;";
paraList.Clear();
sqlParam = new SqlParameter("@QRC013", SqlDbType.DateTime);
sqlParam.Value = datNow;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@QRC014", SqlDbType.Int);
sqlParam.Value = newId;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@QRC016", SqlDbType.VarChar);
sqlParam.Value = ml;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@QRC015", SqlDbType.VarChar);
sqlParam.Value = code;
paraList.Add(sqlParam);
dbCtl.ExecuteCommad(strSQL, paraList);
dbCtl.CommintTransaction();
if (rwardType == "0")
{
//虛擬獎品 簡訊發送
char line = Convert.ToChar(6);
SMSContent = SMSContent.Replace("%s%", coupnumber);
SMSContent = SMSContent.Replace(System.Environment.NewLine, Convert.ToChar(6).ToString());
bool bSMS_OK = THC_Library.SMSHelper.SendTo(ml, mobil, SMSContent);
}
jsonString = THC_Library.APPCURL.ScanRecord(event_key.ToString(), code,
datNow.ToString(), ml, age, gender, city, iTemp.ToString(), iWeather.ToString(),
lat, lng, rwardName, rwardEC, rwardType, winDesc, tk);
jsonResult = Newtonsoft.Json.JsonConvert.DeserializeObject(jsonString);
//if (jsonResult.Number != 0)
//{
//}
}
catch (THC_Library.CodeRenderException codeex)
{
error = new THC_Library.Error();
error.Number = codeex.Number;
error.ErrorMessage = codeex.AdditionalMessage;
}
catch (Exception ex)
{
//dbCtl.RollBackTransaction();
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.ToString(); //"系統發生異常錯誤，請紀錄您的中獎序號，並與客服人員聯絡，我們會盡訊處理這問題。";
}
finally
{
dbCtl.Close();
}
return(true);
}
public void checkActivityAndCode(string ac, string code, out THC_Library.Error error)
{
error = null;
DateTime datNow = DateTime.Now;
DateTime datNowDate = new DateTime(datNow.Year, datNow.Month, datNow.Day);
int eventKey;
string eventName = "";
DateTime startTime = DateTime.MaxValue;
DateTime endTime = DateTime.MinValue;
SqlParameter sqlParam;
IDataReader dataReader;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
DataBaseControl dbCtl = new DataBaseControl();
//paraList.Add(new SqlParameter("@EQCH002", event_key));
string strSQL = "select * from activity_event where AE002=@AE002";
paraList.Add(new SqlParameter("@AE002", ac));
try
{
bool bRightEvent = false;
bool bKeyExist = false;
dbCtl.Open();
dataReader = dbCtl.GetReader(strSQL, paraList);
if (dataReader.Read())
{
bRightEvent = true;
eventKey = int.Parse(dataReader["AE001"].ToString());
eventName = dataReader["AE003"].ToString();
startTime = Convert.ToDateTime(dataReader["AE005"]);
endTime = Convert.ToDateTime(dataReader["AE006"]);
}
dataReader.Close();
if (!bRightEvent)
{
throw new THC_Library.CodeRenderException(THC_Library.CodeRenderException.INVAILD_ACTIVITY, "無效的活動");
}
else
{
if (startTime.Subtract(datNowDate).TotalDays > 0)
{
//未開始
THC_Library.CodeRenderException codeException =
new THC_Library.CodeRenderException(THC_Library.CodeRenderException.ACTIVITY_NOT_START, "活動尚未開始");
codeException.AdditionalMessage = string.Format("{0} 活動期間 {1} - {2}", eventName, startTime, endTime);
throw codeException;
}
if (endTime.Subtract(datNowDate).TotalDays < 0)
{
//結束
THC_Library.CodeRenderException codeException = new THC_Library.CodeRenderException(THC_Library.CodeRenderException.ACTIVITY_FINISHED, "活動已結束");
codeException.AdditionalMessage = string.Format("{0} 活動期間 {1} - {2}", eventName, startTime, endTime);
throw codeException;
}
}
strSQL = "select * from qr_record where QRC015=@QRC015";
paraList.Clear();
paraList.Add(new SqlParameter("@QRC015", code));
dataReader = dbCtl.GetReader(strSQL, paraList);
if (dataReader.Read())
{
int iScanCounter = int.Parse(dataReader["QRC012"].ToString());
if (iScanCounter == 0)
{
bKeyExist = true;
}
else
{
DateTime lastTime;
DateTime.TryParse(dataReader["QRC013"].ToString(), out lastTime);
dataReader.Close();
THC_Library.CodeRenderException codeException =
new THC_Library.CodeRenderException(THC_Library.CodeRenderException.REPEAT_SCAN, lastTime.ToString("yyyy/MM/dd HH:mm"));
codeException.AdditionalMessage = lastTime.ToString("yyyy/MM/dd HH:mm");
throw codeException;
}
}
dataReader.Close();
if (!bKeyExist)
{
//掃描的 code 不再發行裡面
throw new THC_Library.CodeRenderException(THC_Library.CodeRenderException.INVAILD_CODE, "無效的發碼");
}
}
catch (THC_Library.CodeRenderException codeex)
{
error = new THC_Library.Error();
error.Number = codeex.Number;
error.ErrorMessage = codeex.AdditionalMessage;
}
catch (Exception ex)
{
dbCtl.RollBackTransaction();
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = "系統發生異常錯誤，請稍後再上線使用。";//ex.Message;
}
finally
{
dbCtl.Close();
}
}
public void updateRewardData(string act, string code, string ml, string tk, out THC_Library.Error error)
{
error = null;
SqlParameter sqlParam;
IDataReader dataReader;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
DateTime datNow = DateTime.Now;
DataBaseControl dbCtl = new DataBaseControl();
string strSQL;
try
{
dbCtl.Open();
bool bLoginChecked = false;
strSQL = "select * from consumer_member where CM002=@CM002 and CM016=@CM016";
paraList.Clear();
paraList.Add(new SqlParameter("@CM002", ml));
paraList.Add(new SqlParameter("@CM016", tk));
dataReader = dbCtl.GetReader(strSQL, paraList);
if (dataReader.Read())
{
bLoginChecked = true;
}
dataReader.Close();
if (!bLoginChecked)
{
THC_Library.CodeRenderException codeException =
new THC_Library.CodeRenderException(THC_Library.CodeRenderException.LOGIN_INVALID, "無效登入");
throw codeException;
}
strSQL = "insert into event_user_records (EUR002,EUR003,EUR004,EUR005,EUR006) values " +
"(@EUR002,@EUR003,@EUR004,@EUR005,@EUR006);SELECT CAST(scope_identity() AS int);";
dbCtl.BeginTransaction();
paraList.Clear();
sqlParam = new SqlParameter("@EUR002", SqlDbType.Int);
sqlParam.Value = "";// eventKey;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@EUR003", SqlDbType.VarChar);
sqlParam.Value = code;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@EUR004", SqlDbType.DateTime);
sqlParam.Value = datNow;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@EUR005", SqlDbType.VarChar);
sqlParam.Value = "";
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@EUR006", SqlDbType.VarChar);
sqlParam.Value = "";
paraList.Add(sqlParam);
object newId = dbCtl.ExecuteScalar(strSQL, paraList);
int iIdentityKey;
int.TryParse(newId.ToString(), out iIdentityKey);
////中獎
strSQL = "update qr_record set QRC012=QRC012+1, QRC013=@QRC013,QRC014=@QRC014,QRC016=@QRC016 where QRC015=@QRC015;";
paraList.Clear();
sqlParam = new SqlParameter("@QRC013", SqlDbType.DateTime);
sqlParam.Value = datNow;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@QRC014", SqlDbType.Int);
sqlParam.Value = newId;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@QRC016", SqlDbType.VarChar);
sqlParam.Value = ml;
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@QRC015", SqlDbType.VarChar);
sqlParam.Value = code;
paraList.Add(sqlParam);
dbCtl.ExecuteCommad(strSQL, paraList);
dbCtl.CommintTransaction();
}
catch (Exception ex)
{
dbCtl.RollBackTransaction();
error = new THC_Library.Error();
error.Number = 300;
error.ErrorMessage = "";
}
finally
{
dbCtl.Close();
}
//dbCtl.BeginTransaction();
//string strSQL = "insert into event_user_records (EUR002,EUR003,EUR004,EUR005,EUR006) values " +
// "(@EUR002,@EUR003,@EUR004,@EUR005,@EUR006);SELECT CAST(scope_identity() AS int);";
//paraList.Clear();
//sqlParam = new SqlParameter("@EUR002", SqlDbType.Int);
//sqlParam.Value = eventKey;
//paraList.Add(sqlParam);
//sqlParam = new SqlParameter("@EUR003", SqlDbType.VarChar);
//sqlParam.Value = code;
//paraList.Add(sqlParam);
//sqlParam = new SqlParameter("@EUR004", SqlDbType.DateTime);
//sqlParam.Value = datNow;
//paraList.Add(sqlParam);
//sqlParam = new SqlParameter("@EUR005", SqlDbType.VarChar);
//sqlParam.Value = "";
//paraList.Add(sqlParam);
//sqlParam = new SqlParameter("@EUR006", SqlDbType.VarChar);
//sqlParam.Value = "";
//paraList.Add(sqlParam);
//object newId = dbCtl.ExecuteScalar(strSQL, paraList);
//int.TryParse(newId.ToString(), out iIdentityKey);
////中獎
//strSQL = "update qr_record set QRC012=QRC012+1, QRC013=@QRC013,QRC014=@QRC014,QRC016=@QRC016 where QRC015=@QRC015;";
//paraList.Clear();
//sqlParam = new SqlParameter("@QRC013", SqlDbType.DateTime);
//sqlParam.Value = datNow;
//paraList.Add(sqlParam);
//sqlParam = new SqlParameter("@QRC014", SqlDbType.Int);
//sqlParam.Value = newId;
//paraList.Add(sqlParam);
//sqlParam = new SqlParameter("@QRC016", SqlDbType.VarChar);
//sqlParam.Value = ml;
//paraList.Add(sqlParam);
//sqlParam = new SqlParameter("@QRC015", SqlDbType.VarChar);
//sqlParam.Value = code;
//paraList.Add(sqlParam);
//dbCtl.ExecuteCommad(strSQL, paraList);
}
public DataTable enterVerify(string account, string access_code, out THC_Library.Error error)
{
error = null;
DataTable resultTable = null;
byte[] pwdBytes = System.Text.Encoding.Default.GetBytes(access_code);
System.Security.Cryptography.MD5 md5 = System.Security.Cryptography.MD5.Create();
pwdBytes = md5.ComputeHash(pwdBytes);
string strPwd = Convert.ToBase64String(pwdBytes);
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select AU001,AU003 from activity_user where AU001=@AU001";
paraList.Add(new SqlParameter("@AU001", account));
DataBaseControl dbCtl = new DataBaseControl();
try
{
dbCtl.Open();
IDataReader dataReader = dbCtl.GetReader(strSQL, paraList);
if (dataReader.Read())
{
string PWD = dataReader["AU003"].ToString();
dataReader.Close();
if (PWD != strPwd)
{
throw new Exception("請輸入正確的密碼");
}
}
else
{
dataReader.Close();
throw new Exception("請輸入正確的帳號");
}
strSQL = "update activity_user set AU004=@AU004 where AU001=@AU001";
paraList.Clear();
paraList.Add(new SqlParameter("@AU004", DateTime.Now));
paraList.Add(new SqlParameter("@AU001", account));
dbCtl.ExecuteCommad(strSQL, paraList);
if (account == "root.admin")
{
strSQL = "select AU001,AU002,AU004 from activity_user where AU001!='root.admin'";
paraList.Clear();
resultTable = dbCtl.GetDataTable(strSQL, paraList);
}
}
catch (Exception ex)
{
error = new THC_Library.Error();
error.Number = THC_Library.THCException.SYSTEM_ERROR;
error.ErrorMessage = ex.Message;
}
finally
{
dbCtl.Close();
}
return(resultTable);
}
public void localFaceBookAccount(string mail, string name, string gender, out Error error)
{
error = null;
bool bHasAccount = false;
SqlParameter sqlParam;
IDataReader dataReader;
IList <SqlParameter> paraList = new System.Collections.Generic.List <SqlParameter>();
string strSQL = "select CM002 from consumer_member where CM003=@CM003";
DataBaseControl dbCtl = new DataBaseControl();
try
{
sqlParam = new SqlParameter("@CM003", mail);
paraList.Add(sqlParam);
dbCtl.Open();
dataReader = dbCtl.GetReader(strSQL, paraList);
if (dataReader.Read())
{
bHasAccount = true;
}
dataReader.Close();
if (bHasAccount)
{
}
else
{
//以FB註冊會員資料
strSQL = "insert into consumer_member (CM002,CM003,CM010) values " +
"(@CM002,@CM007,@CM010,@CM011);";
sqlParam = new SqlParameter("@CM002", mail);
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@CM003", mail);
paraList.Add(sqlParam);
sqlParam = new SqlParameter("@CM010", SqlDbType.Char);
if (gender == null)
{
sqlParam.Value = "0";
}
else
{
sqlParam.Value = gender;
}
paraList.Add(sqlParam);
dbCtl.Open();
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 = sqlEx.ToString(); //"系統錯誤";
}
}
catch (Exception ex)
{
error = new Error();
error.Number = 100;
error.ErrorMessage = ex.ToString();// "系統錯誤";
}
finally
{
dbCtl.Close();
}
}
