/// <summary>
/// 刪除指定的面談資料
/// </summary>
/// <param name="interviewId">面談ID</param>
/// <returns></returns>
public string DelInterviewDataByInterviewId(string interviewId)
{
if (!this.ValidInterviewIdIsAppear(interviewId))
{
return("此面談資料不存在");
}
if (this.DelImageByInterviewId(interviewId) == "圖片刪除失敗")
{
return("圖片刪除失敗");
}
if (TalentFiles.GetInstance().DelFilesByInterviewId(interviewId) == "附加檔案刪除失敗")
{
return("附加檔案刪除失敗");
}
string sqlStr = string.Empty;
try
{
using (SqlCommand cmd = new SqlCommand(sqlStr, ScConnection, StTransaction))
{
////更新最後修改時間
cmd.CommandText = @"update Contact_Info set UpdateTime = @updateTime where Contact_Id =
(select Contact_Id from Interview_Info where Interview_Id = @interviewId)";
cmd.Parameters.Add("@updateTime", SqlDbType.DateTime).Value = DateTime.Now.ToString();
cmd.Parameters.Add("@interviewId", SqlDbType.Int).Value = interviewId;
cmd.ExecuteNonQuery();
////專案經驗資料
cmd.CommandText = @"delete from Project_Experience where Interview_Id = @id";
cmd.Parameters.Add("@id", SqlDbType.Int).Value = interviewId;
cmd.ExecuteNonQuery();
////面談評語資料
cmd.CommandText = @"delete from Interview_Comments where Interview_Id = @id";
cmd.ExecuteNonQuery();
////附加檔案
cmd.CommandText = @"delete from Files where Interview_Id = @id";
cmd.ExecuteNonQuery();
////面談基本資料
cmd.CommandText = @"delete from Interview_Info where Interview_Id = @id";
cmd.ExecuteNonQuery();
this.CommitTransaction();
}
return("刪除成功");
}
catch (Exception ex)
{
LogInfo.WriteErrorInfo(ex);
this.RollbackTransaction();
return("資料庫發生錯誤");
}
finally
{
this.CloseDatabaseConnection();
}
}
/// <summary>
/// 將附加檔案資訊寫入資料庫
/// </summary>
/// <param name="files"></param>
/// <param name="interviewId"></param>
/// <param name="attachedFileMode"></param>
/// <returns></returns>
public string SaveAttachedFilesToDB(List <string> files, string interviewId, string attachedFileMode)
{
string sqlStr = string.Empty;
List <string> path = TalentFiles.GetInstance().SaveAttachedFiles(files, interviewId, attachedFileMode);
if (path.Contains("上傳失敗"))
{
return("檔案上傳失敗");
}
try
{
using (SqlCommand cmd = new SqlCommand(sqlStr, ScConnection, StTransaction))
{
cmd.CommandText = @"delete from Files where Interview_Id = @Interview_Id and Belong = @Belong";
cmd.Parameters.Add("@Interview_Id", SqlDbType.Int).Value = interviewId;
cmd.Parameters.Add("@Belong", SqlDbType.NVarChar).Value = attachedFileMode;
cmd.ExecuteNonQuery();
cmd.CommandText = @"insert into Files (Interview_Id,File_Path,Belong) values (@Interview_Id,@File_Path,@Belong)";
foreach (string file in path)
{
cmd.Parameters.Clear();
cmd.Parameters.Add("@Interview_Id", SqlDbType.Int).Value = interviewId;
cmd.Parameters.Add("@File_Path", SqlDbType.NVarChar).Value = file;
cmd.Parameters.Add("@Belong", SqlDbType.NVarChar).Value = attachedFileMode;
cmd.ExecuteNonQuery();
}
cmd.Parameters.Clear();
cmd.CommandText = @"update Contact_Info set UpdateTime = @updateTime where Contact_Id =
(select Contact_Id from Interview_Info where Interview_Id = @interviewId)";
cmd.Parameters.Add("@updateTime", SqlDbType.DateTime).Value = DateTime.Now.ToString();
cmd.Parameters.Add("@interviewId", SqlDbType.Int).Value = interviewId;
cmd.ExecuteNonQuery();
this.CommitTransaction();
return("檔案上傳成功");
}
}
catch (Exception ex)
{
LogInfo.WriteErrorInfo(ex);
this.RollbackTransaction();
return("檔案上傳失敗");
}
finally
{
this.CloseDatabaseConnection();
}
}
/// <summary>
/// 修改面試基本資料
/// </summary>
/// <param name="editData">面試基本資料</param>
/// <param name="interviewId">面試ID</param>
/// <returns></returns>
public string UpdateInterviewInfoData(DataTable editData, string interviewId, string dbPath, string uiPath)
{
if (!this.ValidInterviewIdIsAppear(interviewId))
{
return("修改失敗");
}
string path = TalentFiles.GetInstance().UpdateImage(dbPath, uiPath, interviewId);
if (path.Equals("上傳失敗") || path.Equals("不存在的路徑") || path.Equals("檔案刪除失敗"))
{
throw new Exception();
}
string update = @"update Interview_Info set Vacancies=@vacancies,Interview_Date=@interviewDate,Name=@name,Sex=@sex,Birthday=@birthday
,Married=@married,Mail=@mail,Adress=@adress,CellPhone=@cellPhone,Image=@image,Expertise_Language=@expertiseLanguage
,Expertise_Tools=@expertiseTools,Expertise_Tools_Framwork=@expertiseToolsFramwork,Expertise_Devops=@expertiseDevops
,Expertise_OS=@expertiseOS
,Expertise_BigData=@expertiseBigData,Expertise_DataBase=@expertiseDataBase,Expertise_Certification=@expertiseCertification
,IsStudy=@isStudy,IsService=@IsService,Relatives_Relationship=@relativesRelationship,Relatives_Name=@relativesName
,Care_Work=@careWork,Hope_Salary=@hopeSalary,When_Report=@whenReport,Advantage=@advantage,Disadvantages=@disadvantages
,Hobby=@hobby,Attract_Reason=@attractReason,Future_Goal=@futureGoal,Hope_Supervisor=@hopeSupervisor
,Hope_Promise=@hopePromise,Introduction=@introduction,During_Service=@duringService,Exemption_Reason=@exemptionReason
,Urgent_Contact_Person=@urgentContactPerson,Urgent_Relationship=@urgentRelationship,Urgent_CellPhone=@urgentCellPhone
,Education=@education,Language=@language,Work_Experience=@workExperience
where Interview_Id=@interviewId";
try
{
using (SqlCommand cmd = new SqlCommand(update, ScConnection, StTransaction))
{
foreach (DataRow dr in editData.Rows)
{
cmd.Parameters.Clear();
cmd.Parameters.Add("@education", SqlDbType.NVarChar).Value = dr["Education"].ToString().Trim();
cmd.Parameters.Add("@language", SqlDbType.NVarChar).Value = dr["Language"].ToString().Trim();
cmd.Parameters.Add("@workExperience", SqlDbType.NVarChar).Value = dr["Work_Experience"].ToString().Trim();
cmd.Parameters.Add("@vacancies", SqlDbType.NVarChar).Value = dr["Vacancies"].ToString().Trim();
cmd.Parameters.Add("@urgentCellPhone", SqlDbType.VarChar).Value = dr["Urgent_CellPhone"].ToString().Trim();
cmd.Parameters.Add("@urgentRelationship", SqlDbType.NVarChar).Value = dr["Urgent_Relationship"].ToString().Trim();
cmd.Parameters.Add("@urgentContactPerson", SqlDbType.NVarChar).Value = dr["Urgent_Contact_Person"].ToString().Trim();
cmd.Parameters.Add("@exemptionReason", SqlDbType.NVarChar).Value = dr["Exemption_Reason"].ToString().Trim();
cmd.Parameters.Add("@duringService", SqlDbType.NVarChar).Value = dr["During_Service"].ToString().Trim();
cmd.Parameters.Add("@introduction", SqlDbType.NVarChar).Value = dr["Introduction"].ToString().Trim();
cmd.Parameters.Add("@hopePromise", SqlDbType.NVarChar).Value = dr["Hope_Promise"].ToString().Trim();
cmd.Parameters.Add("@hopeSupervisor", SqlDbType.NVarChar).Value = dr["Hope_Supervisor"].ToString().Trim();
cmd.Parameters.Add("@futureGoal", SqlDbType.NVarChar).Value = dr["Future_Goal"].ToString().Trim();
cmd.Parameters.Add("@attractReason", SqlDbType.NVarChar).Value = dr["Attract_Reason"].ToString().Trim();
cmd.Parameters.Add("@hobby", SqlDbType.NVarChar).Value = dr["Hobby"].ToString().Trim();
cmd.Parameters.Add("@disadvantages", SqlDbType.NVarChar).Value = dr["Disadvantages"].ToString().Trim();
cmd.Parameters.Add("@advantage", SqlDbType.NVarChar).Value = dr["Advantage"].ToString().Trim();
cmd.Parameters.Add("@whenReport", SqlDbType.NVarChar).Value = dr["When_Report"].ToString().Trim();
cmd.Parameters.Add("@hopeSalary", SqlDbType.NVarChar).Value = dr["Hope_salary"].ToString().Trim();
cmd.Parameters.Add("@careWork", SqlDbType.NVarChar).Value = dr["Care_Work"].ToString().Trim();
cmd.Parameters.Add("@relativesName", SqlDbType.NVarChar).Value = dr["Relatives_Name"].ToString().Trim();
cmd.Parameters.Add("@relativesRelationship", SqlDbType.NVarChar).Value = dr["Relatives_Relationship"].ToString().Trim();
cmd.Parameters.Add("@isService", SqlDbType.NVarChar).Value = dr["IsService"].ToString().Trim();
cmd.Parameters.Add("@isStudy", SqlDbType.NVarChar).Value = dr["IsStudy"].ToString().Trim();
cmd.Parameters.Add("@expertiseCertification", SqlDbType.NVarChar).Value = dr["Expertise_Certification"].ToString().Trim();
cmd.Parameters.Add("@expertiseDataBase", SqlDbType.NVarChar).Value = dr["Expertise_DataBase"].ToString().Trim();
cmd.Parameters.Add("@expertiseBigData", SqlDbType.NVarChar).Value = dr["Expertise_BigData"].ToString().Trim();
cmd.Parameters.Add("@expertiseOS", SqlDbType.NVarChar).Value = dr["Expertise_OS"].ToString().Trim();
cmd.Parameters.Add("@expertiseDevops", SqlDbType.NVarChar).Value = dr["Expertise_Devops"].ToString().Trim();
cmd.Parameters.Add("@expertiseTools", SqlDbType.NVarChar).Value = dr["Expertise_Tools"].ToString().Trim();
cmd.Parameters.Add("@expertiseToolsFramwork", SqlDbType.NVarChar).Value = dr["Expertise_Tools_Framwork"].ToString().Trim();
cmd.Parameters.Add("@expertiseLanguage", SqlDbType.NVarChar).Value = dr["Expertise_language"].ToString().Trim();
cmd.Parameters.Add("@image", SqlDbType.NVarChar).Value = path.Trim();
cmd.Parameters.Add("@interviewDate", SqlDbType.Date).Value = dr["Interview_Date"].ToString().Trim();
cmd.Parameters.Add("@interviewId", SqlDbType.Int).Value = interviewId;
cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = dr["Name"].ToString().Trim();
cmd.Parameters.Add("@sex", SqlDbType.NVarChar).Value = dr["Sex"].ToString().Trim();
cmd.Parameters.Add("@birthday", SqlDbType.Date).Value = Common.GetInstance().ValueIsNullOrEmpty(dr["Birthday"].ToString().Trim());
cmd.Parameters.Add("@married", SqlDbType.NVarChar).Value = dr["Married"].ToString().Trim();
cmd.Parameters.Add("@mail", SqlDbType.VarChar).Value = dr["Mail"].ToString().Trim();
cmd.Parameters.Add("@adress", SqlDbType.NVarChar).Value = dr["Adress"].ToString().Trim();
cmd.Parameters.Add("@cellPhone", SqlDbType.NVarChar).Value = dr["CellPhone"].ToString().Trim();
cmd.ExecuteNonQuery();
}
cmd.Parameters.Clear();
cmd.CommandText = @"update Contact_Info set UpdateTime = @updateTime where Contact_Id =
(select Contact_Id from Interview_Info where Interview_Id = @interviewId)";
cmd.Parameters.Add("@updateTime", SqlDbType.DateTime).Value = DateTime.Now.ToString();
cmd.Parameters.Add("@interviewId", SqlDbType.Int).Value = interviewId;
cmd.ExecuteNonQuery();
this.CommitTransaction();
return("修改成功");
}
}
catch (Exception ex)
{
this.RollbackTransaction();
LogInfo.WriteErrorInfo(ex);
return("修改失敗");
}
finally
{
this.CloseDatabaseConnection();
}
}
/// <summary>
/// 儲存面談資料
/// </summary>
/// <param name="saveData">面談資料</param>
/// <param name="interviewId">面談ID</param>
/// <param name="dbPath">資料庫的圖片路徑</param>
/// <param name="uiPath">UI的圖片路徑</param>
/// <returns></returns>
public string SaveInterviewData(DataSet saveData, string interviewId, string dbPath, string uiPath)
{
if (!Talent.GetInstance().ValidInterviewIdIsAppear(interviewId))
{
return("沒有對應的面試基本資料");
}
string path = TalentFiles.GetInstance().UpdateImage(dbPath, uiPath, interviewId);
if (path.Equals("上傳失敗") || path.Equals("不存在的路徑") || path.Equals("檔案刪除失敗"))
{
throw new Exception(path);
}
string update = @"update Interview_Info set Vacancies=@vacancies,Interview_Date=@interviewDate,Name=@name,Sex=@sex,Birthday=@birthday
,Married=@married,Mail=@mail,Adress=@adress,CellPhone=@cellPhone,Image=@image,Expertise_Language=@expertiseLanguage
,Expertise_Tools=@expertiseTools,Expertise_Tools_Framwork=@expertiseToolsFramwork,Expertise_Devops=@expertiseDevops
,Expertise_OS=@expertiseOS
,Expertise_BigData=@expertiseBigData,Expertise_DataBase=@expertiseDataBase,Expertise_Certification=@expertiseCertification
,IsStudy=@isStudy,IsService=@IsService,Relatives_Relationship=@relativesRelationship,Relatives_Name=@relativesName
,Care_Work=@careWork,Hope_Salary=@hopeSalary,When_Report=@whenReport,Advantage=@advantage,Disadvantages=@disadvantages
,Hobby=@hobby,Attract_Reason=@attractReason,Future_Goal=@futureGoal,Hope_Supervisor=@hopeSupervisor
,Hope_Promise=@hopePromise,Introduction=@introduction,During_Service=@duringService,Exemption_Reason=@exemptionReason
,Urgent_Contact_Person=@urgentContactPerson,Urgent_Relationship=@urgentRelationship,Urgent_CellPhone=@urgentCellPhone
,Education=@education,Language=@language,Work_Experience=@workExperience
where Interview_Id=@interviewId";
try
{
using (SqlCommand cmd = new SqlCommand(update, ScConnection, StTransaction))
{
////儲存面談基本資料
foreach (DataRow dr in saveData.Tables[1].Rows)
{
cmd.Parameters.Clear();
cmd.Parameters.Add("@education", SqlDbType.NVarChar).Value = dr["Education"].ToString().Trim();
cmd.Parameters.Add("@language", SqlDbType.NVarChar).Value = dr["Language"].ToString().Trim();
cmd.Parameters.Add("@workExperience", SqlDbType.NVarChar).Value = dr["Work_Experience"].ToString().Trim();
cmd.Parameters.Add("@vacancies", SqlDbType.NVarChar).Value = dr["Vacancies"].ToString().Trim();
cmd.Parameters.Add("@urgentCellPhone", SqlDbType.VarChar).Value = dr["Urgent_CellPhone"].ToString().Trim();
cmd.Parameters.Add("@urgentRelationship", SqlDbType.NVarChar).Value = dr["Urgent_Relationship"].ToString().Trim();
cmd.Parameters.Add("@urgentContactPerson", SqlDbType.NVarChar).Value = dr["Urgent_Contact_Person"].ToString().Trim();
cmd.Parameters.Add("@exemptionReason", SqlDbType.NVarChar).Value = dr["Exemption_Reason"].ToString().Trim();
cmd.Parameters.Add("@duringService", SqlDbType.NVarChar).Value = dr["During_Service"].ToString().Trim();
cmd.Parameters.Add("@introduction", SqlDbType.NVarChar).Value = dr["Introduction"].ToString().Trim();
cmd.Parameters.Add("@hopePromise", SqlDbType.NVarChar).Value = dr["Hope_Promise"].ToString().Trim();
cmd.Parameters.Add("@hopeSupervisor", SqlDbType.NVarChar).Value = dr["Hope_Supervisor"].ToString().Trim();
cmd.Parameters.Add("@futureGoal", SqlDbType.NVarChar).Value = dr["Future_Goal"].ToString().Trim();
cmd.Parameters.Add("@attractReason", SqlDbType.NVarChar).Value = dr["Attract_Reason"].ToString().Trim();
cmd.Parameters.Add("@hobby", SqlDbType.NVarChar).Value = dr["Hobby"].ToString().Trim();
cmd.Parameters.Add("@disadvantages", SqlDbType.NVarChar).Value = dr["Disadvantages"].ToString().Trim();
cmd.Parameters.Add("@advantage", SqlDbType.NVarChar).Value = dr["Advantage"].ToString().Trim();
cmd.Parameters.Add("@whenReport", SqlDbType.NVarChar).Value = dr["When_Report"].ToString().Trim();
cmd.Parameters.Add("@hopeSalary", SqlDbType.NVarChar).Value = dr["Hope_salary"].ToString().Trim();
cmd.Parameters.Add("@careWork", SqlDbType.NVarChar).Value = dr["Care_Work"].ToString().Trim();
cmd.Parameters.Add("@relativesName", SqlDbType.NVarChar).Value = dr["Relatives_Name"].ToString().Trim();
cmd.Parameters.Add("@relativesRelationship", SqlDbType.NVarChar).Value = dr["Relatives_Relationship"].ToString().Trim();
cmd.Parameters.Add("@isService", SqlDbType.NVarChar).Value = dr["IsService"].ToString().Trim();
cmd.Parameters.Add("@isStudy", SqlDbType.NVarChar).Value = dr["IsStudy"].ToString().Trim();
cmd.Parameters.Add("@expertiseCertification", SqlDbType.NVarChar).Value = dr["Expertise_Certification"].ToString().Trim();
cmd.Parameters.Add("@expertiseDataBase", SqlDbType.NVarChar).Value = dr["Expertise_DataBase"].ToString().Trim();
cmd.Parameters.Add("@expertiseBigData", SqlDbType.NVarChar).Value = dr["Expertise_BigData"].ToString().Trim();
cmd.Parameters.Add("@expertiseOS", SqlDbType.NVarChar).Value = dr["Expertise_OS"].ToString().Trim();
cmd.Parameters.Add("@expertiseDevops", SqlDbType.NVarChar).Value = dr["Expertise_Devops"].ToString().Trim();
cmd.Parameters.Add("@expertiseTools", SqlDbType.NVarChar).Value = dr["Expertise_Tools"].ToString().Trim();
cmd.Parameters.Add("@expertiseToolsFramwork", SqlDbType.NVarChar).Value = dr["Expertise_Tools_Framwork"].ToString().Trim();
cmd.Parameters.Add("@expertiseLanguage", SqlDbType.NVarChar).Value = dr["Expertise_language"].ToString().Trim();
cmd.Parameters.Add("@image", SqlDbType.NVarChar).Value = path.Trim();
cmd.Parameters.Add("@interviewDate", SqlDbType.Date).Value = dr["Interview_Date"].ToString().Trim();
cmd.Parameters.Add("@interviewId", SqlDbType.Int).Value = interviewId;
cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = dr["Name"].ToString().Trim();
cmd.Parameters.Add("@sex", SqlDbType.NVarChar).Value = dr["Sex"].ToString().Trim();
cmd.Parameters.Add("@birthday", SqlDbType.Date).Value = Common.GetInstance().ValueIsNullOrEmpty(dr["Birthday"].ToString().Trim());
cmd.Parameters.Add("@married", SqlDbType.NVarChar).Value = dr["Married"].ToString().Trim();
cmd.Parameters.Add("@mail", SqlDbType.VarChar).Value = dr["Mail"].ToString().Trim();
cmd.Parameters.Add("@adress", SqlDbType.NVarChar).Value = dr["Adress"].ToString().Trim();
cmd.Parameters.Add("@cellPhone", SqlDbType.NVarChar).Value = dr["CellPhone"].ToString().Trim();
cmd.ExecuteNonQuery();
}
////儲存專案經驗資料
cmd.CommandText = @"delete from [Project_Experience] where Interview_Id=@interviewId";
cmd.ExecuteNonQuery();
cmd.CommandText = @"insert into [Project_Experience] ([Company],[Project_Name],[OS],[Database],[Position],[Language],[Tools],[Description],[Start_End_Date],[Interview_Id])
values (@Company,@Project_Name,@OS,@Database,@Position,@Language,@Tools,@Description,@Start_End_Date,@Interview_Id)";
foreach (DataRow dr in saveData.Tables[2].Rows)
{
cmd.Parameters.Clear();
cmd.Parameters.Add(@"Company", SqlDbType.NVarChar).Value = dr["Company"].ToString();
cmd.Parameters.Add(@"Project_Name", SqlDbType.NVarChar).Value = dr["Project_Name"].ToString();
cmd.Parameters.Add(@"OS", SqlDbType.NVarChar).Value = dr["OS"].ToString();
cmd.Parameters.Add(@"Database", SqlDbType.NVarChar).Value = dr["Database"].ToString();
cmd.Parameters.Add(@"Position", SqlDbType.NVarChar).Value = dr["Position"].ToString();
cmd.Parameters.Add(@"Language", SqlDbType.NVarChar).Value = dr["Language"].ToString();
cmd.Parameters.Add(@"Tools", SqlDbType.NVarChar).Value = dr["Tools"].ToString();
cmd.Parameters.Add(@"Description", SqlDbType.NVarChar).Value = dr["Description"].ToString();
cmd.Parameters.Add(@"Start_End_Date", SqlDbType.NVarChar).Value = dr["Start_End_Date"].ToString();
cmd.Parameters.Add(@"Interview_Id", SqlDbType.Int).Value = interviewId;
cmd.ExecuteNonQuery();
}
////儲存面談結果資料
cmd.CommandText = @"update Interview_Info set Appointment=@Appointment,Results_Remark=@Results_Remark where Interview_Id=@Interview_Id";
foreach (DataRow dr in saveData.Tables[3].Rows)
{
cmd.Parameters.Clear();
cmd.Parameters.Add(@"Appointment", SqlDbType.NVarChar).Value = dr["Appointment"].ToString();
cmd.Parameters.Add(@"Results_Remark", SqlDbType.NVarChar).Value = dr["Results_Remark"].ToString();
cmd.Parameters.Add(@"Interview_Id", SqlDbType.Int).Value = interviewId;
cmd.ExecuteNonQuery();
}
cmd.Parameters.Clear();
cmd.CommandText = @"delete from [Interview_Comments] where Interview_Id=@interviewId";
cmd.Parameters.Add("@interviewId", SqlDbType.Int).Value = interviewId;
cmd.ExecuteNonQuery();
cmd.CommandText = @"insert into [Interview_Comments] ([Interviewer],[Result],[Interview_Id])
values (@Interviewer,@Result,@Interview_Id)";
foreach (DataRow dr in saveData.Tables[0].Rows)
{
cmd.Parameters.Clear();
cmd.Parameters.Add(@"Interviewer", SqlDbType.NVarChar).Value = dr["Interviewer"].ToString();
cmd.Parameters.Add(@"Result", SqlDbType.NVarChar).Value = dr["Result"].ToString();
cmd.Parameters.Add(@"Interview_Id", SqlDbType.Int).Value = interviewId;
cmd.ExecuteNonQuery();
}
cmd.Parameters.Clear();
cmd.CommandText = @"update Contact_Info set UpdateTime = @updateTime where Contact_Id =
(select Contact_Id from Interview_Info where Interview_Id = @interviewId)";
cmd.Parameters.Add("@updateTime", SqlDbType.DateTime).Value = DateTime.Now.ToString();
cmd.Parameters.Add("@interviewId", SqlDbType.Int).Value = interviewId;
cmd.ExecuteNonQuery();
this.CommitTransaction();
return("修改成功");
}
}
catch (Exception ex)
{
this.RollbackTransaction();
LogInfo.WriteErrorInfo(ex);
return("修改失敗");
}
finally
{
this.CloseDatabaseConnection();
}
}
/// <summary>
/// 新增面試基本資料
/// </summary>
/// <param name="inData"></param>
/// <param name="contactId"></param>
/// <returns></returns>
public string InsertInterviewInfoData(DataTable inData, string contactId)
{
if (!this.ValidIdIsAppear(contactId))
{
return("此面試基本資料，沒有對應的聯繫資料");
}
string interviewId = string.Empty;
string insert = @"insert into Interview_Info ([Vacancies],[Interview_Date],[Contact_Id],[Name],[Sex],[Birthday],[Married],[Mail]
,[Adress],[CellPhone],[Expertise_Language],[Expertise_Tools],[Expertise_Tools_Framwork],[Expertise_Devops],[Expertise_OS]
,[Expertise_BigData],[Expertise_DataBase],[Expertise_Certification],[IsStudy],[IsService],[Relatives_Relationship]
,[Relatives_Name],[Care_Work],[Hope_Salary],[When_Report],[Advantage],[Disadvantages],[Hobby],[Attract_Reason]
,[Future_Goal],[Hope_Supervisor],[Hope_Promise],[Introduction],[During_Service]
,[Exemption_Reason],[Urgent_Contact_Person],[Urgent_Relationship],[Urgent_CellPhone],[Education],[Language],[Work_Experience])
output inserted.Interview_Id values
(@vacancies,@interviewDate,@contactId,@name,@sex,@birthday,@married,@mail,@adress,@cellPhone,@expertiseLanguage
,@expertiseTools,@expertiseToolsFramwork,@expertiseDevops,@expertiseOS,@expertiseBigData,@expertiseDataBase,@expertiseCertification
,@isStudy,@IsService,@relativesRelationship,@relativesName,@careWork,@hopeSalary,@whenReport,@advantage
,@disadvantages,@hobby,@attractReason,@futureGoal,@hopeSupervisor,@hopePromise,@introduction,@duringService
,@exemptionReason,@urgentContactPerson,@urgentRelationship,@urgentCellPhone,@education,@language,@workExperience)";
try
{
using (SqlCommand cmd = new SqlCommand(insert, ScConnection, StTransaction))
{
foreach (DataRow dr in inData.Rows)
{
cmd.Parameters.Clear();
cmd.Parameters.Add("@education", SqlDbType.NVarChar).Value = dr["Education"].ToString();
cmd.Parameters.Add("@language", SqlDbType.NVarChar).Value = dr["Language"].ToString();
cmd.Parameters.Add("@workExperience", SqlDbType.NVarChar).Value = dr["Work_Experience"].ToString();
cmd.Parameters.Add("@vacancies", SqlDbType.NVarChar).Value = dr["Vacancies"].ToString();
cmd.Parameters.Add("@urgentCellPhone", SqlDbType.VarChar).Value = dr["Urgent_CellPhone"].ToString();
cmd.Parameters.Add("@urgentRelationship", SqlDbType.NVarChar).Value = dr["Urgent_Relationship"].ToString();
cmd.Parameters.Add("@urgentContactPerson", SqlDbType.NVarChar).Value = dr["Urgent_Contact_Person"].ToString();
cmd.Parameters.Add("@exemptionReason", SqlDbType.NVarChar).Value = dr["Exemption_Reason"].ToString();
cmd.Parameters.Add("@duringService", SqlDbType.NVarChar).Value = dr["During_Service"].ToString();
cmd.Parameters.Add("@introduction", SqlDbType.NVarChar).Value = dr["Introduction"].ToString();
cmd.Parameters.Add("@hopePromise", SqlDbType.NVarChar).Value = dr["Hope_Promise"].ToString();
cmd.Parameters.Add("@hopeSupervisor", SqlDbType.NVarChar).Value = dr["Hope_Supervisor"].ToString();
cmd.Parameters.Add("@futureGoal", SqlDbType.NVarChar).Value = dr["Future_Goal"].ToString();
cmd.Parameters.Add("@attractReason", SqlDbType.NVarChar).Value = dr["Attract_Reason"].ToString();
cmd.Parameters.Add("@hobby", SqlDbType.NVarChar).Value = dr["Hobby"].ToString();
cmd.Parameters.Add("@disadvantages", SqlDbType.NVarChar).Value = dr["Disadvantages"].ToString();
cmd.Parameters.Add("@advantage", SqlDbType.NVarChar).Value = dr["Advantage"].ToString();
cmd.Parameters.Add("@whenReport", SqlDbType.NVarChar).Value = dr["When_Report"].ToString();
cmd.Parameters.Add("@hopeSalary", SqlDbType.NVarChar).Value = dr["Hope_salary"].ToString();
cmd.Parameters.Add("@careWork", SqlDbType.NVarChar).Value = dr["Care_Work"].ToString();
cmd.Parameters.Add("@relativesName", SqlDbType.NVarChar).Value = dr["Relatives_Name"].ToString();
cmd.Parameters.Add("@relativesRelationship", SqlDbType.NVarChar).Value = dr["Relatives_Relationship"].ToString();
cmd.Parameters.Add("@isService", SqlDbType.NVarChar).Value = dr["IsService"].ToString();
cmd.Parameters.Add("@isStudy", SqlDbType.NVarChar).Value = dr["IsStudy"].ToString();
cmd.Parameters.Add("@expertiseCertification", SqlDbType.NVarChar).Value = dr["Expertise_Certification"].ToString();
cmd.Parameters.Add("@expertiseDataBase", SqlDbType.NVarChar).Value = dr["Expertise_DataBase"].ToString();
cmd.Parameters.Add("@expertiseBigData", SqlDbType.NVarChar).Value = dr["Expertise_BigData"].ToString();
cmd.Parameters.Add("@expertiseOS", SqlDbType.NVarChar).Value = dr["Expertise_OS"].ToString();
cmd.Parameters.Add("@expertiseDevops", SqlDbType.NVarChar).Value = dr["Expertise_Devops"].ToString();
cmd.Parameters.Add("@expertiseTools", SqlDbType.NVarChar).Value = dr["Expertise_Tools"].ToString();
cmd.Parameters.Add("@expertiseToolsFramwork", SqlDbType.NVarChar).Value = dr["Expertise_Tools_Framwork"].ToString();
cmd.Parameters.Add("@expertiseLanguage", SqlDbType.NVarChar).Value = dr["Expertise_language"].ToString();
cmd.Parameters.Add("@interviewDate", SqlDbType.Date).Value = dr["Interview_Date"].ToString();
cmd.Parameters.Add("@contactId", SqlDbType.Int).Value = contactId;
cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = dr["Name"].ToString();
cmd.Parameters.Add("@sex", SqlDbType.NVarChar).Value = dr["Sex"].ToString();
cmd.Parameters.Add("@birthday", SqlDbType.Date).Value = Common.GetInstance().ValueIsNullOrEmpty(dr["Birthday"].ToString());
cmd.Parameters.Add("@married", SqlDbType.NVarChar).Value = dr["Married"].ToString();
cmd.Parameters.Add("@mail", SqlDbType.VarChar).Value = dr["Mail"].ToString();
cmd.Parameters.Add("@adress", SqlDbType.NVarChar).Value = dr["Adress"].ToString();
cmd.Parameters.Add("@cellPhone", SqlDbType.VarChar).Value = dr["CellPhone"].ToString();
interviewId = cmd.ExecuteScalar().ToString();
if (!string.IsNullOrEmpty(dr["Image"].ToString()))
{
string path = TalentFiles.GetInstance().UpLoadImage(dr["Image"].ToString(), interviewId);
if (path.Equals("上傳失敗") || path.Equals("不存在的路徑"))
{
throw new Exception(path);
}
cmd.CommandText = @"update Interview_Info set Image = @image where Interview_Id = @Interview_Id";
cmd.Parameters.Add("@image", SqlDbType.NVarChar).Value = path;
cmd.Parameters.Add("@Interview_Id", SqlDbType.Int).Value = interviewId;
cmd.ExecuteNonQuery();
}
}
this.UpdateEditTime(contactId, cmd);
this.CommitTransaction();
}
return(interviewId);
}
catch (Exception ex)
{
LogInfo.WriteErrorInfo(ex);
this.RollbackTransaction();
return("新增失敗");
}
finally
{
this.CloseDatabaseConnection();
}
}
