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 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 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 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 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 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 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 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();
}
}
/// <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 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 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);
}