Пример #1
0
        /// <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();
            }
        }
Пример #2
0
        /// <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();
            }
        }
Пример #3
0
        /// <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();
            }
        }
Пример #4
0
        /// <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();
            }
        }
Пример #5
0
        /// <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();
            }
        }