/// <summary>
/// 新增使用者帳號
/// </summary>
/// <param name="account">欲新增的帳號</param>
/// <returns>新增成功或失敗</returns>
public string InsertMember(string account)
{
try
{
string msg = this.ValidInsertMember(account);
if (!msg.Equals(string.Empty))
{
return(msg);
}
account = TalentCommon.GetInstance().AddMailFormat(account);
string[] splitAccount = account.Split('@');
string password = Common.GetInstance().PasswordEncryption(splitAccount[0].ToLower());
string insert = @"insert into Member (Account,Password,States) values (@account,@password,N'啟用')";
using (SqlCommand cmd = new SqlCommand(insert, ScConnection, StTransaction))
{
cmd.Parameters.Add("@account", SqlDbType.VarChar).Value = account;
cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = password;
cmd.ExecuteNonQuery();
this.CommitTransaction();
return("新增成功");
}
}
catch (Exception ex)
{
LogInfo.WriteErrorInfo(ex);
this.RollbackTransaction();
return("新增失敗");
}
}
/// <summary>
/// 驗證欲新增的帳號是否已存在
/// </summary>
/// <param name="account">欲新增的帳號</param>
/// <returns>空值代表不存在</returns>
public string ValidInsertMember(string account)
{
if (string.IsNullOrEmpty(account))
{
return("帳號不可為空值");
}
int count = 0;
account = TalentCommon.GetInstance().AddMailFormat(account);
string msg = TalentValid.GetInstance().ValidIsCompanyMail(account);
if (!msg.Equals(string.Empty))
{
return(msg);
}
string select = @"select count(1) from Member where account=@account";
try
{
using (SqlCommand cmd = new SqlCommand(select, ScConnection))
{
cmd.Parameters.Add("@account", SqlDbType.VarChar).Value = account;
using (SqlDataReader re = cmd.ExecuteReader())
{
if (re.Read())
{
count = int.Parse(re[0].ToString());
}
if (count > 0)
{
return("帳號已存在");
}
else
{
return(string.Empty);
}
}
}
}
catch (Exception ex)
{
LogInfo.WriteErrorInfo(ex);
return("發生錯誤");
}
finally
{
this.CloseDatabaseConnection();
}
}
/// <summary>
/// 登入功能
/// </summary>
/// <param name="account">帳號</param>
/// <param name="password">密碼</param>
/// <returns>登入成功回傳帳號，登入失敗回傳"登入失敗"，該帳號停用中回傳"該帳號停用中"</returns>
public string SignIn(string account, string password)
{
ErrorMessage = string.Empty;
string msg = "登入失敗";
account = TalentCommon.GetInstance().AddMailFormat(account);
password = Common.GetInstance().PasswordEncryption(password.ToLower());
string select = @"select Account,States from Member where Account=@account and Password = @password";
DataTable dt = new DataTable();
try
{
using (SqlDataAdapter da = new SqlDataAdapter(select, ScConnection))
{
da.SelectCommand.Parameters.Add("@account", SqlDbType.NVarChar).Value = account;
da.SelectCommand.Parameters.Add("@password", SqlDbType.NVarChar).Value = password;
da.Fill(dt);
if (dt.Rows.Count == 0)
{
return(msg);
}
}
if (TalentValid.GetInstance().ValidMemberStates(dt.Rows[0][1].ToString()))
{
return(dt.Rows[0][0].ToString());
}
else
{
return("該帳號停用中");
}
}
catch (Exception ex)
{
LogInfo.WriteErrorInfo(ex);
ErrorMessage = "資料庫發生錯誤";
return(msg);
}
finally
{
this.CloseDatabaseConnection();
}
}
/// <summary>
/// 根據地點，技能，合作模式，聯繫狀態，最後編輯時間，查詢符合的Id
/// </summary>
/// <param name="places">地點，多筆請用","隔開</param>
/// <param name="expertises">技能，多筆請用","隔開</param>
/// <param name="cooperationMode">合作模式</param>
/// <param name="states">聯繫狀態</param>
/// <param name="startEditDate">起始日期，日期格式</param>
/// <param name="endEditDate">結束日期，日期格式</param>
/// <returns>回傳符合條件的Id</returns>
public List <string> SelectIdByContact(string places, string expertises, string cooperationMode, string states, string startEditDate, string endEditDate)
{
ErrorMessage = string.Empty;
DataTable dt = new DataTable();
List <string> idList = new List <string>();
try
{
if (Valid.GetInstance().ValidDateRange(startEditDate, endEditDate) != string.Empty)
{
MessageBox.Show("最後編輯日之日期格式或者是日期區間不正確", "錯誤訊息");
return(new List <string>());
}
string[] place = places.Split(',');
string[] expertise = expertises.Split(',');
string select = @"select Contact_Id from Contact_Info where ISNULL(Status,'NA') = ISNULL(ISNULL(@status,Status),'NA') and
UpdateTime >= ISNULL(@startEditDate, UpdateTime) and
UpdateTime <= ISNULL(@endEditDate, UpdateTime) and
ISNULL(Cooperation_Mode,'NA') = ISNULL(ISNULL(@CooperationMode,Cooperation_Mode),'NA')";
using (SqlDataAdapter da = new SqlDataAdapter(select, ScConnection))
{
////如果合作模式為"全職"or"合約"，則值為"皆可"也要被查詢出來
if (cooperationMode.Equals("全職") || cooperationMode.Equals("合約"))
{
da.SelectCommand.CommandText += @" or ISNULL(Cooperation_Mode,'NA') = ISNULL(ISNULL(@CooperationMode1,Cooperation_Mode),'NA')";
da.SelectCommand.Parameters.Add("@CooperationMode1", SqlDbType.NChar).Value = Common.GetInstance().ValueIsNullOrEmpty("皆可");
}
////多筆地點
for (int i = 0; i < place.Length; i++)
{
if (i == 0)
{
da.SelectCommand.CommandText += @" and ISNULL(Place,'NA') LIKE ISNULL(ISNULL(@place" + (i + 1) + ", Place),'NA')";
da.SelectCommand.Parameters.Add("@place" + (i + 1), SqlDbType.NVarChar).Value = Common.GetInstance().ValueIsNullOrEmpty("%" + place[i] + "%");
}
else
{
da.SelectCommand.CommandText += @" or ISNULL(Place,'NA') LIKE ISNULL(ISNULL(@place" + (i + 1) + ", Place),'NA')";
da.SelectCommand.Parameters.Add("@place" + (i + 1), SqlDbType.NVarChar).Value = Common.GetInstance().ValueIsNullOrEmpty("%" + place[i] + "%");
}
}
////多筆技能
for (int i = 0; i < expertise.Length; i++)
{
if (i == 0)
{
da.SelectCommand.CommandText += @" and ISNULL(Skill,'NA') Like ISNULL(ISNULL(@skill" + (i + 1) + ", Skill),'NA')";
da.SelectCommand.Parameters.Add("@skill" + (i + 1), SqlDbType.NVarChar).Value = Common.GetInstance().ValueIsNullOrEmpty("%" + expertise[i] + "%");
}
else
{
da.SelectCommand.CommandText += @" or ISNULL(Skill,'NA') Like ISNULL(ISNULL(@skill" + (i + 1) + ", Skill),'NA')";
da.SelectCommand.Parameters.Add("@skill" + (i + 1), SqlDbType.NVarChar).Value = Common.GetInstance().ValueIsNullOrEmpty("%" + expertise[i] + "%");
}
}
da.SelectCommand.Parameters.Add("@CooperationMode", SqlDbType.NChar).Value = TalentCommon.GetInstance().ValueIsAny(cooperationMode);
da.SelectCommand.Parameters.Add("@status", SqlDbType.NVarChar).Value = TalentCommon.GetInstance().ValueIsAny(states);
da.SelectCommand.Parameters.Add("@startEditDate", SqlDbType.DateTime).Value = Common.GetInstance().ValueIsNullOrEmpty(startEditDate);
da.SelectCommand.Parameters.Add("@endEditDate", SqlDbType.DateTime).Value = Common.GetInstance().ValueIsNullOrEmpty(endEditDate);
da.Fill(dt);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
idList.Add(dr[0].ToString());
}
}
return(idList);
}
}
catch (Exception ex)
{
LogInfo.WriteErrorInfo(ex);
ErrorMessage = "資料庫發生錯誤";
return(new List <string>());
}
finally
{
this.CloseDatabaseConnection();
}
}