/// <summary>
/// 下載附加檔案
/// </summary>
/// <param name="serverPath">伺服器端的檔案路徑</param>
/// <param name="clientPath">存檔的路徑</param>
/// <returns></returns>
public string DownloadFile(string serverPath, string clientPath)
{
string msg = Valid.GetInstance().ValidFilePath(serverPath);
if (msg != string.Empty)
{
return("不存在的路徑");
}
try
{
using (Stream stream = File.Open(serverPath, FileMode.Open))
{
using (FileStream fs = new FileStream(clientPath, FileMode.OpenOrCreate))
{
stream.CopyTo(fs);
fs.Flush();
}
}
return("下載成功");
}
catch (Exception ex)
{
LogInfo.WriteErrorInfo(ex);
return("下載失敗");
}
}
/// <summary>
/// 驗證聯繫狀況基本資料是否正確
/// </summary>
/// <param name="name">姓名不可為空</param>
/// <param name="code">可以有多個代碼，代碼都是唯一值</param>
/// <param name="sex">值為"男"or"女"</param>
/// <param name="mail">e-mail格式</param>
/// <param name="cellPhone">手機格式</param>
/// <param name="place"><地點</param>
/// <param name="skill">技能</param>
/// <param name="cooperationMode">合作模式值為"全職"or"合約"or"皆可"</param>
/// <param name="states">狀態值為"追蹤"or"保留"</param>
/// <returns>空值代表沒有錯誤</returns>
public string ValidContactSituationInfoData(string name, DataTable code, string sex, string mail, string cellPhone, string place, string skill, string cooperationMode, string states)
{
string msg = string.Empty;
string validMsg = string.Empty;
if (string.IsNullOrEmpty(name) && code.Rows.Count == 0)
{
msg += "姓名或代碼請至少填一個\n";
}
validMsg = Valid.GetInstance().ValidSex(sex);
if (!validMsg.Equals(string.Empty))
{
msg += validMsg + "\n";
}
validMsg = Valid.GetInstance().ValidMailFormat(mail);
if (!validMsg.Equals(string.Empty))
{
msg += validMsg + "\n";
}
validMsg = Valid.GetInstance().ValidCellPhoneFormat(cellPhone);
if (!validMsg.Equals(string.Empty))
{
msg += validMsg + "\n";
}
validMsg = this.ValidCooperationMode(cooperationMode);
if (!validMsg.Equals(string.Empty))
{
msg += validMsg + "\n";
}
validMsg = this.ValidStates(states);
if (!validMsg.Equals(string.Empty))
{
msg += validMsg + "\n";
}
validMsg = Talent.GetInstance().ValidCodeIsRepeat(code);
if (!validMsg.Equals(string.Empty))
{
msg += validMsg;
}
return(msg);
}
/// <summary>
/// 驗證面試基本資料是否正確
/// </summary>
/// <param name="vacancies">應徵職缺不可為空值</param>
/// <param name="name">姓名不可為空值</param>
/// <param name="married">值為"已婚"or"未婚"</param>
/// <param name="interviewDate">面試日期不可為空值</param>
/// <param name="sex">值為"男"or"女"</param>
/// <param name="mail">值為e-mail格式</param>
/// <param name="birthday">生日</param>
/// <param name="cellPhone">值為手機格式</param>
/// <param name="picture">值為照片路徑</param>
/// <returns>空值代表沒有錯誤</returns>
public string ValidInterviewInfoData(string vacancies, string name, string married, string interviewDate, string sex, string mail, string birthday, string cellPhone, string picture)
{
string msg = string.Empty;
string validMsg = string.Empty;
if (string.IsNullOrEmpty(vacancies))
{
msg += "應徵職缺不可為空值\n";
}
if (string.IsNullOrEmpty(name))
{
msg += "姓名不可為空值\n";
}
validMsg = Valid.GetInstance().ValidMarried(married);
if (validMsg != string.Empty)
{
msg += validMsg + "\n";
}
validMsg = Valid.GetInstance().ValidDateFormat(interviewDate);
if (validMsg != string.Empty)
{
msg += "面談日期為空值or格式不正確\n";
}
validMsg = Valid.GetInstance().ValidSex(sex);
if (validMsg != string.Empty)
{
msg += validMsg + "\n";
}
if (!string.IsNullOrEmpty(string.Empty))
{
validMsg = Valid.GetInstance().ValidDateFormat(birthday);
if (validMsg != string.Empty)
{
msg += "生日為格式不正確\n";
}
}
return(msg);
}
/// <summary>
/// 根據條件查詢符合的資料
/// </summary>
/// <param name="keyWords">關鍵字，如有多個關鍵字請用","隔開</param>
/// <param name="places">地點，多筆請用","隔開</param>
/// <param name="expertises">技能，多筆請用","隔開</param>
/// <param name="cooperationMode">合作模式</param>
/// <param name="states">聯繫狀態</param>
/// <param name="startEditDate">起始日期，日期格式</param>
/// <param name="endEditDate">結束日期，日期格式</param>
/// <param name="isInterview">是否已面談，值為"已面談","未面談","不限"</param>
/// <param name="interviewResult">面談結果，值為"錄用","不錄用","暫保留","不限"</param>
/// <param name="startInterviewDate">起始日期，日期格式</param>
/// <param name="endInterviewDate">結束日期，日期格式</param>
/// <returns></returns>
public DataTable SelectIdByFilter(string keyWords, string places, string expertises, string cooperationMode, string states, string startEditDate, string endEditDate, string isInterview, string interviewResult, string startInterviewDate, string endInterviewDate)
{
ErrorMessage = string.Empty;
DataTable dt = new DataTable();
string select = @"select Contact_Id,Name,Code_Id,CONVERT(varchar(100), Contact_Date, 111) Contact_Date,Contact_Status,Remarks,CONVERT(varchar(100), Interview_Date, 111) Interview_Date,CONVERT(varchar(100), UpdateTime, 120) UpdateTime from FilterTable where 1=1 ";
try
{
if (Valid.GetInstance().ValidDateRange(startEditDate, endEditDate) != string.Empty)
{
ErrorMessage = "最後編輯日之日期格式或者是日期區間不正確";
return(new DataTable());
}
if (Valid.GetInstance().ValidDateRange(startInterviewDate, endInterviewDate) != string.Empty)
{
ErrorMessage = "面談日期之日期格式或者是日期區間不正確";
return(new DataTable());
}
using (SqlDataAdapter da = new SqlDataAdapter(select, ScConnection))
{
this.CombinationWhereByContactFilter(places, expertises, cooperationMode, states, startEditDate, endEditDate, da);
this.CombinationWhereByInterviewFilter(isInterview, interviewResult, startInterviewDate, endInterviewDate, da);
this.CombinationWhereByKeyWord(keyWords, da);
da.SelectCommand.CommandText += " order by UpdateTime desc";
da.Fill(dt);
}
return(dt);
}
catch (Exception ex)
{
LogInfo.WriteErrorInfo(ex);
ErrorMessage = "資料庫發生錯誤";
return(new DataTable());
}
finally
{
this.CloseDatabaseConnection();
}
}
/// <summary>
/// 上傳照片
/// </summary>
/// <param name="path">本機端路徑</param>
/// <param name="interviewId">面談ID</param>
/// <returns></returns>
public string UpLoadImage(string path, string interviewId)
{
string msg = Valid.GetInstance().ValidFilePath(path);
if (msg != string.Empty)
{
return("不存在的路徑");
}
try
{
////取得副檔名
string extension = Path.GetExtension(path).ToLowerInvariant();
// 檢查 Server 上該資料夾是否存在，不存在就自動建立
string serverDir = @".\images";
if (Directory.Exists(serverDir) == false)
{
Directory.CreateDirectory(serverDir);
}
////將檔名以面談ID命名
string fileName = string.Format("{0}{1}", interviewId, extension);
////組合出完整路徑
string serverFilePath = Path.Combine(serverDir, fileName);
using (Stream stream = File.Open(path, FileMode.Open, FileAccess.Read))
{
using (FileStream fs = new FileStream(serverFilePath, FileMode.OpenOrCreate))
{
stream.CopyTo(fs);
fs.Flush();
}
}
return(serverFilePath);
}
catch (Exception ex)
{
LogInfo.WriteErrorInfo(ex);
return("上傳失敗");
}
}
/// <summary>
/// 驗證聯繫狀況資料的正確性
/// </summary>
/// <param name="validData">聯繫狀況資料</param>
/// <returns>空值代表沒有錯誤</returns>
public string ValidContactSituationData(DataTable validData)
{
string msg = string.Empty;
foreach (DataRow dr in validData.Rows)
{
msg = Valid.GetInstance().ValidDateFormat(dr["Contact_Date"].ToString().Trim());
if (!msg.Equals(string.Empty))
{
return(msg);
}
msg = this.ValidContactStatus(dr["Contact_Status"].ToString().Trim());
if (!msg.Equals(string.Empty))
{
return(msg);
}
}
return(msg);
}
/// <summary>
/// 匯入新版資料
/// </summary>
/// <param name="path"></param>
public List <ContactSituation> ImportNewTalent(string path)
{
ErrorMessage = string.Empty;
string msg = string.Empty;
List <string> checkCodeIsRepeat = new List <string>(); ////檢查Excel內部的代碼是否有重複
List <ContactSituation> contactSituationList = new List <ContactSituation>();
try
{
Workbook workbook = new Workbook();
workbook.LoadFromFile(path);
Worksheet sheet = workbook.Worksheets[0];
DataTable dt = sheet.ExportDataTable();
//if(!TalentValid.GetInstance().ValidIsNewTalentFormat(dt.Columns))
if (!TalentValid.GetInstance().ValidTalentFormat(dt.Columns, false))
{
ErrorMessage = "excel格式不符";
return(contactSituationList);
}
if (dt.Rows.Count == 0)
{
ErrorMessage = "空的excel";
return(contactSituationList);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
ContactSituation contactSituation = new ContactSituation();
ContactInfo contactInfo = new ContactInfo();
List <ContactStatus> contactStatusList = new List <ContactStatus>();
for (int j = 0; j < 29; j++)
{
////先不處理聯繫狀況
if (j == 3 || j == 4 || j == 5)
{
continue;
}
switch (dt.Columns[j].ToString().Trim())
{
case "姓名":
contactInfo.Name = dt.Rows[i].ItemArray[j].ToString().Trim();
break;
case "地點":
contactInfo.Place = dt.Rows[i].ItemArray[j].ToString().Trim();
break;
case "1111/104代碼":
////檢查Excel內部的代碼是否有重複
string[] codeList = dt.Rows[i].ItemArray[j].ToString().Trim().Split('\n');
foreach (string code in codeList)
{
if (checkCodeIsRepeat.Contains(code))
{
contactSituationList.Clear();
ErrorMessage = "第" + (i + 1) + "行" + code + "重複\n請檢查Excel";
return(contactSituationList);
}
checkCodeIsRepeat.Add(code);
}
contactSituation.Code = dt.Rows[i].ItemArray[j].ToString().Trim();
break;
case "JAVA":
if (!string.IsNullOrEmpty(dt.Rows[i].ItemArray[j].ToString().Trim()))
{
contactInfo.Skill += dt.Rows[i].ItemArray[j].ToString().Trim() + ",";
}
break;
case "JSP":
if (!string.IsNullOrEmpty(dt.Rows[i].ItemArray[j].ToString().Trim()))
{
contactInfo.Skill += dt.Rows[i].ItemArray[j].ToString().Trim() + ",";
}
break;
case "Android APP":
if (!string.IsNullOrEmpty(dt.Rows[i].ItemArray[j].ToString().Trim()))
{
contactInfo.Skill += dt.Rows[i].ItemArray[j].ToString().Trim() + ",";
}
break;
case "ASP":
if (!string.IsNullOrEmpty(dt.Rows[i].ItemArray[j].ToString().Trim()))
{
contactInfo.Skill += dt.Rows[i].ItemArray[j].ToString().Trim() + ",";
}
break;
case "C/C++":
if (!string.IsNullOrEmpty(dt.Rows[i].ItemArray[j].ToString().Trim()))
{
contactInfo.Skill += dt.Rows[i].ItemArray[j].ToString().Trim() + ",";
}
break;
case "C#":
if (!string.IsNullOrEmpty(dt.Rows[i].ItemArray[j].ToString().Trim()))
{
contactInfo.Skill += dt.Rows[i].ItemArray[j].ToString().Trim() + ",";
}
break;
case "ASP.NET":
if (!string.IsNullOrEmpty(dt.Rows[i].ItemArray[j].ToString().Trim()))
{
contactInfo.Skill += dt.Rows[i].ItemArray[j].ToString().Trim() + ",";
}
break;
case "VB.NET":
if (!string.IsNullOrEmpty(dt.Rows[i].ItemArray[j].ToString().Trim()))
{
contactInfo.Skill += dt.Rows[i].ItemArray[j].ToString().Trim() + ",";
}
break;
case "VB6":
if (!string.IsNullOrEmpty(dt.Rows[i].ItemArray[j].ToString().Trim()))
{
contactInfo.Skill += dt.Rows[i].ItemArray[j].ToString().Trim() + ",";
}
break;
case "HTML":
if (!string.IsNullOrEmpty(dt.Rows[i].ItemArray[j].ToString().Trim()))
{
contactInfo.Skill += dt.Rows[i].ItemArray[j].ToString().Trim() + ",";
}
break;
case "Javascript":
if (!string.IsNullOrEmpty(dt.Rows[i].ItemArray[j].ToString().Trim()))
{
contactInfo.Skill += dt.Rows[i].ItemArray[j].ToString().Trim() + ",";
}
break;
case "Bootstrap":
if (!string.IsNullOrEmpty(dt.Rows[i].ItemArray[j].ToString().Trim()))
{
contactInfo.Skill += dt.Rows[i].ItemArray[j].ToString().Trim() + ",";
}
break;
case "Delphi":
if (!string.IsNullOrEmpty(dt.Rows[i].ItemArray[j].ToString().Trim()))
{
contactInfo.Skill += dt.Rows[i].ItemArray[j].ToString().Trim() + ",";
}
break;
case "PHP":
if (!string.IsNullOrEmpty(dt.Rows[i].ItemArray[j].ToString().Trim()))
{
contactInfo.Skill += dt.Rows[i].ItemArray[j].ToString().Trim() + ",";
}
break;
case "研替":
if (!string.IsNullOrEmpty(dt.Rows[i].ItemArray[j].ToString().Trim()))
{
contactInfo.Skill += dt.Rows[i].ItemArray[j].ToString().Trim() + ",";
}
break;
case "Hadoop":
if (!string.IsNullOrEmpty(dt.Rows[i].ItemArray[j].ToString().Trim()))
{
contactInfo.Skill += dt.Rows[i].ItemArray[j].ToString().Trim() + ",";
}
break;
case "ETL":
if (!string.IsNullOrEmpty(dt.Rows[i].ItemArray[j].ToString().Trim()))
{
contactInfo.Skill += dt.Rows[i].ItemArray[j].ToString().Trim() + ",";
}
break;
case "R":
if (!string.IsNullOrEmpty(dt.Rows[i].ItemArray[j].ToString().Trim()))
{
contactInfo.Skill += dt.Rows[i].ItemArray[j].ToString().Trim() + ",";
}
break;
case "notes":
if (!string.IsNullOrEmpty(dt.Rows[i].ItemArray[j].ToString().Trim()))
{
contactInfo.Skill += dt.Rows[i].ItemArray[j].ToString().Trim() + ",";
}
break;
case "UI/UX":
if (!string.IsNullOrEmpty(dt.Rows[i].ItemArray[j].ToString().Trim()))
{
contactInfo.Skill += dt.Rows[i].ItemArray[j].ToString().Trim() + ",";
}
break;
case "資料庫":
if (!string.IsNullOrEmpty(dt.Rows[i].ItemArray[j].ToString().Trim()))
{
contactInfo.Skill += dt.Rows[i].ItemArray[j].ToString().Trim() + ",";
}
break;
case "Linux":
if (!string.IsNullOrEmpty(dt.Rows[i].ItemArray[j].ToString().Trim()))
{
contactInfo.Skill += dt.Rows[i].ItemArray[j].ToString().Trim() + ",";
}
break;
}
}
////聯繫狀況
do
{
ContactStatus contactStatus = new ContactStatus();
for (int z = 3; z <= 5; z++)
{
switch (dt.Columns[z].ToString().Trim())
{
case "日期":
msg = Valid.GetInstance().ValidDateFormat(dt.Rows[i].ItemArray[z].ToString().Trim());
if (msg != string.Empty)
{
contactSituationList.Clear();
ErrorMessage = "第" + (i + 1) + "行" + msg + "\n請檢查Excel";
return(contactSituationList);
}
contactStatus.Contact_Date = dt.Rows[i].ItemArray[z].ToString().Trim();
break;
case "聯絡狀況":
if (string.IsNullOrEmpty(dt.Rows[i].ItemArray[z].ToString().Trim()))
{
contactStatus.Contact_Status = "(無)";
}
else
{
msg = TalentValid.GetInstance().ValidContactStatus(dt.Rows[i].ItemArray[z].ToString().Trim());
if (msg != string.Empty)
{
contactSituationList.Clear();
ErrorMessage = "第" + (i + 1) + "行" + msg + "\n請檢查Excel";
return(contactSituationList);
}
contactStatus.Contact_Status = dt.Rows[i].ItemArray[z].ToString().Trim();
}
break;
case "說明":
contactStatus.Remarks = dt.Rows[i].ItemArray[z].ToString().Trim();
break;
}
}
contactStatusList.Add(contactStatus);
i++;
if (i >= dt.Rows.Count)
{
break;
}
} while (string.IsNullOrEmpty(dt.Rows[i].ItemArray[0].ToString().Trim()) && string.IsNullOrEmpty(dt.Rows[i].ItemArray[1].ToString().Trim()));
i--; ////因為在do迴圈有先i++判斷下一列的姓名與代碼是否有值，因此在跳出迴圈時，要把它減回來
contactInfo.Skill = contactInfo.Skill.RemoveEndWithDelimiter(",");
contactSituation.Info = contactInfo;
contactSituation.Status = contactStatusList;
contactSituationList.Add(contactSituation);
}
msg = Talent.GetInstance().ValidCodeIsRepeat(checkCodeIsRepeat);
if (msg != string.Empty)
{
contactSituationList.Clear();
ErrorMessage = msg + "\n請檢查Excel";
return(contactSituationList);
}
return(contactSituationList);
}
catch (Exception ex)
{
LogInfo.WriteErrorInfo(ex);
contactSituationList.Clear();
ErrorMessage = "讀取Excel發生錯誤";
return(contactSituationList);
}
}
/// <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();
}
}
/// <summary>
/// 根據面談條件查詢符合的聯繫ID
/// </summary>
/// <param name="isInterview">是否已面談，值為"已面談","未面談","不限"</param>
/// <param name="interviewResult">面談結果，值為"錄用","不錄用","暫保留","不限"</param>
/// <param name="startInterviewDate">起始日期，日期格式</param>
/// <param name="endInterviewDate">結束日期，日期格式</param>
/// <returns>回傳符合條件的聯繫ID</returns>
public List <string> SelectIdByInterviewFilter(string isInterview, string interviewResult, string startInterviewDate, string endInterviewDate)
{
ErrorMessage = string.Empty;
DataTable dt = new DataTable();
List <string> idList = new List <string>();
string select = string.Empty;
try
{
if (Valid.GetInstance().ValidDateRange(startInterviewDate, endInterviewDate) != string.Empty)
{
MessageBox.Show("面試日期之日期格式或者是日期區間不正確", "錯誤訊息");
return(new List <string>());
}
using (SqlDataAdapter da = new SqlDataAdapter(select, ScConnection))
{
////是否已面談
if (isInterview == "已面談")
{
da.SelectCommand.CommandText += @"(select Contact_Id from Interview_Info where Appointment is not null and Appointment !='') INTERSECT ";
}
else if (isInterview == "未面談")
{
da.SelectCommand.CommandText += @"(select Contact_Id from Contact_Info
EXCEPT
select Contact_Id from Interview_Info where Appointment is not null and Appointment !='') INTERSECT ";
}
////面談結果
if (interviewResult != "不限")
{
da.SelectCommand.CommandText += @"(select Contact_Id from Interview_Info where Appointment = ISNULL(@interviewResult, Appointment)) INTERSECT ";
da.SelectCommand.Parameters.Add("@interviewResult", SqlDbType.NVarChar).Value = Common.GetInstance().ValueIsNullOrEmpty(interviewResult);
}
////面談日期
if (!string.IsNullOrEmpty(startInterviewDate) || !string.IsNullOrEmpty(endInterviewDate))
{
da.SelectCommand.CommandText += @"(select Contact_Id from Interview_Info where Interview_Date <= ISNULL(@endInterviewDate, Interview_Date) AND Interview_Date >= ISNULL(@startInterviewDate, Interview_Date)) INTERSECT ";
da.SelectCommand.Parameters.Add("@endInterviewDate", SqlDbType.DateTime).Value = Common.GetInstance().ValueIsNullOrEmpty(endInterviewDate);
da.SelectCommand.Parameters.Add("@startInterviewDate", SqlDbType.DateTime).Value = Common.GetInstance().ValueIsNullOrEmpty(startInterviewDate);
}
////所有ID
da.SelectCommand.CommandText += @"(select Contact_Id from Contact_Info)";
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();
}
}
