513 lines
17 KiB
C#
513 lines
17 KiB
C#
//
|
|
// 這個檔案是 SignUp 資料表的資料存取類別 檔案二
|
|
// 您可以修改這個檔案，加入您自訂的屬性與方法
|
|
//
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.ComponentModel;
|
|
using System.Data;
|
|
using System.Data.Common;
|
|
using System.Data.SqlClient;
|
|
using System.Linq;
|
|
using System.Security.Cryptography;
|
|
using System.Text;
|
|
using WebLib;
|
|
|
|
namespace 報到系統
|
|
{
|
|
///<summary>
|
|
/// SignUp 資料存取類別
|
|
///</summary>
|
|
public partial class DAC_SignUp : WebLib.DAC
|
|
{
|
|
#region 自動產生的資料存取方法
|
|
///<summary>
|
|
/// InsertOne 之前會執行此方法
|
|
/// 在這裡可以做一些檢查或是自動編號等動作
|
|
/// 回傳 false 會打斷 InsertOne 的執行
|
|
///</summary>
|
|
protected virtual bool BeforeInsertOne(SignUpItem value)
|
|
{
|
|
if (string.IsNullOrEmpty(value.SignUpType))
|
|
value.SignUpType = "現場";
|
|
|
|
// 生成 QRCode
|
|
value.QRCode = GenQRCodeText(value);
|
|
|
|
// 自動生成序號 - 如果 SeqNo 為 0 或未設定，則自動生成
|
|
if (value.SeqNo <= 0)
|
|
{
|
|
// 查詢該活動的最大序號
|
|
SignUpList existingList = this.Select(
|
|
noInputReturnAll: true,
|
|
CourseID: value.CourseID,
|
|
SeqNo: null,
|
|
Name: null,
|
|
Mobile: null,
|
|
QRCode: null,
|
|
orderBy: "SeqNo DESC"
|
|
);
|
|
|
|
int maxSeqNo = 0;
|
|
if (existingList != null && existingList.Count > 0)
|
|
{
|
|
maxSeqNo = existingList[0].SeqNo;
|
|
}
|
|
|
|
value.SeqNo = maxSeqNo + 1; // 設定新序號
|
|
}
|
|
|
|
return true;
|
|
}
|
|
|
|
///<summary>
|
|
/// InsertOne 之後會執行此方法
|
|
///</summary>
|
|
protected virtual void AfterInsertOne(SignUpItem value)
|
|
{
|
|
}
|
|
|
|
///<summary>
|
|
/// InsertOne 發生錯誤時會執行此方法
|
|
///</summary>
|
|
///<param name="handled">錯誤是否已經處理妥當</param>
|
|
protected virtual void OnInsertOneError(Exception error, out bool handled)
|
|
{
|
|
handled = false;
|
|
}
|
|
///<summary>
|
|
/// UpdateOne 之前會執行此方法
|
|
/// 在這裡可以做一些檢查
|
|
/// 回傳 false 會打斷 UpdateOne 的執行
|
|
///</summary>
|
|
protected virtual bool BeforeUpdateOne(SignUpItem value)
|
|
{
|
|
if (string.IsNullOrEmpty(value.SignUpType))
|
|
value.SignUpType = "現場";
|
|
|
|
// 補上 QRCode
|
|
if (string.IsNullOrEmpty(value.QRCode))
|
|
{
|
|
value.QRCode = GenQRCodeText(value);
|
|
}
|
|
return true;
|
|
}
|
|
|
|
///<summary>
|
|
/// UpdateOne 之前會執行此方法
|
|
/// 在這裡可以做一些檢查
|
|
/// 回傳 false 會打斷 UpdateOne 的執行
|
|
///</summary>
|
|
protected virtual bool BeforeUpdateOne(SignUpItem value, SignUpItem original_value)
|
|
{
|
|
return true;
|
|
}
|
|
|
|
///<summary>
|
|
/// UpdateOne 之後會執行此方法
|
|
///</summary>
|
|
protected virtual void AfterUpdateOne(SignUpItem value)
|
|
{
|
|
}
|
|
|
|
///<summary>
|
|
/// UpdateOne 之後會執行此方法
|
|
///</summary>
|
|
protected virtual void AfterUpdateOne(SignUpItem value, SignUpItem original_value)
|
|
{
|
|
}
|
|
|
|
///<summary>
|
|
/// UpdateOne 發生錯誤時會執行此方法
|
|
///</summary>
|
|
///<param name="handled">錯誤是否已經處理妥當</param>
|
|
protected virtual void OnUpdateOneError(Exception error, out bool handled)
|
|
{
|
|
handled = false;
|
|
}
|
|
///<summary>
|
|
/// DeleteOne 之前會執行此方法
|
|
/// 在這裡可以做一些檢查
|
|
/// 回傳 false 會打斷 DeleteOne 的執行
|
|
///</summary>
|
|
protected virtual bool BeforeDeleteOne(SignUpItem value)
|
|
{
|
|
return true;
|
|
}
|
|
|
|
///<summary>
|
|
/// DeleteOne 之後會執行此方法
|
|
///</summary>
|
|
protected virtual void AfterDeleteOne(SignUpItem value)
|
|
{
|
|
}
|
|
|
|
///<summary>
|
|
/// DeleteOne 之前會執行此方法
|
|
/// 在這裡可以做一些檢查
|
|
/// 回傳 false 會打斷 DeleteOne 的執行
|
|
///</summary>
|
|
protected virtual bool BeforeDeleteOne(int ID, int DB_APPNO)
|
|
{
|
|
return true;
|
|
}
|
|
|
|
///<summary>
|
|
/// DeleteOne 之後會執行此方法
|
|
///</summary>
|
|
protected virtual void AfterDeleteOne(int ID, int DB_APPNO)
|
|
{
|
|
}
|
|
|
|
///<summary>
|
|
/// DeleteOne 發生錯誤時會執行此方法
|
|
///</summary>
|
|
///<param name="handled">錯誤是否已經處理妥當</param>
|
|
protected virtual void OnDeleteOneError(Exception error, out bool handled)
|
|
{
|
|
handled = false;
|
|
}
|
|
#endregion
|
|
|
|
/// <summary>
|
|
/// 生成 QRCode 雜湊值 - 基於 CourseID、Name、Mobile 的 MD5 雜湊
|
|
/// </summary>
|
|
/// <param name="value"></param>
|
|
/// <returns></returns>
|
|
public static string GenQRCodeText(SignUpItem value)
|
|
{
|
|
// 生成 QRCode 雜湊值 - 基於 CourseID、Name、Mobile 的 MD5 雜湊
|
|
if (value != null && string.IsNullOrEmpty(value.QRCode))
|
|
{
|
|
string input = value.CourseID.ToString() + "|" + (value.Name ?? "").Trim() + "|" + (value.Mobile ?? "").Trim();
|
|
using (MD5 md5 = MD5.Create())
|
|
{
|
|
byte[] inputBytes = System.Text.Encoding.UTF8.GetBytes(input);
|
|
byte[] hashBytes = md5.ComputeHash(inputBytes);
|
|
return System.Convert.ToBase64String(hashBytes);
|
|
}
|
|
}
|
|
return string.Empty;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 按 QRCode 查詢單筆記錄
|
|
/// </summary>
|
|
public SignUpList SelectByQRCode(string qrCode)
|
|
{
|
|
SignUpList result = new SignUpList();
|
|
IDataList list = result as IDataList;
|
|
DbCommand cmdSelect = NewCommand();
|
|
cmdSelect.CommandText = string.Format("select {0} from [SignUp] where 1=1 and QRCode = @QRCode", _allFields);
|
|
AddParam(cmdSelect, "QRCode", qrCode);
|
|
Select(cmdSelect, ref list);
|
|
return result;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 按 CourseID、Name、Mobile 查詢單筆記錄
|
|
/// </summary>
|
|
public SignUpList SelectByCourseNameMobile(int courseID, string name, string mobile)
|
|
{
|
|
SignUpList result = new SignUpList();
|
|
IDataList list = result as IDataList;
|
|
DbCommand cmdSelect = NewCommand();
|
|
cmdSelect.CommandText = string.Format("select {0} from [SignUp] where 1=1 and CourseID = @CourseID and Name = @Name and Mobile = @Mobile", _allFields);
|
|
AddParam(cmdSelect, "CourseID", courseID);
|
|
AddParam(cmdSelect, "Name", name);
|
|
AddParam(cmdSelect, "Mobile", mobile);
|
|
Select(cmdSelect, ref list);
|
|
return result;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 依照 courseId 刪除參加人員
|
|
/// </summary>
|
|
/// <param name="courseId"></param>
|
|
/// <returns></returns>
|
|
public int DeleteByCourseID(int courseId)
|
|
{
|
|
DbCommand cmdDelete = NewCommand(string.Format("delete from {0} where CourseID=@CourseID", _tableName));
|
|
AddParam(cmdDelete, "CourseID", courseId);
|
|
return ExecuteNonQuery(cmdDelete);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 進階搜尋參加人員（包含序號、身分別、身分證字號、餐點、電子郵件等條件）
|
|
/// </summary>
|
|
public SignUpList SelectPageAdvanced(
|
|
bool noInputReturnAll,
|
|
int startRowIndex,
|
|
int maximumRows,
|
|
int? CourseID,
|
|
int? SeqNo,
|
|
string Name,
|
|
string Mobile,
|
|
string IDType,
|
|
string IDNumber,
|
|
bool IDNumberEmpty,
|
|
string MealType,
|
|
string Email,
|
|
bool EmailEmpty,
|
|
string orderBy)
|
|
{
|
|
SignUpList result = new SignUpList();
|
|
IDataList list = result as IDataList;
|
|
|
|
StringBuilder sqlWhere = new StringBuilder();
|
|
DbCommand cmd = NewCommand();
|
|
|
|
if (!noInputReturnAll)
|
|
{
|
|
sqlWhere.Append(" where 1=1");
|
|
|
|
if (CourseID.HasValue && CourseID.Value > 0)
|
|
{
|
|
sqlWhere.Append(" and [CourseID] = @CourseID");
|
|
AddParam(cmd, "CourseID", CourseID.Value);
|
|
}
|
|
|
|
if (SeqNo.HasValue && SeqNo.Value > 0)
|
|
{
|
|
sqlWhere.Append(" and [SeqNo] = @SeqNo");
|
|
AddParam(cmd, "SeqNo", SeqNo.Value);
|
|
}
|
|
|
|
if (!string.IsNullOrEmpty(Name))
|
|
{
|
|
sqlWhere.Append(" and [Name] like @Name");
|
|
AddParam(cmd, "Name", "%" + Name + "%");
|
|
}
|
|
|
|
if (!string.IsNullOrEmpty(Mobile))
|
|
{
|
|
sqlWhere.Append(" and [Mobile] like @Mobile");
|
|
AddParam(cmd, "Mobile", "%" + Mobile + "%");
|
|
}
|
|
|
|
// 身分別過濾
|
|
if (!string.IsNullOrEmpty(IDType))
|
|
{
|
|
if (IDType == "___EMPTY___")
|
|
{
|
|
// 查詢身分別為空的記錄
|
|
sqlWhere.Append(" and ([IDType] is null or [IDType] = '')");
|
|
}
|
|
else
|
|
{
|
|
// 查詢特定身分別
|
|
sqlWhere.Append(" and [IDType] = @IDType");
|
|
AddParam(cmd, "IDType", IDType);
|
|
}
|
|
}
|
|
|
|
// 身分證字號過濾
|
|
if (IDNumberEmpty)
|
|
{
|
|
// 查詢未輸入的記錄
|
|
sqlWhere.Append(" and ([IDNumber] is null or [IDNumber] = '')");
|
|
}
|
|
else if (!string.IsNullOrEmpty(IDNumber))
|
|
{
|
|
// 查詢特定的身分證字號
|
|
sqlWhere.Append(" and [IDNumber] = @IDNumber");
|
|
AddParam(cmd, "IDNumber", IDNumber);
|
|
}
|
|
|
|
// 餐點過濾
|
|
if (!string.IsNullOrEmpty(MealType))
|
|
{
|
|
if (MealType == "___EMPTY___")
|
|
{
|
|
// 查詢餐點為空的記錄
|
|
sqlWhere.Append(" and ([MealType] is null or [MealType] = '')");
|
|
}
|
|
else
|
|
{
|
|
// 查詢特定餐點
|
|
sqlWhere.Append(" and [MealType] = @MealType");
|
|
AddParam(cmd, "MealType", MealType);
|
|
}
|
|
}
|
|
|
|
// 電子郵件過濾
|
|
if (EmailEmpty)
|
|
{
|
|
// 查詢未輸入的記錄
|
|
sqlWhere.Append(" and ([Email] is null or [Email] = '')");
|
|
}
|
|
else if (!string.IsNullOrEmpty(Email))
|
|
{
|
|
// 查詢特定的電子郵件
|
|
sqlWhere.Append(" and [Email] like @Email");
|
|
AddParam(cmd, "Email", "%" + Email + "%");
|
|
}
|
|
}
|
|
|
|
string sqlCmd = string.Format("select {0} from {1} {2} {3} offset {4} rows fetch next {5} rows only",
|
|
_allFields,
|
|
_tableName,
|
|
sqlWhere.ToString(),
|
|
!string.IsNullOrEmpty(orderBy) ? "order by " + orderBy : "",
|
|
startRowIndex,
|
|
maximumRows);
|
|
|
|
cmd.CommandText = sqlCmd;
|
|
Select(cmd, ref list);
|
|
|
|
return result;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 計算進階搜尋的結果筆數
|
|
/// </summary>
|
|
public int SelectCountAdvanced(
|
|
bool noInputReturnAll,
|
|
int? CourseID,
|
|
int? SeqNo,
|
|
string Name,
|
|
string Mobile,
|
|
string IDType,
|
|
string IDNumber,
|
|
bool IDNumberEmpty,
|
|
string MealType,
|
|
string Email,
|
|
bool EmailEmpty)
|
|
{
|
|
StringBuilder sqlWhere = new StringBuilder();
|
|
DbCommand cmd = NewCommand();
|
|
|
|
if (!noInputReturnAll)
|
|
{
|
|
sqlWhere.Append(" where 1=1");
|
|
|
|
if (CourseID.HasValue && CourseID.Value > 0)
|
|
{
|
|
sqlWhere.Append(" and [CourseID] = @CourseID");
|
|
AddParam(cmd, "CourseID", CourseID.Value);
|
|
}
|
|
|
|
if (SeqNo.HasValue && SeqNo.Value > 0)
|
|
{
|
|
sqlWhere.Append(" and [SeqNo] = @SeqNo");
|
|
AddParam(cmd, "SeqNo", SeqNo.Value);
|
|
}
|
|
|
|
if (!string.IsNullOrEmpty(Name))
|
|
{
|
|
sqlWhere.Append(" and [Name] like @Name");
|
|
AddParam(cmd, "Name", "%" + Name + "%");
|
|
}
|
|
|
|
if (!string.IsNullOrEmpty(Mobile))
|
|
{
|
|
sqlWhere.Append(" and [Mobile] like @Mobile");
|
|
AddParam(cmd, "Mobile", "%" + Mobile + "%");
|
|
}
|
|
|
|
// 身分別過濾
|
|
if (!string.IsNullOrEmpty(IDType))
|
|
{
|
|
if (IDType == "___EMPTY___")
|
|
{
|
|
// 查詢身分別為空的記錄
|
|
sqlWhere.Append(" and ([IDType] is null or [IDType] = '')");
|
|
}
|
|
else
|
|
{
|
|
// 查詢特定身分別
|
|
sqlWhere.Append(" and [IDType] = @IDType");
|
|
AddParam(cmd, "IDType", IDType);
|
|
}
|
|
}
|
|
|
|
// 身分證字號過濾
|
|
if (IDNumberEmpty)
|
|
{
|
|
// 查詢未輸入的記錄
|
|
sqlWhere.Append(" and ([IDNumber] is null or [IDNumber] = '')");
|
|
}
|
|
else if (!string.IsNullOrEmpty(IDNumber))
|
|
{
|
|
// 查詢特定的身分證字號
|
|
sqlWhere.Append(" and [IDNumber] = @IDNumber");
|
|
AddParam(cmd, "IDNumber", IDNumber);
|
|
}
|
|
|
|
// 餐點過濾
|
|
if (!string.IsNullOrEmpty(MealType))
|
|
{
|
|
if (MealType == "___EMPTY___")
|
|
{
|
|
// 查詢餐點為空的記錄
|
|
sqlWhere.Append(" and ([MealType] is null or [MealType] = '')");
|
|
}
|
|
else
|
|
{
|
|
// 查詢特定餐點
|
|
sqlWhere.Append(" and [MealType] = @MealType");
|
|
AddParam(cmd, "MealType", MealType);
|
|
}
|
|
}
|
|
|
|
// 電子郵件過濾
|
|
if (EmailEmpty)
|
|
{
|
|
// 查詢未輸入的記錄
|
|
sqlWhere.Append(" and ([Email] is null or [Email] = '')");
|
|
}
|
|
else if (!string.IsNullOrEmpty(Email))
|
|
{
|
|
// 查詢特定的電子郵件
|
|
sqlWhere.Append(" and [Email] like @Email");
|
|
AddParam(cmd, "Email", "%" + Email + "%");
|
|
}
|
|
}
|
|
|
|
string sqlCmd = string.Format("select count(*) from {0} {1}", _tableName, sqlWhere.ToString());
|
|
cmd.CommandText = sqlCmd;
|
|
|
|
ExecuteScalar(cmd, out int result);
|
|
return result;
|
|
}
|
|
|
|
// 有另外加上去的屬性及方法，請加在這裡
|
|
}
|
|
|
|
///<summary>
|
|
///SignUp 資料集合類別
|
|
///</summary>
|
|
public partial class SignUpList : List<SignUpItem>
|
|
{
|
|
// 有另外加上去的屬性及方法，請加在這裡
|
|
}
|
|
|
|
///<summary>
|
|
///SignUp 資料紀錄類別
|
|
///</summary>
|
|
public partial class SignUpItem
|
|
{
|
|
// 有另外加上去的屬性及方法，請加在這裡
|
|
|
|
/// <summary>
|
|
/// 簡訊發送狀況
|
|
/// </summary>
|
|
public string MessageSending { get; set; }
|
|
|
|
/// <summary>
|
|
/// 出席：有簽到時間
|
|
/// </summary>
|
|
public bool IsPresent { get { return !string.IsNullOrEmpty(CheckInTime); } }
|
|
|
|
/// <summary>
|
|
/// 異常：沒有簽到時間只有簽退時間
|
|
/// </summary>
|
|
public bool IsException { get { return string.IsNullOrEmpty(CheckInTime) && !string.IsNullOrEmpty(CheckOutTime); } }
|
|
|
|
/// <summary>
|
|
/// 現場報名
|
|
/// </summary>
|
|
public bool IsOnSite { get { return string.Compare(SignUpType, "現場") == 0; } }
|
|
}
|
|
}
|