/******************************************************************************* * Copyright(c) 2014 DongkeSoft All rights reserved. / Confidential * 类的信息： * 1.程序名称：HRModuleDAL.cs * 2.功能描述：员工管理更新db逻辑处理 * 编辑履历： * 作者日期版本修改内容 * 王鑫 2014/09/12 1.00 新建 *******************************************************************************/ using System; using System.Collections.Generic; using System.Data; using System.Text; using Dongke.IBOSS.PRD.Basics.BaseResources; using Dongke.IBOSS.PRD.Basics.DataAccess; using Dongke.IBOSS.PRD.Service.DataModels; using Dongke.IBOSS.PRD.WCF.DataModels; using Oracle.ManagedDataAccess.Client; namespace Dongke.IBOSS.PRD.Service.HRModuleLogic { ///

/// 员工管理更新db逻辑处理 ///

public static class HRModuleDAL { #region 员工档案 ///

/// 添加员工档案 ///

/// 员工实体类 /// 用户基本信息 /// 员工图片集 /// HRResultEntity public static HRResultEntity AddStaffInfo(StaffEntity staffInfo, SUserInfo sUserInfo, List staffPhoto) { HRResultEntity resultEnity = new HRResultEntity(); if (staffInfo == null) { resultEnity.OperateStatus = 0; return resultEnity; } IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); string nextval = "select SEQ_HR_STAFF_STAFFID.nextval from dual"; int id = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(nextval)); #region 向员工档案表插入数据 string sqlString = " INSERT INTO TP_HR_STAFF " + "(StaffID,StaffCode" + ",StaffName" + ",IDCardNo" + ",Birthday" + ",Gender" + ",MaritalStatus" + ",HomeTown" + ",PolicitalStatus" + ",National" + ",Educational" + ",Graduated" + ",SpecialField" + ",Telephone" + ",Height" + ",BloodGroup" + ",Weight" + ",Address" + ",LaidOff" + ",Disability" + ",JoinPartyDate" + ",Email" + ",OpeningBank" + ",AccountNo" + ",Remarks" //+ ",UserID" + ",AccountID" + ",ValueFlag" + ",CreateUserID" + ",UpdateUserID" + ",CreateTime" + ",UpdateTime" + ",OPTimeStamp" + ",StaffStatus" + ",OrganizationID" + ",Jobs" + ",Post" + ")" + " VALUES" + " (:StaffID" + " ,:StaffCode" + ",:StaffName" + ",:IDCardNo" + ",:Birthday" + ",:Gender" + ",:MaritalStatus" + ",:HomeTown" + ",:PolicitalStatus" + ",:National" + ",:Educational" + ",:Graduated" + ",:SpecialField" + ",:Telephone" + ",:Height" + ",:BloodGroup" + ",:Weight" + ",:Address" + ",:LaidOff" + ",:Disability" + ",:JoinPartyDate" + ",:Email" + ",:OpeningBank" + ",:AccountNo" + ",:Remarks" //+ ",:UserID" + ",:AccountID" + ",:ValueFlag" + ",:CreateUserID" + ",:UpdateUserID" + ",sysdate" + ",sysdate" + ",FUN_CMN_GetAccountDate(:AccountID)" + ",:StaffStatus" + ",:OrganizationID" + ",:Jobs" + ",:Post" + " )"; OracleParameter[] parmeters = new OracleParameter[] { new OracleParameter(":StaffID",id), new OracleParameter(":StaffCode",staffInfo.StaffCode), new OracleParameter(":StaffName",staffInfo.StaffName), new OracleParameter(":IDCardNo",staffInfo.IDCardNo), new OracleParameter(":Birthday",staffInfo.Birthday), new OracleParameter(":Gender",staffInfo.Gender), new OracleParameter(":MaritalStatus",staffInfo.MaritalStatus), new OracleParameter(":HomeTown",staffInfo.HomeTown), new OracleParameter(":PolicitalStatus",staffInfo.PolicitalStatus), new OracleParameter(":National",staffInfo.National), new OracleParameter(":Educational",staffInfo.Educational), new OracleParameter(":Graduated",staffInfo.Graduated), new OracleParameter(":SpecialField",staffInfo.SpecialField), new OracleParameter(":Telephone",staffInfo.Telephone), new OracleParameter(":Height",staffInfo.Height), new OracleParameter(":BloodGroup",staffInfo.BloodGroup), new OracleParameter(":Weight",staffInfo.Weight), new OracleParameter(":Address",staffInfo.Address), new OracleParameter(":LaidOff",staffInfo.LaidOff?1:0), new OracleParameter(":Disability",staffInfo.Disability?1:0), new OracleParameter(":JoinPartyDate",staffInfo.JoinPartyDate), new OracleParameter(":Email",staffInfo.Email), new OracleParameter(":OpeningBank",staffInfo.OpeningBank), new OracleParameter(":AccountNo",staffInfo.AccountNo), new OracleParameter(":Remarks",staffInfo.Remarks), // new OracleParameter(":UserID",sUserInfo.UserID), new OracleParameter(":AccountID",sUserInfo.AccountID), new OracleParameter(":ValueFlag",staffInfo.ValueFlag?1:0), new OracleParameter(":CreateUserID",sUserInfo.UserID), new OracleParameter(":UpdateUserID",sUserInfo.UserID), new OracleParameter(":StaffStatus",staffInfo.StaffStatus), new OracleParameter(":OrganizationID",OracleDbType.Int32, staffInfo.OrganizationID, ParameterDirection.Input ), new OracleParameter(":Jobs",OracleDbType.Int32, staffInfo.Jobs, ParameterDirection.Input ), new OracleParameter(":Post",OracleDbType.Int32, staffInfo.Post, ParameterDirection.Input ), }; #endregion resultEnity.OperateStatus = oracleTrConn.ExecuteNonQuery(sqlString, parmeters); resultEnity.HRStaffID = id; #region 向TP_HR_STAFFPHOTO插入数据 StringBuilder sbSql = new StringBuilder(); //此处添加图片信息 foreach (StaffPhotoEntity img in staffPhoto) { // 缩略图 img.Thumbnail = CommonModuleLogic.CommonModuleLogic.ConvertThumbnail(img.Photo); sbSql.Clear(); sbSql.Append("Insert into TP_HR_STAFFPHOTO"); sbSql.Append("( StaffID,Thumbnail,Photo,AccountID,"); sbSql.Append("CreateUserID,UpdateUserID)"); sbSql.Append(" VALUES(:StaffID,:Thumbnail,:Photo,:AccountID,"); sbSql.Append(":CreateUserID,:UpdateUserID)"); OracleParameter[] imgParas = new OracleParameter[] { new OracleParameter(":StaffID",OracleDbType.Int32, id,ParameterDirection.Input), new OracleParameter(":Thumbnail",OracleDbType.Blob, img.Thumbnail,ParameterDirection.Input), new OracleParameter(":Photo",OracleDbType.Blob, img.Photo,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), }; oracleTrConn.ExecuteNonQuery(sbSql.ToString(), imgParas); } #endregion oracleTrConn.Commit(); oracleTrConn.Disconnect(); return resultEnity; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } } } ///

/// 导入员工档案 ///

/// 员工信息 /// 用户基本信息 /// ServiceResultEntity public static ServiceResultEntity ImportStaffInfo(DataTable staffInfo, SUserInfo sUserInfo) { IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); //StringBuilder staffCodes = new StringBuilder(); //foreach (DataRow item in staffInfo.Rows) //{ // staffCodes.Append("'" + item["员工编码"] + "',"); //} //string sql = "select StaffCode from TP_HR_Staff where StaffCode in (" // + staffCodes.ToString(0, staffCodes.Length - 1) + ")"; //DataTable existsCode = oracleTrConn.GetSqlResultToDt(sql); // if (existsCode != null && existsCode.Rows.Count > 0) // { // ServiceResultEntity sre = new ServiceResultEntity(); // sre.Status = Constant.ServiceResultStatus.Other; // staffCodes.Clear(); // staffCodes = new StringBuilder(); // foreach (DataRow item in existsCode.Rows) // { // staffCodes.Append(item["StaffCode"] + ","); // } // sre.Message = "系统中已存在以下员工编码:" + System.Environment.NewLine // + staffCodes.ToString(0, staffCodes.Length - 1); // return sre; // } string sql = "select StaffCode from TP_HR_Staff where StaffCode = '{0}'"; List staffCodes = new List(); string nextval = "select SEQ_HR_STAFF_STAFFID.nextval from dual"; #region 向员工档案表插入数据 string sqlString = " INSERT INTO TP_HR_STAFF " + "(StaffID,StaffCode" + ",StaffName" + ",IDCardNo" + ",Birthday" + ",Gender" + ",MaritalStatus" + ",HomeTown" + ",PolicitalStatus" + ",National" + ",Educational" + ",Graduated" + ",SpecialField" + ",Telephone" + ",Height" + ",BloodGroup" + ",Weight" + ",Address" + ",LaidOff" + ",Disability" + ",JoinPartyDate" + ",Email" + ",OpeningBank" + ",AccountNo" + ",Remarks" + ",AccountID" + ",ValueFlag" + ",CreateUserID" + ",UpdateUserID" + ",CreateTime" + ",UpdateTime" + ",OrganizationID" + ",Jobs" + ",Post" + ",StaffStatus" + ",EntryDate" + ")" + " VALUES" + " (:StaffID" + " ,:StaffCode" + ",:StaffName" + ",:IDCardNo" + ",:Birthday" + ",:Gender" + ",:MaritalStatus" + ",:HomeTown" + ",:PolicitalStatus" + ",:National" + ",:Educational" + ",:Graduated" + ",:SpecialField" + ",:Telephone" + ",:Height" + ",:BloodGroup" + ",:Weight" + ",:Address" + ",:LaidOff" + ",:Disability" + ",:JoinPartyDate" + ",:Email" + ",:OpeningBank" + ",:AccountNo" + ",:Remarks" + ",:AccountID" + ",'1'" + ",:CreateUserID" + ",:CreateUserID" + ",sysdate" + ",sysdate" + ",:OrganizationID" + ",:Jobs" + ",:Post" + ",:StaffStatus" + ",decode(:StaffStatus, 0, null, trunc(sysdate))" + " )"; List ids = new List(); foreach (DataRow item in staffInfo.Rows) { // 已存在不影响其他导入 chenxy 2019-10-14 object objCode = oracleTrConn.GetSqlResultToObj(string.Format(sql, item["员工编码"])); if (!string.IsNullOrEmpty(objCode + "")) { staffCodes.Add(objCode + ""); continue; } int id = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(nextval)); ids.Add(id); OracleParameter[] parmeters = new OracleParameter[] { new OracleParameter(":StaffID",id), new OracleParameter(":StaffCode",item["员工编码"]), new OracleParameter(":StaffName",item["员工姓名"]), new OracleParameter(":IDCardNo",item["身份证号码"]), new OracleParameter(":Birthday", OracleDbType.Date, item["生日"], ParameterDirection.Input ), new OracleParameter(":Gender",OracleDbType.Char, item["性别"], ParameterDirection.Input ), new OracleParameter(":MaritalStatus",OracleDbType.Int32, int.Parse(item["婚姻状况"].ToString()), ParameterDirection.Input ), new OracleParameter(":HomeTown",OracleDbType.NVarchar2, item["籍贯"], ParameterDirection.Input ), new OracleParameter(":PolicitalStatus",OracleDbType.NVarchar2, item["政治面貌"], ParameterDirection.Input ), new OracleParameter(":National",OracleDbType.Int32, int.Parse(item["民族"].ToString()), ParameterDirection.Input ), new OracleParameter(":Educational",OracleDbType.Int32,int.Parse( item["学历"].ToString()), ParameterDirection.Input ), new OracleParameter(":Graduated",OracleDbType.NVarchar2, item["毕业学校"], ParameterDirection.Input ), new OracleParameter(":SpecialField",OracleDbType.NVarchar2, item["专业"], ParameterDirection.Input ), new OracleParameter(":Telephone",OracleDbType.NVarchar2, item["联系电话"], ParameterDirection.Input ), new OracleParameter(":Height",OracleDbType.Decimal, item["身高(CM)"], ParameterDirection.Input ), new OracleParameter(":BloodGroup",OracleDbType.NVarchar2, item["血型"], ParameterDirection.Input ), new OracleParameter(":Weight",OracleDbType.Decimal, item["体重(KG)"], ParameterDirection.Input ), new OracleParameter(":Address",OracleDbType.NVarchar2, item["家庭住址"], ParameterDirection.Input ), new OracleParameter(":LaidOff",OracleDbType.Char, item["下岗职工"], ParameterDirection.Input ), new OracleParameter(":Disability",OracleDbType.Char, item["残疾职工"], ParameterDirection.Input ), new OracleParameter(":JoinPartyDate",OracleDbType.Date, item["入党日期"], ParameterDirection.Input ), new OracleParameter(":Email",OracleDbType.NVarchar2, item["电子邮箱"], ParameterDirection.Input ), new OracleParameter(":OpeningBank",OracleDbType.NVarchar2, item["开户行"], ParameterDirection.Input ), new OracleParameter(":AccountNo",OracleDbType.NVarchar2, item["开户账号"], ParameterDirection.Input ), new OracleParameter(":Remarks",OracleDbType.NVarchar2, item["备注"], ParameterDirection.Input ), new OracleParameter(":AccountID",sUserInfo.AccountID), new OracleParameter(":CreateUserID",sUserInfo.UserID), new OracleParameter(":OrganizationID",OracleDbType.Int32, item["部门(全称)"].ToString()==""?null:item["部门(全称)"], ParameterDirection.Input ), new OracleParameter(":Jobs",OracleDbType.Int32, item["工种"].ToString()==""?null:item["工种"], ParameterDirection.Input ), new OracleParameter(":Post",OracleDbType.Int32, item["职务"].ToString()==""?null:item["职务"], ParameterDirection.Input ), new OracleParameter(":StaffStatus",OracleDbType.Int32, item["StaffStatus"], ParameterDirection.Input ), }; oracleTrConn.ExecuteNonQuery(sqlString, parmeters); } #endregion oracleTrConn.Commit(); oracleTrConn.Disconnect(); ServiceResultEntity result = new ServiceResultEntity(); if (staffCodes.Count > 0) { result.Status = Constant.ServiceResultStatus.Other; result.Message = "系统中已存在以下员工编码（其他已导入）:" + System.Environment.NewLine + string.Join(",", staffCodes); return result; } result.Status = Constant.ServiceResultStatus.Success; result.Result = string.Join(",", ids); return result; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } } } ///

/// 更新员工档案 ///

/// 员工实体类 /// 用户基本信息 /// 员工图片集 /// HRResultEntity public static HRResultEntity EditStaffInfo(StaffEntity staffInfo, SUserInfo sUserInfo, List staffPhoto) { HRResultEntity resultEnity = new HRResultEntity(); if (staffInfo == null) { resultEnity.OperateStatus = 0; return resultEnity; } IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); #region 验证员工档案表时间戳 string sql = "SELECT OPTimeStamp FROM tp_hr_staff" + " WHERE Staffid = " + staffInfo.StaffID + " and OPTimeStamp = :OPTimeStamp"; OracleParameter[] parmetersSql = new OracleParameter[] { new OracleParameter(":OPTimeStamp", OracleDbType.TimeStamp, staffInfo.OPTimeStamp, ParameterDirection.Input), }; DataSet returnDataset = oracleTrConn.GetSqlResultToDs(sql, parmetersSql); if (returnDataset != null && returnDataset.Tables[0].Rows.Count == 0) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -3; return resultEnity; } #endregion #region 更新员工档案表中的数据 string sqlString = " UPDATE TP_HR_STAFF SET" + " StaffCode=:StaffCode" + ",StaffName=:StaffName" + ",IDCardNo=:IDCardNo" + ",Birthday=:Birthday" + ",Gender=:Gender" + ",MaritalStatus=:MaritalStatus" + ",HomeTown=:HomeTown" + ",PolicitalStatus=:PolicitalStatus" + ",National=:National" + ",Educational=:Educational" + ",Graduated=:Graduated" + ",SpecialField=:SpecialField" + ",Telephone=:Telephone" + ",Height=:Height" + ",BloodGroup=:BloodGroup" + ",Weight=:Weight" + ",Address=:Address" + ",LaidOff=:LaidOff" + ",Disability=:Disability" + ",JoinPartyDate=:JoinPartyDate" + ",Email=:Email" + ",OpeningBank=:OpeningBank" + ",AccountNo=:AccountNo" + ",Remarks=:Remarks" + ",ValueFlag=:ValueFlag" + ",UpdateUserID=:UpdateUserID" + ",OrganizationID=:OrganizationID" + ",Post=:Post" + ",Jobs=:Jobs" + " where staffid=" + staffInfo.StaffID; OracleParameter[] parmeters = new OracleParameter[] { new OracleParameter(":StaffCode",staffInfo.StaffCode), new OracleParameter(":StaffName",staffInfo.StaffName), new OracleParameter(":IDCardNo",staffInfo.IDCardNo), new OracleParameter(":Birthday",staffInfo.Birthday), new OracleParameter(":Gender",staffInfo.Gender), new OracleParameter(":MaritalStatus",staffInfo.MaritalStatus), new OracleParameter(":HomeTown",staffInfo.HomeTown), new OracleParameter(":PolicitalStatus",staffInfo.PolicitalStatus), new OracleParameter(":National",staffInfo.National), new OracleParameter(":Educational",staffInfo.Educational), new OracleParameter(":Graduated",staffInfo.Graduated), new OracleParameter(":SpecialField",staffInfo.SpecialField), new OracleParameter(":Telephone",staffInfo.Telephone), new OracleParameter(":Height",staffInfo.Height), new OracleParameter(":BloodGroup",staffInfo.BloodGroup), new OracleParameter(":Weight",staffInfo.Weight), new OracleParameter(":Address",staffInfo.Address), new OracleParameter(":LaidOff",staffInfo.LaidOff?1:0), new OracleParameter(":Disability",staffInfo.Disability?1:0), new OracleParameter(":JoinPartyDate",staffInfo.JoinPartyDate), new OracleParameter(":Email",staffInfo.Email), new OracleParameter(":OpeningBank",staffInfo.OpeningBank), new OracleParameter(":AccountNo",staffInfo.AccountNo), new OracleParameter(":Remarks",staffInfo.Remarks), new OracleParameter(":ValueFlag",staffInfo.ValueFlag?1:0), new OracleParameter(":UpdateUserID",sUserInfo.UserID), new OracleParameter(":OrganizationID",staffInfo.OrganizationID), new OracleParameter(":Post",staffInfo.Post), new OracleParameter(":Jobs",staffInfo.Jobs), }; #endregion resultEnity.OperateStatus = oracleTrConn.ExecuteNonQuery(sqlString, parmeters); resultEnity.HRStaffID = staffInfo.StaffID; #region 更新员工照片表数据注释掉 /* StringBuilder sbSql = new StringBuilder(); foreach (StaffPhotoEntity img in staffPhoto) { sbSql.Clear(); string sqlExist = "SELECT 1 FROM TP_HR_STAFFPHOTO" + " WHERE StaffPhotoID = " + img.StaffPhotoID + " and AccountID = :AccountID"; OracleParameter[] parmetersExist = new OracleParameter[] { new OracleParameter(":AccountID",sUserInfo.AccountID), }; DataSet returnDSexist = oracleTrConn.GetSqlResultToDs(sqlExist, parmetersExist); if (returnDSexist != null && returnDSexist.Tables[0].Rows.Count == 0) { string sqlExistStaff = "SELECT 1 FROM TP_HR_STAFFPHOTO" + " WHERE StaffID = " + staffInfo.StaffID + " and AccountID = :AccountID"; OracleParameter[] parmetersExistStaff = new OracleParameter[] { new OracleParameter(":AccountID",sUserInfo.AccountID), }; DataSet returnDSexistStaff = oracleTrConn.GetSqlResultToDs(sqlExistStaff, parmetersExistStaff); if (returnDSexistStaff != null && returnDSexistStaff.Tables[0].Rows.Count == 0) { sbSql.Append("Insert into TP_HR_STAFFPHOTO"); sbSql.Append("(StaffID,Thumbnail,Photo,AccountID,ValueFlag,CreateTime,"); sbSql.Append("CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp)"); sbSql.Append(" VALUES(:StaffID,:Thumbnail,:Photo,:AccountID,:ValueFlag,:CreateTime,"); sbSql.Append(":CreateUserID,:UpdateTime,:UpdateUserID,:OPTimeStamp)"); OracleParameter[] imgParas = new OracleParameter[] { new OracleParameter(":StaffID",OracleDbType.Int32, staffInfo.StaffID,ParameterDirection.Input), new OracleParameter(":Thumbnail",OracleDbType.Blob, img.Thumbnail,ParameterDirection.Input), new OracleParameter(":Photo",OracleDbType.Blob, img.Photo,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":ValueFlag",OracleDbType.Int32, img.ValueFlag,ParameterDirection.Input), new OracleParameter(":CreateTime",OracleDbType.Date, DateTime.Now,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateTime",OracleDbType.Date, DateTime.Now,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp, DateTime.Now,ParameterDirection.Input) }; oracleTrConn.ExecuteNonQuery(sbSql.ToString(), imgParas); } else { sbSql.Append(" update TP_HR_STAFFPHOTO"); sbSql.Append(" set Thumbnail=:Thumbnail,Photo=:Photo,AccountID=:AccountID,ValueFlag=:ValueFlag,"); sbSql.Append(" UpdateUserID=:UpdateUserID"); sbSql.Append(" where StaffID=:StaffID"); OracleParameter[] imgParasStaff = new OracleParameter[] { new OracleParameter(":Thumbnail",OracleDbType.Blob, img.Thumbnail,ParameterDirection.Input), new OracleParameter(":Photo",OracleDbType.Blob, img.Photo,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":ValueFlag",OracleDbType.Int32, img.ValueFlag,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":StaffID",OracleDbType.Int32, staffInfo.StaffID,ParameterDirection.Input),}; oracleTrConn.ExecuteNonQuery(sbSql.ToString(), imgParasStaff); } } else { sbSql.Clear(); sbSql.Append(" update TP_HR_STAFFPHOTO"); sbSql.Append(" set StaffID=:StaffID,Thumbnail=:Thumbnail,Photo=:Photo,AccountID=:AccountID,ValueFlag=:ValueFlag,"); sbSql.Append(" UpdateUserID=:UpdateUserID"); sbSql.Append(" where STAFFPHOTOID=:STAFFPHOTOID"); OracleParameter[] imgParas = new OracleParameter[] { new OracleParameter(":StaffID",OracleDbType.Int32, staffInfo.StaffID,ParameterDirection.Input), new OracleParameter(":Thumbnail",OracleDbType.Blob, img.Thumbnail,ParameterDirection.Input), new OracleParameter(":Photo",OracleDbType.Blob, img.Photo,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":ValueFlag",OracleDbType.Int32, img.ValueFlag,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":STAFFPHOTOID",OracleDbType.Int32, img.StaffPhotoID,ParameterDirection.Input) }; oracleTrConn.ExecuteNonQuery(sbSql.ToString(), imgParas); } } */ #endregion #region 先删除图片 string strDel = "delete TP_HR_STAFFPHOTO where StaffID=:staffID"; OracleParameter[] imgParas = new OracleParameter[] { new OracleParameter(":staffID",OracleDbType.Int32, staffInfo.StaffID,ParameterDirection.Input), }; oracleTrConn.ExecuteNonQuery(strDel, imgParas); #endregion #region 向TP_HR_STAFFPHOTO插入数据 StringBuilder sbSql = new StringBuilder(); //此处添加图片信息 foreach (StaffPhotoEntity img in staffPhoto) { // 缩略图 img.Thumbnail = CommonModuleLogic.CommonModuleLogic.ConvertThumbnail(img.Photo); sbSql.Clear(); sbSql.Append("Insert into TP_HR_STAFFPHOTO"); sbSql.Append("( StaffID,Thumbnail,Photo,AccountID,"); sbSql.Append("CreateUserID,UpdateUserID)"); sbSql.Append(" VALUES(:StaffID,:Thumbnail,:Photo,:AccountID,"); sbSql.Append(":CreateUserID,:UpdateUserID)"); imgParas = new OracleParameter[] { new OracleParameter(":StaffID",OracleDbType.Int32, staffInfo.StaffID,ParameterDirection.Input), new OracleParameter(":Thumbnail",OracleDbType.Blob, img.Thumbnail,ParameterDirection.Input), new OracleParameter(":Photo",OracleDbType.Blob, img.Photo,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), }; oracleTrConn.ExecuteNonQuery(sbSql.ToString(), imgParas); } #endregion oracleTrConn.Commit(); oracleTrConn.Disconnect(); return resultEnity; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } } } ///

/// 设置禁用员工 ///

/// 员工ID /// 用户基本信息 /// HRResultEntity public static HRResultEntity SetValueFlag(int staffid, SUserInfo sUserInfo) { HRResultEntity resultEnity = new HRResultEntity(); IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); string sql = "SELECT Count(*) FROM TP_HR_STAFF WHERE staffid=" + staffid + " AND ValueFlag=1"; string strCount = oracleTrConn.GetSqlResultToStr(sql); if (!"0".Equals(strCount)) { string sqlString = "Update TP_HR_STAFF SET ValueFlag=0,UpdateUserID =" + sUserInfo.UserID + " WHERE staffid=" + staffid; resultEnity.OperateStatus = oracleTrConn.ExecuteNonQuery(sqlString); //string sqlString2 = "Select StaffRecordID From TP_HR_StaffRecord " // + " Where ApprovalStatus = 0 AND RecordType = 1 And ValueFlag = 1 And StaffID = " + staffid; //string strStaffRecordID = oracleTrConn.GetSqlResultToStr(sqlString2); //if (!string.IsNullOrEmpty(strStaffRecordID)) //{ string sqlString3 = "Update TP_HR_StaffRecord Set ValueFlag = 0,UpdateUserID = " + sUserInfo.UserID + " Where ApprovalStatus = 0 AND RecordType = 1 And ValueFlag = 1 And StaffID = " + staffid; oracleTrConn.ExecuteNonQuery(sqlString3); //} oracleTrConn.Commit(); oracleTrConn.Disconnect(); return resultEnity; } else { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -4; return resultEnity; } } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } } #endregion #region 员工入职 ///

/// 添加员工履历 ///

/// 员工履历实体类 /// 用户基本信息 /// HRResultEntity public static HRResultEntity AddStaffRecord(StaffRecordEntity staffrecord, SUserInfo sUserInfo) { HRResultEntity resultEnity = new HRResultEntity(); if (staffrecord == null) { resultEnity.OperateStatus = 0; return resultEnity; } if (staffrecord.ExProbationEndDate != null) { // 取得最近一次工资结算日 DateTime wageDate = CommonModuleLogic.CommonModuleLogic.GetSystemDate(Constant.SystemDateType.WageSettlementDate, sUserInfo); if (staffrecord.ExProbationEndDate.Value <= wageDate) { resultEnity.OperateStatus = Constant.INT_IS_NEGATIE_FOUR; return resultEnity; } } IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); //查询此员工是否有待审批的履历 string sql = "SELECT 1 FROM TP_HR_STAFFRECORD" + " WHERE Staffid =:Staffid and RecordType = 1 And ValueFlag = 1 and ApprovalStatus=0"; OracleParameter[] parmetersSql = new OracleParameter[] { new OracleParameter(":Staffid", OracleDbType.Int32, staffrecord.StaffID , ParameterDirection.Input), }; DataSet returnDataset = oracleTrConn.GetSqlResultToDs(sql, parmetersSql); if (returnDataset != null && returnDataset.Tables[0].Rows.Count > 0) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -1; return resultEnity;//不允许在添加,此员工有待审批的履历 } // 查询新插入的生产数据ID string strSeq = oracleTrConn.GetSqlResultToStr("Select SEQ_HR_STAFFRECORD_ID.nextval from dual"); #region 插入员工履历数据 string sqlString = " INSERT INTO TP_HR_STAFFRECORD " + "(StaffRecordID,StaffID" + ",StaffCode" + ",RecordDate" + ",RecordType" + ",ExProbationEndDate" + ",OriginalStaffStatus" + ",OriginalJobs" + ",OriginalOrganizationID" + ",OriginalPost" + ",TargetStaffStatus" + ",TargetJobs" + ",TargetOrganizationID" + ",TargetPost" + ",Applicant" + ",Reason" + ",Suggestion" + ",ApprovalStatus" + ",Approver" + ",ApprovalDate" + ",Remarks" + ",AccountID" + ",ValueFlag" + ",CreateUserID" + ",UpdateUserID" + ")" + " VALUES " + " (:StaffRecordID,:StaffID" + ",:StaffCode" + ",sysdate" + ",:RecordType" + ",:ExProbationEndDate" + ",:OriginalStaffStatus" + ",:OriginalJobs" + ",:OriginalOrganizationID" + ",:OriginalPost" + ",:TargetStaffStatus" + ",:TargetJobs" + ",:TargetOrganizationID" + ",:TargetPost" + ",:Applicant" + ",:Reason" + ",:Suggestion" + ",:ApprovalStatus" + ",:Approver" + ",FUN_CMN_GetAccountDate(:ACCOUNTID)" + ",:Remarks" + ",:AccountID" + ",:ValueFlag" + ",:CreateUserID" + ",:UpdateUserID" + " )"; OracleParameter[] parmeters = new OracleParameter[] { new OracleParameter(":StaffRecordID",OracleDbType.Int32,Convert.ToInt32(strSeq),ParameterDirection.Input), new OracleParameter(":StaffID",OracleDbType.Int32,staffrecord.StaffID,ParameterDirection.Input), new OracleParameter(":StaffCode",OracleDbType.Varchar2,staffrecord.StaffCode,ParameterDirection.Input), new OracleParameter(":RecordType",OracleDbType.Int32,staffrecord.RecordType,ParameterDirection.Input), new OracleParameter(":ExProbationEndDate",OracleDbType.Date,staffrecord.ExProbationEndDate,ParameterDirection.Input), new OracleParameter(":OriginalStaffStatus",OracleDbType.Int32,staffrecord.OriginalStaffStatus,ParameterDirection.Input), new OracleParameter(":OriginalJobs",OracleDbType.Int32,staffrecord.OriginalJobs,ParameterDirection.Input), new OracleParameter(":OriginalOrganizationID",OracleDbType.Int32,staffrecord.OriginalOrganizationID,ParameterDirection.Input), new OracleParameter(":OriginalPost",OracleDbType.Int32,staffrecord.OriginalPost,ParameterDirection.Input), new OracleParameter(":TargetStaffStatus",OracleDbType.Int32,staffrecord.TargetStaffStatus,ParameterDirection.Input), new OracleParameter(":TargetJobs",OracleDbType.Int32,staffrecord.TargetJobs,ParameterDirection.Input), new OracleParameter(":TargetOrganizationID",OracleDbType.Int32,staffrecord.TargetOrganizationID,ParameterDirection.Input), new OracleParameter(":TargetPost",OracleDbType.Int32,staffrecord.TargetPost,ParameterDirection.Input), new OracleParameter(":Applicant",OracleDbType.Int32,staffrecord.Applicant,ParameterDirection.Input), new OracleParameter(":Reason",OracleDbType.Varchar2,staffrecord.Reason,ParameterDirection.Input), new OracleParameter(":Suggestion",OracleDbType.Varchar2,staffrecord.Suggestion,ParameterDirection.Input), new OracleParameter(":ApprovalStatus",OracleDbType.Int32,staffrecord.ApprovalStatus,ParameterDirection.Input), new OracleParameter(":Approver",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":Remarks",OracleDbType.Varchar2,staffrecord.Remarks,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":ValueFlag",OracleDbType.Int32,staffrecord.ValueFlag,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), }; #endregion resultEnity.OperateStatus = oracleTrConn.ExecuteNonQuery(sqlString, parmeters); resultEnity.HRStaffRecordID = Convert.ToInt32(strSeq); #region 更新员工档案的试用期时间 string strSql2 = "Update TP_HR_Staff Set ExProbationEndDate = :pExProbationEndDate WHERE StaffID = :pStaffID"; OracleParameter[] parmetersSql2 = new OracleParameter[] { new OracleParameter(":pExProbationEndDate", OracleDbType.Date, staffrecord.ExProbationEndDate, ParameterDirection.Input), new OracleParameter(":pStaffID", OracleDbType.Int32, staffrecord.StaffID , ParameterDirection.Input) }; foreach (var itemNull in parmetersSql2) { if (string.IsNullOrEmpty(itemNull.Value + "")) { itemNull.Value = DBNull.Value; } } oracleTrConn.ExecuteNonQuery(strSql2, parmetersSql2); #endregion if (staffrecord.ApprovalStatus == 3) //审批通过 { #region 更新数据到员工档案表 string sqlString2 = " UPDATE TP_HR_STAFF SET " + "StaffStatus=" + staffrecord.TargetStaffStatus //试用 + ",EntryDate=FUN_CMN_GetAccountDate(:pACCOUNTID)" + ",UpdateUserID=:pUpdateUserID" + ",OrganizationID=:OrganizationID" + ",Post=:Post" + ",Jobs=:Jobs" + ",TurnoverDate=NULL" + " Where StaffID=:pStaffID"; OracleParameter[] parmeters2 = new OracleParameter[] { new OracleParameter(":pACCOUNTID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":pUpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":pStaffID",OracleDbType.Int32,staffrecord.StaffID,ParameterDirection.Input), new OracleParameter(":OrganizationID",OracleDbType.Int32,staffrecord.TargetOrganizationID,ParameterDirection.Input), new OracleParameter(":Post",OracleDbType.Int32,staffrecord.TargetPost,ParameterDirection.Input), new OracleParameter(":Jobs",OracleDbType.Int32,staffrecord.TargetJobs,ParameterDirection.Input), }; #endregion resultEnity.OperateStatus += oracleTrConn.ExecuteNonQuery(sqlString2, parmeters2); if (staffrecord.TargetStaffStatus == 1) { #region 如果试用，往试用表里插入数据 string sqlString3 = "INSERT INTO TP_HR_StaffProbation(" + "StaffID" + ",JobsID" + ",BeginDate" + ",AccountID" + ",CreateUserID" + ",UpdateUserID) VALUES (" + ":StaffID" + ",:JobsID" + ",sysdate" + ",:AccountID" + ",:CreateUserID" + ",:UpdateUserID)"; OracleParameter[] parmeters3 = new OracleParameter[] { new OracleParameter(":StaffID",OracleDbType.Int32,staffrecord.StaffID,ParameterDirection.Input), new OracleParameter(":JobsID",OracleDbType.Int32,staffrecord.TargetJobs,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), }; resultEnity.OperateStatus += oracleTrConn.ExecuteNonQuery(sqlString3, parmeters3); #endregion } } oracleTrConn.Commit(); oracleTrConn.Disconnect(); return resultEnity; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } } } ///

/// 编辑员工履历 ///

/// 员工履历实体类 /// 用户基本信息 /// HRResultEntity public static HRResultEntity EditStaffRecord(StaffRecordEntity staffrecord, SUserInfo sUserInfo) { HRResultEntity resultEnity = new HRResultEntity(); if (staffrecord == null) { resultEnity.OperateStatus = 0; return resultEnity; } if (staffrecord.ExProbationEndDate != null) { // 取得最近一次工资结算日 DateTime wageDate = CommonModuleLogic.CommonModuleLogic.GetSystemDate(Constant.SystemDateType.WageSettlementDate, sUserInfo); if (staffrecord.ExProbationEndDate.Value <= wageDate) { resultEnity.OperateStatus = Constant.INT_IS_NEGATIE_FOUR; return resultEnity; } } IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); string sql = "SELECT OPTimeStamp FROM TP_HR_STAFFRECORD" + " WHERE StaffRecordID = " + staffrecord.StaffRecordID + " and OPTimeStamp = :OPTimeStamp"; OracleParameter[] parmetersSql = new OracleParameter[] { new OracleParameter(":OPTimeStamp", OracleDbType.TimeStamp, staffrecord.OPTimeStamp, ParameterDirection.Input), }; DataSet returnDataset = oracleTrConn.GetSqlResultToDs(sql, parmetersSql); if (returnDataset != null && returnDataset.Tables[0].Rows.Count == 0) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -2; return resultEnity; } #region 更新员工履历信息 string sqlString = " UPDATE TP_HR_STAFFRECORD SET " + "StaffID=:StaffID" + ",StaffCode=:StaffCode" + ",ExProbationEndDate=:ExProbationEndDate" + ",OriginalStaffStatus=:OriginalStaffStatus" + ",OriginalJobs=:OriginalJobs" + ",OriginalOrganizationID=:OriginalOrganizationID" + ",OriginalPost=:OriginalPost" + ",TargetStaffStatus=:TargetStaffStatus" + ",TargetJobs=:TargetJobs" + ",TargetOrganizationID=:TargetOrganizationID" + ",TargetPost=:TargetPost" + ",Reason=:Reason" + ",Suggestion=:Suggestion" + ",UpdateUserID=:UpdateUserID" + ",Applicant=:Applicant" + ",Remarks=:Remarks" + " Where StaffRecordID=:StaffRecordID"; OracleParameter[] parmeters = new OracleParameter[] { new OracleParameter(":StaffID",OracleDbType.Int32,staffrecord.StaffID,ParameterDirection.Input), new OracleParameter(":StaffCode",OracleDbType.Varchar2,staffrecord.StaffCode,ParameterDirection.Input), new OracleParameter(":ExProbationEndDate",OracleDbType.Date,staffrecord.ExProbationEndDate,ParameterDirection.Input), new OracleParameter(":OriginalStaffStatus",OracleDbType.Int32,staffrecord.OriginalStaffStatus,ParameterDirection.Input), new OracleParameter(":OriginalJobs",OracleDbType.Int32,staffrecord.OriginalJobs,ParameterDirection.Input), new OracleParameter(":OriginalOrganizationID",OracleDbType.Int32,staffrecord.OriginalOrganizationID,ParameterDirection.Input), new OracleParameter(":OriginalPost",OracleDbType.Int32,staffrecord.OriginalPost,ParameterDirection.Input), new OracleParameter(":TargetStaffStatus",OracleDbType.Int32,staffrecord.TargetStaffStatus,ParameterDirection.Input), new OracleParameter(":TargetJobs",OracleDbType.Int32,staffrecord.TargetJobs,ParameterDirection.Input), new OracleParameter(":TargetOrganizationID",OracleDbType.Int32,staffrecord.TargetOrganizationID,ParameterDirection.Input), new OracleParameter(":TargetPost",OracleDbType.Int32,staffrecord.TargetPost,ParameterDirection.Input), new OracleParameter(":Reason",OracleDbType.Varchar2,staffrecord.Reason,ParameterDirection.Input), new OracleParameter(":Suggestion",OracleDbType.Varchar2,staffrecord.Suggestion,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":StaffRecordID",OracleDbType.Int32,staffrecord.StaffRecordID,ParameterDirection.Input), new OracleParameter(":Applicant",OracleDbType.Int32,staffrecord.Applicant,ParameterDirection.Input), new OracleParameter(":Remarks",OracleDbType.Varchar2,staffrecord.Remarks,ParameterDirection.Input), }; #endregion resultEnity.OperateStatus = oracleTrConn.ExecuteNonQuery(sqlString, parmeters); resultEnity.HRStaffRecordID = staffrecord.StaffRecordID; #region 更新员工档案的试用期时间 string strSql2 = "Update TP_HR_Staff Set ExProbationEndDate = :pExProbationEndDate WHERE StaffID = :pStaffID"; OracleParameter[] parmetersSql2 = new OracleParameter[] { new OracleParameter(":pExProbationEndDate", OracleDbType.Date, staffrecord.ExProbationEndDate, ParameterDirection.Input), new OracleParameter(":pStaffID", OracleDbType.Int32, staffrecord.StaffID , ParameterDirection.Input) }; foreach (var itemNull in parmetersSql2) { if (string.IsNullOrEmpty(itemNull.Value + "")) { itemNull.Value = DBNull.Value; } } oracleTrConn.ExecuteNonQuery(strSql2, parmetersSql2); #endregion oracleTrConn.Commit(); oracleTrConn.Disconnect(); return resultEnity; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } } } #endregion #region 员工离职 ///

/// 保存员工离职履历信息 ///

/// 员工编号 /// 员工档案表时间戳 /// 员工履历表实体 /// 用户基本信息 /// 窗体是新增还是编辑 /// 0 没有数据被修改 -1员工档案被其他用户修改 -2存在待审批履历 -3员工履历被其他用户修改 public static HRResultEntity SaveStaffRecordDimission(int pUserId, DateTime pOPTimeStamp, StaffRecordEntity pStaffRecord, SUserInfo sUserInfo, WCFConstant.FormMode pStatus) { HRResultEntity resultEnity = new HRResultEntity(); if (pStaffRecord == null) { resultEnity.OperateStatus = 0; return resultEnity; } IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); #region 员工档案被其他用户修改 string sql1 = "SELECT * FROM TP_HR_Staff" + " WHERE Staffid =:Staffid And OPTimeStamp= :opTimeStamp"; OracleParameter[] parmetersSql1 = new OracleParameter[] { new OracleParameter(":Staffid", OracleDbType.Int32, pUserId , ParameterDirection.Input), new OracleParameter(":opTimeStamp", OracleDbType.TimeStamp, pOPTimeStamp , ParameterDirection.Input) }; DataTable dtStaff = oracleTrConn.GetSqlResultToDt(sql1, parmetersSql1); if (dtStaff == null || dtStaff.Rows.Count <= 0) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -3; return resultEnity;// 员工档案被其他用户修改 } #endregion if (pStatus == WCFConstant.FormMode.Add) { #region 新增加记录 #region 查询此员工是否有待审批的履历 string sql2 = "SELECT Count(*) FROM TP_HR_STAFFRECORD" + " WHERE Staffid =:Staffid And ApprovalStatus=0 And ValueFlag = 1"; OracleParameter[] parmetersSql2 = new OracleParameter[] { new OracleParameter(":Staffid", OracleDbType.Int32, pUserId , ParameterDirection.Input), }; string strCount = oracleTrConn.GetSqlResultToStr(sql2, parmetersSql2); if (strCount != "0") { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -1; return resultEnity;//不允许在添加,此员工有待审批的履历 } #endregion // 查询新插入的生产数据ID string strSeq = oracleTrConn.GetSqlResultToStr("Select SEQ_HR_STAFFRECORD_ID.nextval from dual"); #region 向员工履历表插入数据 string sqlString = " INSERT INTO TP_HR_STAFFRECORD " + "(StaffRecordID,StaffID" + ",StaffCode" + ",RecordDate" + ",RecordType" + ",OriginalStaffStatus" + ",OriginalJobs" + ",OriginalOrganizationID" + ",OriginalPost" + ",TargetStaffStatus" + ",TargetJobs" + ",TargetOrganizationID" + ",TargetPost" + ",Applicant" + ",Reason" + ",Suggestion" + ",ApprovalStatus" + ",Approver" + ",ApprovalDate" + ",Remarks" + ",AccountID" + ",ValueFlag" + ",CreateUserID" + ",UpdateUserID" + ")" + " VALUES " + " (:StaffRecordID,:StaffID" + ",:StaffCode" + ",:RecordDate" + ",:RecordType" + ",:OriginalStaffStatus" + ",:OriginalJobs" + ",:OriginalOrganizationID" + ",:OriginalPost" + ",:TargetStaffStatus" + ",:TargetJobs" + ",:TargetOrganizationID" + ",:TargetPost" + ",:Applicant" + ",:Reason" + ",:Suggestion" + ",:ApprovalStatus" + ",:Approver" + ",FUN_CMN_GetAccountDate(:ACCOUNTID)" + ",:Remarks" + ",:AccountID" + ",:ValueFlag" + ",:CreateUserID" + ",:UpdateUserID" + " )"; OracleParameter[] parmeters = new OracleParameter[] { new OracleParameter(":StaffRecordID",OracleDbType.Int32,Convert.ToInt32(strSeq),ParameterDirection.Input), new OracleParameter(":StaffID",OracleDbType.Int32,pStaffRecord.StaffID,ParameterDirection.Input), new OracleParameter(":StaffCode",OracleDbType.Varchar2,dtStaff.Rows[0]["StaffCode"].ToString(),ParameterDirection.Input), new OracleParameter(":RecordDate",OracleDbType.Date,pStaffRecord.RecordDate,ParameterDirection.Input), new OracleParameter(":RecordType",OracleDbType.Int32,6,ParameterDirection.Input), new OracleParameter(":OriginalStaffStatus",OracleDbType.Int32,Convert.ToInt32(dtStaff.Rows[0]["StaffStatus"].ToString()),ParameterDirection.Input), new OracleParameter(":OriginalJobs",OracleDbType.Int32,Convert.ToInt32(dtStaff.Rows[0]["Jobs"].ToString()),ParameterDirection.Input), new OracleParameter(":OriginalOrganizationID",OracleDbType.Int32,Convert.ToInt32(dtStaff.Rows[0]["OrganizationID"].ToString()),ParameterDirection.Input), new OracleParameter(":OriginalPost",OracleDbType.Int32,Convert.ToInt32(dtStaff.Rows[0]["Post"].ToString()),ParameterDirection.Input), new OracleParameter(":TargetStaffStatus",OracleDbType.Int32,Convert.ToInt32(dtStaff.Rows[0]["StaffStatus"].ToString()),ParameterDirection.Input), new OracleParameter(":TargetJobs",OracleDbType.Int32,Convert.ToInt32(dtStaff.Rows[0]["Jobs"].ToString()),ParameterDirection.Input), new OracleParameter(":TargetOrganizationID",OracleDbType.Int32,Convert.ToInt32(dtStaff.Rows[0]["OrganizationID"].ToString()),ParameterDirection.Input), new OracleParameter(":TargetPost",OracleDbType.Int32,Convert.ToInt32(dtStaff.Rows[0]["Post"].ToString()),ParameterDirection.Input), new OracleParameter(":Applicant",OracleDbType.Int32,pStaffRecord.Applicant,ParameterDirection.Input), new OracleParameter(":Reason",OracleDbType.Varchar2,pStaffRecord.Reason,ParameterDirection.Input), new OracleParameter(":Suggestion",OracleDbType.Varchar2,pStaffRecord.Suggestion,ParameterDirection.Input), new OracleParameter(":ApprovalStatus",OracleDbType.Int32,pStaffRecord.ApprovalStatus,ParameterDirection.Input), new OracleParameter(":Approver",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), //new OracleParameter(":ACCOUNTID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":Remarks",OracleDbType.Varchar2,pStaffRecord.Remarks,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":ValueFlag",OracleDbType.Int32,1,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), }; #endregion resultEnity.OperateStatus = oracleTrConn.ExecuteNonQuery(sqlString, parmeters); #endregion resultEnity.HRStaffRecordID = Convert.ToInt32(strSeq); } else if (pStatus == WCFConstant.FormMode.Edit) { #region 编辑记录 #region 员工履历被其他用户修改 string sql2 = "SELECT Count(*) FROM TP_HR_STAFFRECORD" + " WHERE StaffRecordID = " + pStaffRecord.StaffRecordID + " And OPTimeStamp = :OPTimeStamp"; OracleParameter[] parmetersSql2 = new OracleParameter[] { new OracleParameter(":OPTimeStamp", OracleDbType.TimeStamp, pStaffRecord.OPTimeStamp, ParameterDirection.Input), }; string strCount = oracleTrConn.GetSqlResultToStr(sql2, parmetersSql2); if ("0".Equals(strCount)) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -2; return resultEnity; //员工履历被其他用户修改 } #endregion #region 更新数据到员工履历表 string sqlString = " UPDATE TP_HR_STAFFRECORD SET " + "RecordDate=:RecordDate" + ",Applicant=:Applicant" + ",Reason=:Reason" + ",Suggestion=:Suggestion" + ",Remarks = :Remarks" + ",UpdateUserID=:UpdateUserID" + " Where StaffRecordID=:StaffRecordID"; OracleParameter[] parmeters = new OracleParameter[] { new OracleParameter(":RecordDate",OracleDbType.Date,pStaffRecord.RecordDate,ParameterDirection.Input), new OracleParameter(":Applicant",OracleDbType.Int32,pStaffRecord.Applicant,ParameterDirection.Input), new OracleParameter(":Reason",OracleDbType.Varchar2,pStaffRecord.Reason,ParameterDirection.Input), new OracleParameter(":Suggestion",OracleDbType.Varchar2,pStaffRecord.Suggestion,ParameterDirection.Input), new OracleParameter(":Remarks",OracleDbType.Varchar2,pStaffRecord.Remarks,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":StaffRecordID",OracleDbType.Int32,pStaffRecord.StaffRecordID,ParameterDirection.Input), }; #endregion resultEnity.OperateStatus = oracleTrConn.ExecuteNonQuery(sqlString, parmeters); resultEnity.HRStaffRecordID = pStaffRecord.StaffRecordID; #endregion } #region 更新数据到员工档案表 // 如果审批通过，直接更新员工为离职状态 if (pStaffRecord.ApprovalStatus == 3) { string sqlString2 = " UPDATE TP_HR_STAFF SET " + "StaffStatus=3" + ",TurnoverDate=FUN_CMN_GetAccountDate(:pACCOUNTID)" + ",UpdateUserID=:pUpdateUserID" + " Where StaffID=:pStaffID"; OracleParameter[] parmeters2 = new OracleParameter[] { new OracleParameter(":pACCOUNTID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":pUpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":pStaffID",OracleDbType.Int32,pUserId,ParameterDirection.Input), }; resultEnity.OperateStatus += oracleTrConn.ExecuteNonQuery(sqlString2, parmeters2); } #endregion oracleTrConn.Commit(); oracleTrConn.Disconnect(); return resultEnity; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } } ///

/// 员工离职操作 ///

/// /// /// public static ServiceResultEntity StaffTurnover(ClientRequestEntity cre, SUserInfo sUserInfo) { IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { ServiceResultEntity sre = new ServiceResultEntity(); string staffIDs = cre.Properties["staffIDs"] + ""; if (staffIDs == null || staffIDs.Length == 0) { sre.Status = Constant.ServiceResultStatus.Other; return sre; } if (cre.Data == null || cre.Data.Tables.Count == 0 || cre.Data.Tables[0].Rows.Count == 0) { sre.Message = "无员工信息"; return null; } DataTable dtStaff = cre.Data.Tables[0]; oracleTrConn.Connect(); #region 更新员工状态为离职 string sqlString = @" UPDATE TP_HR_Staff SET StaffStatus = 3, TurnoverDate = SYSDATE WHERE StaffID IN ("+ staffIDs + ")"; int returnRows = oracleTrConn.ExecuteNonQuery(sqlString); #endregion #region 向员工履历表插入数据 // 新建个履历实体 StaffRecordEntity pStaffRecord = new StaffRecordEntity(); pStaffRecord.RecordDate = DateTime.Now; // 履历日期取今天 pStaffRecord.Applicant = sUserInfo.UserID; // 申请人取操作者 pStaffRecord.Reason = "-"; // 原因为空 pStaffRecord.Suggestion = "-"; // 意见为空 pStaffRecord.ApprovalStatus = 3; // 直接审批通过 pStaffRecord.Remarks = "一键离职"; // 备注一键离职 int returnStaffRecordRows = 0; sqlString = " INSERT INTO TP_HR_STAFFRECORD " + "(StaffRecordID,StaffID" + ",StaffCode" + ",RecordDate" + ",RecordType" + ",OriginalStaffStatus" + ",OriginalJobs" + ",OriginalOrganizationID" + ",OriginalPost" + ",TargetStaffStatus" + ",TargetJobs" + ",TargetOrganizationID" + ",TargetPost" + ",Applicant" + ",Reason" + ",Suggestion" + ",ApprovalStatus" + ",Approver" + ",ApprovalDate" + ",Remarks" + ",AccountID" + ",ValueFlag" + ",CreateUserID" + ",UpdateUserID" + ")" + " VALUES " + " (:StaffRecordID,:StaffID" + ",:StaffCode" + ",:RecordDate" + ",:RecordType" + ",:OriginalStaffStatus" + ",:OriginalJobs" + ",:OriginalOrganizationID" + ",:OriginalPost" + ",:TargetStaffStatus" + ",:TargetJobs" + ",:TargetOrganizationID" + ",:TargetPost" + ",:Applicant" + ",:Reason" + ",:Suggestion" + ",:ApprovalStatus" + ",:Approver" + ",FUN_CMN_GetAccountDate(:ACCOUNTID)" + ",:Remarks" + ",:AccountID" + ",:ValueFlag" + ",:CreateUserID" + ",:UpdateUserID" + " )"; foreach (DataRow row in dtStaff.Rows) { string strSeq = oracleTrConn.GetSqlResultToStr("Select SEQ_HR_STAFFRECORD_ID.nextval from dual"); OracleParameter[] parmeters = new OracleParameter[] { new OracleParameter(":StaffRecordID",OracleDbType.Int32,Convert.ToInt32(strSeq),ParameterDirection.Input), new OracleParameter(":StaffID",OracleDbType.Int32,Convert.ToInt32(row["staffID"]),ParameterDirection.Input), new OracleParameter(":StaffCode",OracleDbType.Varchar2,dtStaff.Rows[0]["StaffCode"].ToString(),ParameterDirection.Input), new OracleParameter(":RecordDate",OracleDbType.Date,pStaffRecord.RecordDate,ParameterDirection.Input), new OracleParameter(":RecordType",OracleDbType.Int32,6,ParameterDirection.Input), new OracleParameter(":OriginalStaffStatus",OracleDbType.Int32,Convert.ToInt32(dtStaff.Rows[0]["StaffStatus"].ToString()),ParameterDirection.Input), new OracleParameter(":OriginalJobs",OracleDbType.Int32,Convert.ToInt32(dtStaff.Rows[0]["Jobs"].ToString()),ParameterDirection.Input), new OracleParameter(":OriginalOrganizationID",OracleDbType.Int32,Convert.ToInt32(dtStaff.Rows[0]["OrganizationID"].ToString()),ParameterDirection.Input), new OracleParameter(":OriginalPost",OracleDbType.Int32,Convert.ToInt32(dtStaff.Rows[0]["Post"].ToString()),ParameterDirection.Input), new OracleParameter(":TargetStaffStatus",OracleDbType.Int32,3,ParameterDirection.Input), new OracleParameter(":TargetJobs",OracleDbType.Int32,Convert.ToInt32(dtStaff.Rows[0]["Jobs"].ToString()),ParameterDirection.Input), new OracleParameter(":TargetOrganizationID",OracleDbType.Int32,Convert.ToInt32(dtStaff.Rows[0]["OrganizationID"].ToString()),ParameterDirection.Input), new OracleParameter(":TargetPost",OracleDbType.Int32,Convert.ToInt32(dtStaff.Rows[0]["Post"].ToString()),ParameterDirection.Input), new OracleParameter(":Applicant",OracleDbType.Int32,pStaffRecord.Applicant,ParameterDirection.Input), new OracleParameter(":Reason",OracleDbType.Varchar2,pStaffRecord.Reason,ParameterDirection.Input), new OracleParameter(":Suggestion",OracleDbType.Varchar2,pStaffRecord.Suggestion,ParameterDirection.Input), new OracleParameter(":ApprovalStatus",OracleDbType.Int32,pStaffRecord.ApprovalStatus,ParameterDirection.Input), new OracleParameter(":Approver",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), //new OracleParameter(":ACCOUNTID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":Remarks",OracleDbType.Varchar2,pStaffRecord.Remarks,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":ValueFlag",OracleDbType.Int32,1,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), }; returnStaffRecordRows = oracleTrConn.ExecuteNonQuery(sqlString, parmeters); } #endregion oracleTrConn.Commit(); oracleTrConn.Disconnect(); if (returnRows > 0 && returnStaffRecordRows > 0) { sre.Status = Constant.ServiceResultStatus.Success; } return sre; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } } } ///

/// 导入员工离职 ///

/// /// /// public static ServiceResultEntity ImportStaffTurnover(ClientRequestEntity cre, SUserInfo sUserInfo) { IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { ServiceResultEntity sre = new ServiceResultEntity(); if (cre.Data == null || cre.Data.Tables.Count == 0 || cre.Data.Tables[0].Rows.Count == 0) { sre.Message = "无员工信息"; return null; } DataTable staffInfo = cre.Data.Tables[0]; staffInfo.Columns.Add("StaffID",typeof(int)); oracleTrConn.Connect(); #region 验证员工编码是否都存在 StringBuilder staffCodes = new StringBuilder(); foreach (DataRow item in staffInfo.Rows) { string sql = @"SELECT StaffID FROM TP_HR_Staff WHERE StaffStatus <> 3 AND ValueFlag = 1 AND StaffCode = '" + item["员工编码"] + "'"; DataTable dtExistsID = oracleTrConn.GetSqlResultToDt(sql); if (dtExistsID != null && dtExistsID.Rows.Count > 0) { item["StaffID"] = dtExistsID.Rows[0][0]; } else { item["StaffID"] = DBNull.Value; staffCodes.Append(item["员工编码"] + ","); } } // 无效员工不处理前台提示 chenxy 2019-10-14 //if (staffCodes != null && staffCodes.Length > 0) //{ // sre.Status = Constant.ServiceResultStatus.Other; // sre.Message = "以下员工编码在系统中不存在或已离职或停用:" + System.Environment.NewLine // + staffCodes.ToString(0, staffCodes.Length - 1); // return sre; //} #endregion #region 更新员工状态为离职 int returnRows = 0; DateTime turnoverDate; string sqlString = string.Empty; OracleParameter[] parmeters = null; foreach (DataRow item in staffInfo.Rows) { // 无效员工不处理前台提示 chenxy 2019-10-14 if (item["StaffID"] == DBNull.Value) { continue; } if (!DateTime.TryParse(item["离职日期"] + "", out turnoverDate)) { turnoverDate = DateTime.Now; } sqlString = @" UPDATE TP_HR_Staff SET StaffStatus = 3, TurnoverDate = :TurnoverDate WHERE StaffID = :StaffID"; parmeters = new OracleParameter[] { new OracleParameter(":TurnoverDate",OracleDbType.Date, turnoverDate, ParameterDirection.Input ), new OracleParameter(":StaffID",OracleDbType.Int32,item["StaffID"], ParameterDirection.Input) }; returnRows += oracleTrConn.ExecuteNonQuery(sqlString, parmeters); sre.Result += item["StaffID"] + ","; } sre.Result = (sre.Result + "").Substring(0, (sre.Result + "").Length - 1); #endregion #region 向员工履历表插入数据 //// 新建个履历实体 StaffRecordEntity pStaffRecord = new StaffRecordEntity(); pStaffRecord.RecordDate = DateTime.Now; // 履历日期取今天 pStaffRecord.Applicant = sUserInfo.UserID; // 申请人取操作者 pStaffRecord.Reason = "-"; // 原因为空 pStaffRecord.Suggestion = "-"; // 意见为空 pStaffRecord.ApprovalStatus = 3; // 直接审批通过 pStaffRecord.Remarks = "一键导入离职"; // 备注一键离职 int returnStaffRecordRows = 0; sqlString = "INSERT INTO TP_HR_STAFFRECORD(\n" + " StaffRecordID\n" + ",StaffID\n" + ",StaffCode\n" + ",RecordDate\n" + ",RecordType\n" + ",OriginalStaffStatus\n" + ",OriginalJobs\n" + ",OriginalOrganizationID\n" + ",OriginalPost\n" + ",TargetStaffStatus\n" + ",TargetJobs\n" + ",TargetOrganizationID\n" + ",TargetPost\n" + ",Applicant\n" + ",Reason\n" + ",Suggestion\n" + ",ApprovalStatus\n" + ",Approver\n" + ",ApprovalDate\n" + ",Remarks\n" + ",AccountID\n" + ",ValueFlag\n" + ",CreateUserID\n" + ",UpdateUserID)\n" + " SELECT SEQ_HR_STAFFRECORD_ID.nextval\n" + ",StaffID\n" + ",StaffCode\n" + ",SYSDATE\n" + ",:RecordType\n" + ",StaffStatus\n" + ",Jobs\n" + ",OrganizationID\n" + ",POST\n" + ",:TargetStaffStatus\n" + ",Jobs\n" + ",OrganizationID\n" + ",POST\n" + ",:Applicant\n" + ",:Reason\n" + ",:Suggestion\n" + ",:ApprovalStatus\n" + ",:Approver\n" + ",SYSDATE\n" + ",:Remarks\n" + ",:AccountID\n" + ",:ValueFlag\n" + ",:CreateUserID\n" + ",:UpdateUserID\n" + " FROM TP_HR_STAFF\n" + " WHERE StaffID IN ("+ sre.Result + ")"; parmeters = new OracleParameter[] { new OracleParameter(":RecordType",OracleDbType.Int32,6,ParameterDirection.Input), new OracleParameter(":TargetStaffStatus",OracleDbType.Int32,3,ParameterDirection.Input), new OracleParameter(":Applicant",OracleDbType.Int32,pStaffRecord.Applicant,ParameterDirection.Input), new OracleParameter(":Reason",OracleDbType.Varchar2,pStaffRecord.Reason,ParameterDirection.Input), new OracleParameter(":Suggestion",OracleDbType.Varchar2,pStaffRecord.Suggestion,ParameterDirection.Input), new OracleParameter(":ApprovalStatus",OracleDbType.Int32,pStaffRecord.ApprovalStatus,ParameterDirection.Input), new OracleParameter(":Approver",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":Remarks",OracleDbType.Varchar2,pStaffRecord.Remarks,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":ValueFlag",OracleDbType.Int32,1,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input) }; returnStaffRecordRows = oracleTrConn.ExecuteNonQuery(sqlString, parmeters); #endregion oracleTrConn.Commit(); oracleTrConn.Disconnect(); // 无效员工不处理前台提示 chenxy 2019-10-14 if (staffCodes != null && staffCodes.Length > 0) { sre.Status = Constant.ServiceResultStatus.Other; sre.Message = "以下员工编码在系统中不存在或已离职或停用（其他已导入）:" + System.Environment.NewLine + staffCodes.ToString(0, staffCodes.Length - 1); return sre; } if (returnRows > 0 && returnStaffRecordRows > 0) { sre.Status = Constant.ServiceResultStatus.Success; } return sre; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } } } #endregion #region 工种调整 ///

/// 保存工种调整方法； ///

/// 员工履历实体类 /// 用户基本信息 /// 状态 1：新增 2：保存 /// 0 没有数据被修改 -1存在待审批履历 -2员工履历被其他用户修改 -3员工档案被其他用户修改 public static HRResultEntity SaveTargetJobInfo(StaffRecordEntity staffRecordEntity, SUserInfo sUserInfo, WCFConstant.FormMode editStatus) { HRResultEntity resultEnity = new HRResultEntity(); IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); #region 判断时间戳 string sqlString1 = "SELECT Count(*) FROM TP_HR_Staff" + " WHERE AccountID = :AccountID AND StaffID = :staffID"; // AND OPTimeStamp = :oPTimeStamp "; OracleParameter[] parmeters1 = new OracleParameter[] { new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":staffID",OracleDbType.Int32,staffRecordEntity.StaffID,ParameterDirection.Input), //new OracleParameter(":oPTimeStamp",OracleDbType.TimeStamp, staffRecordEntity.OPTimeStamp,ParameterDirection.Input), }; DataTable dtStaff = oracleTrConn.GetSqlResultToDt(sqlString1, parmeters1); if (dtStaff == null || dtStaff.Rows.Count <= 0) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -3; return resultEnity;// 员工档案被其他用户修改 } #endregion // 新建工种调整信息 if (editStatus == WCFConstant.FormMode.Add) { #region 验证员工是否存在未审批数据 sqlString1 = "SELECT Count(*) FROM TP_HR_StaffRecord " + " WHERE AccountID = :AccountID and ApprovalStatus in(0,1) AND StaffID = :staffID And ValueFlag = 1"; parmeters1 = new OracleParameter[] { new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":staffID",OracleDbType.Int32,staffRecordEntity.StaffID,ParameterDirection.Input) }; string strCount = oracleTrConn.GetSqlResultToStr(sqlString1, parmeters1); if (strCount != "0") { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -1; return resultEnity;//员工档案存在未审批数据 } #endregion // string strSeq = oracleTrConn.GetSqlResultToStr("Select SEQ_HR_STAFFRECORD_ID.nextval from dual"); #region 新增工种调整信息 sqlString1 = "INSERT INTO TP_HR_StaffRecord " + "(StaffRecordID,StaffID,StaffCode,RecordDate,RecordType,ExProbationEndDate,ApprovalStatus,Approver,ApprovalDate" + ",OriginalOrganizationID,OriginalStaffStatus,OriginalJobs" + ",TargetOrganizationID,TargetStaffStatus,TargetJobs" + ",Applicant,Reason,Suggestion,Remarks" + ",AccountID,CreateTime,UpdateTime,UpdateUserID,CreateUserID)" + " VALUES " + "(:StaffRecordID,:staffID,:staffCode,:recordDate,:recordType,:exProbationEndDate,:approvalStatus,:Approver,FUN_CMN_GetAccountDate(:ACCOUNTID)" + ",:originalOrganizationID,:originalStaffStatus,:originalJobs" + ",:targetOrganizationID,:targetStaffStatus,:targetJobs" + ",:applicant,:reason,:suggestion,:remarks" + ",:AccountID,sysdate,sysdate,:UpdateUserID,:CreateUserID)"; parmeters1 = new OracleParameter[] { new OracleParameter(":StaffRecordID",OracleDbType.Int32,Convert.ToInt32(strSeq),ParameterDirection.Input), new OracleParameter(":staffID",OracleDbType.Int32,staffRecordEntity.StaffID,ParameterDirection.Input), new OracleParameter(":staffCode",OracleDbType.Varchar2,staffRecordEntity.StaffCode,ParameterDirection.Input), new OracleParameter(":recordDate",OracleDbType.Date,staffRecordEntity.RecordDate,ParameterDirection.Input), new OracleParameter(":recordType",OracleDbType.Int32,staffRecordEntity.RecordType,ParameterDirection.Input), new OracleParameter(":exProbationEndDate",OracleDbType.Date,staffRecordEntity.ExProbationEndDate,ParameterDirection.Input), new OracleParameter(":approvalStatus",OracleDbType.Int32,staffRecordEntity.ApprovalStatus,ParameterDirection.Input), new OracleParameter(":Approver",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":originalOrganizationID",OracleDbType.Int32,staffRecordEntity.OriginalOrganizationID,ParameterDirection.Input), new OracleParameter(":originalStaffStatus",OracleDbType.Int32,staffRecordEntity.OriginalStaffStatus,ParameterDirection.Input), new OracleParameter(":originalJobs",OracleDbType.Int32,staffRecordEntity.OriginalJobs,ParameterDirection.Input), new OracleParameter(":targetOrganizationID",OracleDbType.Int32,staffRecordEntity.TargetOrganizationID,ParameterDirection.Input), new OracleParameter(":targetStaffStatus",OracleDbType.Int32,staffRecordEntity.TargetStaffStatus,ParameterDirection.Input), new OracleParameter(":targetJobs",OracleDbType.Int32,staffRecordEntity.TargetJobs,ParameterDirection.Input), new OracleParameter(":applicant",OracleDbType.Int32,staffRecordEntity.Applicant,ParameterDirection.Input), new OracleParameter(":reason",OracleDbType.Varchar2,staffRecordEntity.Reason,ParameterDirection.Input), new OracleParameter(":suggestion",OracleDbType.Varchar2,staffRecordEntity.Suggestion,ParameterDirection.Input), new OracleParameter(":remarks",OracleDbType.Varchar2,staffRecordEntity.Remarks,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input) }; #endregion resultEnity.OperateStatus = oracleTrConn.ExecuteNonQuery(sqlString1, parmeters1); resultEnity.HRStaffRecordID = Convert.ToInt32(strSeq); }//修改工种调整信息 else if (editStatus == WCFConstant.FormMode.Edit) { #region 员工履历被其他用户修改 sqlString1 = "SELECT Count(*) FROM TP_HR_STAFFRECORD" + " WHERE StaffRecordID = :staffRecordID And OPTimeStamp = :OPTimeStamp"; parmeters1 = new OracleParameter[] { new OracleParameter(":staffRecordID",OracleDbType.Int32,staffRecordEntity.StaffRecordID,ParameterDirection.Input), new OracleParameter(":OPTimeStamp", OracleDbType.TimeStamp, staffRecordEntity.OPTimeStamp, ParameterDirection.Input) }; string strCount = oracleTrConn.GetSqlResultToStr(sqlString1, parmeters1); if ("0".Equals(strCount)) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -2; return resultEnity; //员工履历被其他用户修改 } #endregion #region 更新工种调整信息 string sqlString = "UPDATE TP_HR_StaffRecord SET " + "RecordDate = :recordDate" + ",TargetJobs = :targetJobs" + ",TargetStaffStatus = :targetStaffStatus" + ",ExProbationEndDate = :exProbationEndDate" + ",Applicant = :applicant" + ",Reason = :reason" + ",Suggestion = :suggestion" + ",Remarks = :remarks" + ",UpdateUserID = :UpdateUserID" + ",UpdateTime = sysdate" + " WHERE StaffRecordID = :staffRecordID"; parmeters1 = new OracleParameter[] { new OracleParameter(":recordDate",OracleDbType.Date,staffRecordEntity.RecordDate,ParameterDirection.Input), new OracleParameter(":targetJobs",OracleDbType.Int32,staffRecordEntity.TargetJobs,ParameterDirection.Input), new OracleParameter(":targetStaffStatus",OracleDbType.Int32,staffRecordEntity.TargetStaffStatus,ParameterDirection.Input), new OracleParameter(":exProbationEndDate",OracleDbType.Date,staffRecordEntity.ExProbationEndDate,ParameterDirection.Input), new OracleParameter(":applicant",OracleDbType.Int32,staffRecordEntity.Applicant,ParameterDirection.Input), new OracleParameter(":reason",OracleDbType.Varchar2,staffRecordEntity.Reason,ParameterDirection.Input), new OracleParameter(":suggestion",OracleDbType.Varchar2,staffRecordEntity.Suggestion,ParameterDirection.Input), new OracleParameter(":remarks",OracleDbType.Varchar2,staffRecordEntity.Remarks,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":staffRecordID",OracleDbType.Int32,staffRecordEntity.StaffRecordID,ParameterDirection.Input) }; #endregion resultEnity.OperateStatus = oracleTrConn.ExecuteNonQuery(sqlString, parmeters1); resultEnity.HRStaffRecordID = staffRecordEntity.StaffRecordID; } #region 更新员工档案表中的预计试用期结束日期 string strSql2 = "Update TP_HR_Staff Set ExProbationEndDate = :pExProbationEndDate WHERE StaffID = :pStaffID"; OracleParameter[] parmetersSql2 = new OracleParameter[] { new OracleParameter(":pExProbationEndDate", OracleDbType.Date, staffRecordEntity.ExProbationEndDate, ParameterDirection.Input), new OracleParameter(":pStaffID", OracleDbType.Int32, staffRecordEntity.StaffID , ParameterDirection.Input) }; foreach (var itemNull in parmetersSql2) { if (string.IsNullOrEmpty(itemNull.Value + "")) { itemNull.Value = DBNull.Value; } } oracleTrConn.ExecuteNonQuery(strSql2, parmetersSql2); #endregion #region 更新数据到员工档案表 if (staffRecordEntity.ApprovalStatus == 3) { string sqlString2 = " UPDATE TP_HR_STAFF SET " + "Jobs = :jobs" + ",StaffStatus=:staffStatus" + ",ExProbationEndDate=:exProbationEndDate" + ",UpdateUserID=:pUpdateUserID" + " Where StaffID=:pStaffID"; OracleParameter[] parmeters2 = new OracleParameter[] { new OracleParameter(":jobs",OracleDbType.Int32,staffRecordEntity.TargetJobs,ParameterDirection.Input), new OracleParameter(":staffStatus",OracleDbType.Int32,staffRecordEntity.TargetStaffStatus,ParameterDirection.Input), new OracleParameter(":exProbationEndDate", OracleDbType.Date, staffRecordEntity.ExProbationEndDate, ParameterDirection.Input), new OracleParameter(":pUpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":pStaffID",OracleDbType.Int32,staffRecordEntity.StaffID,ParameterDirection.Input), }; resultEnity.OperateStatus += oracleTrConn.ExecuteNonQuery(sqlString2, parmeters2); if (staffRecordEntity.ExProbationEndDate != null) { #region 如果试用，往试用表里插入数据 string sqlString3 = "INSERT INTO TP_HR_StaffProbation(" + "StaffID" + ",JobsID" + ",BeginDate" + ",AccountID" + ",CreateUserID" + ",UpdateUserID) VALUES (" + ":StaffID" + ",:JobsID" + ",sysdate" + ",:AccountID" + ",:CreateUserID" + ",:UpdateUserID)"; OracleParameter[] parmeters3 = new OracleParameter[] { new OracleParameter(":StaffID",OracleDbType.Int32,staffRecordEntity.StaffID,ParameterDirection.Input), new OracleParameter(":JobsID",OracleDbType.Int32,staffRecordEntity.TargetJobs,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), }; resultEnity.OperateStatus += oracleTrConn.ExecuteNonQuery(sqlString3, parmeters3); #endregion } } #endregion oracleTrConn.Commit(); oracleTrConn.Disconnect(); return resultEnity; } catch (Exception ex) { throw ex; } finally { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } } } ///

/// 工种调整审批保存 ///

/// 员工编号 /// 员工档案表时间戳 /// 员工履历表实体 /// 审批状态 True 通过 False 不通过 /// 审批意见 /// 用户基本信息 /// 0 没有数据被修改 -1员工档案被其他用户修改 -2存在待审批履历 -3员工履历被其他用户修改 public static HRResultEntity SaveHRStaffJobsApprovalInfo(int pUserId, DateTime pOPTimeStamp, StaffRecordEntity pStaffRecord, bool pState, string pMemo, SUserInfo sUserInfo) { HRResultEntity resultEnity = new HRResultEntity(); IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); #region 员工档案被其他用户修改 string sql1 = "SELECT * FROM TP_HR_Staff" + " WHERE Staffid =:Staffid And OPTimeStamp= :opTimeStamp"; OracleParameter[] parmetersSql1 = new OracleParameter[] { new OracleParameter(":Staffid", OracleDbType.Int32, pUserId , ParameterDirection.Input), new OracleParameter(":opTimeStamp", OracleDbType.TimeStamp, pOPTimeStamp , ParameterDirection.Input) }; DataTable dtStaff = oracleTrConn.GetSqlResultToDt(sql1, parmetersSql1); if (dtStaff == null || dtStaff.Rows.Count <= 0) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -3; return resultEnity;// 员工档案被其他用户修改 } #endregion #region 员工履历被其他用户修改 string sql2 = "SELECT * FROM TP_HR_STAFFRECORD" + " WHERE StaffRecordID = :staffRecordID And OPTimeStamp = :oPTimeStamp"; OracleParameter[] parmetersSql2 = new OracleParameter[] { new OracleParameter(":staffRecordID", OracleDbType.Int32, pStaffRecord.StaffRecordID, ParameterDirection.Input), new OracleParameter(":oPTimeStamp", OracleDbType.TimeStamp, pStaffRecord.OPTimeStamp, ParameterDirection.Input), }; DataTable dtStaffRecord = oracleTrConn.GetSqlResultToDt(sql2, parmetersSql2); if (dtStaffRecord == null || dtStaffRecord.Rows.Count <= 0) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -2; return resultEnity;// 员工履历被其他用户修改 } #endregion //保存数据到员工履历表和人事审批表 resultEnity.OperateStatus += UpdateHRApprovalInfo(dtStaff, dtStaffRecord, pState, pMemo, sUserInfo, oracleTrConn); if (pState) { #region 更新数据到员工档案表 string sqlString2 = " UPDATE TP_HR_STAFF SET " + "Jobs = :jobs" + ",StaffStatus=:staffStatus" + ",ExProbationEndDate=:exProbationEndDate" + ",UpdateUserID=:pUpdateUserID" + " Where StaffID=:pStaffID"; OracleParameter[] parmeters2 = new OracleParameter[] { new OracleParameter(":jobs",OracleDbType.Int32,pStaffRecord.TargetJobs,ParameterDirection.Input), new OracleParameter(":staffStatus",OracleDbType.Int32,pStaffRecord.TargetStaffStatus,ParameterDirection.Input), new OracleParameter(":exProbationEndDate", OracleDbType.Date, pStaffRecord.ExProbationEndDate, ParameterDirection.Input), new OracleParameter(":pUpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":pStaffID",OracleDbType.Int32,pUserId,ParameterDirection.Input), }; #endregion resultEnity.OperateStatus += oracleTrConn.ExecuteNonQuery(sqlString2, parmeters2); if (pStaffRecord.ExProbationEndDate != null) { #region 如果试用，往试用表里插入数据 string sqlString3 = "INSERT INTO TP_HR_StaffProbation(" + "StaffID" + ",JobsID" + ",BeginDate" + ",AccountID" + ",CreateUserID" + ",UpdateUserID) VALUES (" + ":StaffID" + ",:JobsID" + ",sysdate" + ",:AccountID" + ",:CreateUserID" + ",:UpdateUserID)"; OracleParameter[] parmeters3 = new OracleParameter[] { new OracleParameter(":StaffID",OracleDbType.Int32,pUserId,ParameterDirection.Input), new OracleParameter(":JobsID",OracleDbType.Int32,pStaffRecord.TargetJobs,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), }; resultEnity.OperateStatus += oracleTrConn.ExecuteNonQuery(sqlString3, parmeters3); #endregion } } oracleTrConn.Commit(); oracleTrConn.Disconnect(); return resultEnity; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } } } #endregion #region 职务调整 ///

/// 保存职务调整方法 ///

/// 员工履历实体类 /// 用户基本信息 /// 状态 1：新增 2：保存 /// 0 没有数据被修改 -1存在待审批履历 -2员工履历被其他用户修改 -3员工档案被其他用户修改 public static HRResultEntity SaveTargetPostInfo(StaffRecordEntity staffRecordEntity, SUserInfo sUserInfo, WCFConstant.FormMode editStatus) { HRResultEntity resultEnity = new HRResultEntity(); IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); #region 判断时间戳 string sqlString1 = "SELECT Count(*) FROM TP_HR_Staff" + " WHERE AccountID = :AccountID AND StaffID = :staffID AND OPTimeStamp = :oPTimeStamp "; OracleParameter[] parmeters1 = new OracleParameter[] { new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":staffID",OracleDbType.Int32,staffRecordEntity.StaffID,ParameterDirection.Input), new OracleParameter(":oPTimeStamp",OracleDbType.TimeStamp, staffRecordEntity.OPTimeStamp,ParameterDirection.Input), }; DataTable dtStaff = oracleTrConn.GetSqlResultToDt(sqlString1, parmeters1); if (dtStaff == null || dtStaff.Rows.Count <= 0) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -3; return resultEnity;// 员工档案被其他用户修改 } #endregion // 新建职务调整信息 if (editStatus == WCFConstant.FormMode.Add) { #region 验证员工是否存在未审批数据 sqlString1 = "SELECT Count(*) FROM TP_HR_StaffRecord " + " WHERE AccountID = :AccountID and ApprovalStatus in(0,1) AND StaffID = :staffID And ValueFlag = 1"; parmeters1 = new OracleParameter[] { new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":staffID",OracleDbType.Int32,staffRecordEntity.StaffID,ParameterDirection.Input) }; string strCount = oracleTrConn.GetSqlResultToStr(sqlString1, parmeters1); if (strCount != "0") { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -1; return resultEnity;//员工档案存在未审批数据 } #endregion // string strSeq = oracleTrConn.GetSqlResultToStr("Select SEQ_HR_STAFFRECORD_ID.nextval from dual"); #region 新增职务调整信息 sqlString1 = "INSERT INTO TP_HR_StaffRecord " + "(StaffRecordID,StaffID,StaffCode,RecordDate,RecordType,ExProbationEndDate,ApprovalStatus,Approver,ApprovalDate" + ",OriginalOrganizationID,OriginalStaffStatus,OriginalPost" + ",TargetOrganizationID,TargetStaffStatus,TargetPost" + ",Applicant,Reason,Suggestion,Remarks" + ",AccountID,CreateTime,UpdateTime,UpdateUserID,CreateUserID)" + " VALUES " + "(:StaffRecordID,:staffID,:staffCode,:recordDate,:recordType,:exProbationEndDate,:approvalStatus,:Approver,FUN_CMN_GetAccountDate(:ACCOUNTID)" + ",:originalOrganizationID,:originalStaffStatus,:originalPost" + ",:targetOrganizationID,:targetStaffStatus,:targetPost" + ",:applicant,:reason,:suggestion,:remarks" + ",:AccountID,sysdate,sysdate,:UpdateUserID,:CreateUserID)"; parmeters1 = new OracleParameter[] { new OracleParameter(":StaffRecordID",OracleDbType.Int32,Convert.ToInt32(strSeq),ParameterDirection.Input), new OracleParameter(":staffID",OracleDbType.Int32,staffRecordEntity.StaffID,ParameterDirection.Input), new OracleParameter(":staffCode",OracleDbType.Varchar2,staffRecordEntity.StaffCode,ParameterDirection.Input), new OracleParameter(":recordDate",OracleDbType.Date,staffRecordEntity.RecordDate,ParameterDirection.Input), new OracleParameter(":recordType",OracleDbType.Int32,staffRecordEntity.RecordType,ParameterDirection.Input), new OracleParameter(":exProbationEndDate",OracleDbType.Date,staffRecordEntity.ExProbationEndDate,ParameterDirection.Input), new OracleParameter(":approvalStatus",OracleDbType.Int32,staffRecordEntity.ApprovalStatus,ParameterDirection.Input), new OracleParameter(":Approver",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":originalOrganizationID",OracleDbType.Int32,staffRecordEntity.OriginalOrganizationID,ParameterDirection.Input), new OracleParameter(":originalStaffStatus",OracleDbType.Int32,staffRecordEntity.OriginalStaffStatus,ParameterDirection.Input), new OracleParameter(":originalPost",OracleDbType.Int32,staffRecordEntity.OriginalPost,ParameterDirection.Input), new OracleParameter(":targetOrganizationID",OracleDbType.Int32,staffRecordEntity.TargetOrganizationID,ParameterDirection.Input), new OracleParameter(":targetStaffStatus",OracleDbType.Int32,staffRecordEntity.TargetStaffStatus,ParameterDirection.Input), new OracleParameter(":targetPost",OracleDbType.Int32,staffRecordEntity.TargetPost,ParameterDirection.Input), new OracleParameter(":applicant",OracleDbType.Int32,staffRecordEntity.Applicant,ParameterDirection.Input), new OracleParameter(":reason",OracleDbType.Varchar2,staffRecordEntity.Reason,ParameterDirection.Input), new OracleParameter(":suggestion",OracleDbType.Varchar2,staffRecordEntity.Suggestion,ParameterDirection.Input), new OracleParameter(":remarks",OracleDbType.Varchar2,staffRecordEntity.Remarks,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input) }; #endregion resultEnity.OperateStatus = oracleTrConn.ExecuteNonQuery(sqlString1, parmeters1); resultEnity.HRStaffRecordID = Convert.ToInt32(strSeq); }//修改职务调整信息 else if (editStatus == WCFConstant.FormMode.Edit) { #region 员工履历被其他用户修改 sqlString1 = "SELECT Count(*) FROM TP_HR_STAFFRECORD" + " WHERE StaffRecordID = :staffRecordID And OPTimeStamp = :OPTimeStamp"; parmeters1 = new OracleParameter[] { new OracleParameter(":staffRecordID",OracleDbType.Int32,staffRecordEntity.StaffRecordID,ParameterDirection.Input), new OracleParameter(":OPTimeStamp", OracleDbType.TimeStamp, staffRecordEntity.OPTimeStamp, ParameterDirection.Input) }; string strCount = oracleTrConn.GetSqlResultToStr(sqlString1, parmeters1); if (strCount != "0") { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -2; return resultEnity; //员工履历被其他用户修改 } #endregion #region 更新职务调整信息 string sqlString = "UPDATE TP_HR_StaffRecord SET " + "RecordDate = :recordDate" + ",TargetPost = :targetPost" + ",Applicant = :applicant" + ",Reason = :reason" + ",Suggestion = :suggestion" + ",Remarks = :remarks" + ",UpdateUserID = :UpdateUserID" + ",UpdateTime = sysdate" + " WHERE StaffRecordID = :staffRecordID"; parmeters1 = new OracleParameter[] { new OracleParameter(":recordDate",OracleDbType.Date,staffRecordEntity.RecordDate,ParameterDirection.Input), new OracleParameter(":targetPost",OracleDbType.Int32,staffRecordEntity.TargetPost,ParameterDirection.Input), new OracleParameter(":applicant",OracleDbType.Int32,staffRecordEntity.Applicant,ParameterDirection.Input), new OracleParameter(":reason",OracleDbType.Varchar2,staffRecordEntity.Reason,ParameterDirection.Input), new OracleParameter(":suggestion",OracleDbType.Varchar2,staffRecordEntity.Suggestion,ParameterDirection.Input), new OracleParameter(":remarks",OracleDbType.Varchar2,staffRecordEntity.Remarks,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":staffRecordID",OracleDbType.Int32,staffRecordEntity.StaffRecordID,ParameterDirection.Input) }; #endregion resultEnity.OperateStatus = oracleTrConn.ExecuteNonQuery(sqlString, parmeters1); resultEnity.HRStaffRecordID = staffRecordEntity.StaffRecordID; } #region 更新数据到员工档案表 if (staffRecordEntity.ApprovalStatus == 3) //审批通过 { string sqlString2 = " UPDATE TP_HR_STAFF SET " + "Post = :post" + ",UpdateUserID=:pUpdateUserID" + " Where StaffID=:pStaffID"; OracleParameter[] parmeters2 = new OracleParameter[] { new OracleParameter(":post",OracleDbType.Int32,staffRecordEntity.TargetPost,ParameterDirection.Input), new OracleParameter(":pUpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":pStaffID",OracleDbType.Int32,staffRecordEntity.StaffID,ParameterDirection.Input), }; resultEnity.OperateStatus += oracleTrConn.ExecuteNonQuery(sqlString2, parmeters2); } #endregion oracleTrConn.Commit(); oracleTrConn.Disconnect(); return resultEnity; } catch (Exception ex) { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } } } ///

/// 职务调整审批保存 ///

/// 员工编号 /// 员工档案表时间戳 /// 员工履历表实体 /// 审批状态 True 通过 False 不通过 /// 审批意见 /// 用户基本信息 /// 0 没有数据被修改 -1员工档案被其他用户修改 -2存在待审批履历 -3员工履历被其他用户修改 public static HRResultEntity SaveHRStaffPostApprovalInfo(int pUserId, DateTime pOPTimeStamp, StaffRecordEntity pStaffRecord, bool pState, string pMemo, SUserInfo sUserInfo) { HRResultEntity resultEnity = new HRResultEntity(); IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); #region 员工档案被其他用户修改 string sql1 = "SELECT * FROM TP_HR_Staff" + " WHERE Staffid =:Staffid And OPTimeStamp= :opTimeStamp"; OracleParameter[] parmetersSql1 = new OracleParameter[] { new OracleParameter(":Staffid", OracleDbType.Int32, pUserId , ParameterDirection.Input), new OracleParameter(":opTimeStamp", OracleDbType.TimeStamp, pOPTimeStamp , ParameterDirection.Input) }; DataTable dtStaff = oracleTrConn.GetSqlResultToDt(sql1, parmetersSql1); if (dtStaff == null || dtStaff.Rows.Count <= 0) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -3; return resultEnity;// 员工档案被其他用户修改 } #endregion #region 员工履历被其他用户修改 string sql2 = "SELECT * FROM TP_HR_STAFFRECORD" + " WHERE StaffRecordID = :staffRecordID And OPTimeStamp = :oPTimeStamp"; OracleParameter[] parmetersSql2 = new OracleParameter[] { new OracleParameter(":staffRecordID", OracleDbType.Int32, pStaffRecord.StaffRecordID, ParameterDirection.Input), new OracleParameter(":oPTimeStamp", OracleDbType.TimeStamp, pStaffRecord.OPTimeStamp, ParameterDirection.Input), }; DataTable dtStaffRecord = oracleTrConn.GetSqlResultToDt(sql2, parmetersSql2); if (dtStaffRecord == null || dtStaffRecord.Rows.Count <= 0) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -2; return resultEnity;// 员工履历被其他用户修改 } #endregion //保存数据到员工履历表和人事审批表 resultEnity.OperateStatus += UpdateHRApprovalInfo(dtStaff, dtStaffRecord, pState, pMemo, sUserInfo, oracleTrConn); if (pState) //审批通过 { #region 更新数据到员工档案表 string sqlString2 = " UPDATE TP_HR_STAFF SET " + "Post = :post" + ",UpdateUserID=:pUpdateUserID" + " Where StaffID=:pStaffID"; OracleParameter[] parmeters2 = new OracleParameter[] { new OracleParameter(":post",OracleDbType.Int32,pStaffRecord.TargetPost,ParameterDirection.Input), new OracleParameter(":pUpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":pStaffID",OracleDbType.Int32,pUserId,ParameterDirection.Input), }; #endregion resultEnity.OperateStatus += oracleTrConn.ExecuteNonQuery(sqlString2, parmeters2); } oracleTrConn.Commit(); oracleTrConn.Disconnect(); return resultEnity; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } } } #endregion #region 部门调整 ///

/// 保存部门调整方法 ///

/// 员工履历实体类 /// 用户基本信息 /// 状态 1：新增 2：保存 /// 0 没有数据被修改 -1存在待审批履历 -2员工履历被其他用户修改 -3员工档案被其他用户修改 public static HRResultEntity SaveTargetOrganizationInfo(StaffRecordEntity staffRecordEntity, SUserInfo sUserInfo, WCFConstant.FormMode editStatus) { HRResultEntity resultEnity = new HRResultEntity(); IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); #region 判断时间戳 string sqlString1 = "SELECT Count(*) FROM TP_HR_Staff" + " WHERE AccountID = :AccountID AND StaffID = :staffID"; OracleParameter[] parmeters1 = new OracleParameter[] { new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":staffID",OracleDbType.Int32,staffRecordEntity.StaffID,ParameterDirection.Input), }; DataTable dtStaff = oracleTrConn.GetSqlResultToDt(sqlString1, parmeters1); if (dtStaff == null || dtStaff.Rows.Count <= 0) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -3; return resultEnity;// 员工档案被其他用户修改 } #endregion // 新建工种调整信息 if (editStatus == WCFConstant.FormMode.Add) { #region 验证员工是否存在未审批数据 sqlString1 = "SELECT Count(*) FROM TP_HR_StaffRecord " + " WHERE AccountID = :AccountID and ApprovalStatus in(0,1) AND StaffID = :staffID And ValueFlag = 1"; parmeters1 = new OracleParameter[] { new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":staffID",OracleDbType.Int32,staffRecordEntity.StaffID,ParameterDirection.Input) }; string strCount = oracleTrConn.GetSqlResultToStr(sqlString1, parmeters1); if (strCount != "0") { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -1; return resultEnity;//员工档案存在未审批数据 } #endregion // 查询新插入的生产数据ID string strSeq = oracleTrConn.GetSqlResultToStr("Select SEQ_HR_STAFFRECORD_ID.nextval from dual"); #region 新增部门调整信息 sqlString1 = "INSERT INTO TP_HR_StaffRecord " + "(StaffRecordID,StaffID,StaffCode,RecordDate,RecordType,ExProbationEndDate,ApprovalStatus,Approver,ApprovalDate" + ",OriginalOrganizationID,OriginalStaffStatus,OriginalJobs,OriginalPost" + ",TargetOrganizationID,TargetStaffStatus,TargetJobs,TargetPost" + ",Applicant,Reason,Suggestion,Remarks" + ",AccountID,CreateTime,UpdateTime,UpdateUserID,CreateUserID)" + " VALUES " + "(:StaffRecordID,:staffID,:staffCode,:recordDate,:recordType,:exProbationEndDate,:approvalStatus,:Approver,FUN_CMN_GetAccountDate(:ACCOUNTID)" + ",:originalOrganizationID,:originalStaffStatus,:originalJobs,:originalPost" + ",:targetOrganizationID,:targetStaffStatus,:targetJobs,:targetPost" + ",:applicant,:reason,:suggestion,:remarks" + ",:AccountID,sysdate,sysdate,:UpdateUserID,:CreateUserID)"; parmeters1 = new OracleParameter[] { new OracleParameter(":StaffRecordID",OracleDbType.Int32,Convert.ToInt32(strSeq),ParameterDirection.Input), new OracleParameter(":staffID",OracleDbType.Int32,staffRecordEntity.StaffID,ParameterDirection.Input), new OracleParameter(":staffCode",OracleDbType.Varchar2,staffRecordEntity.StaffCode,ParameterDirection.Input), new OracleParameter(":recordDate",OracleDbType.Date,staffRecordEntity.RecordDate,ParameterDirection.Input), new OracleParameter(":recordType",OracleDbType.Int32,staffRecordEntity.RecordType,ParameterDirection.Input), new OracleParameter(":exProbationEndDate",OracleDbType.Date,staffRecordEntity.ExProbationEndDate,ParameterDirection.Input), new OracleParameter(":approvalStatus",OracleDbType.Int32,staffRecordEntity.ApprovalStatus,ParameterDirection.Input), new OracleParameter(":Approver",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":originalOrganizationID",OracleDbType.Int32,staffRecordEntity.OriginalOrganizationID,ParameterDirection.Input), new OracleParameter(":originalStaffStatus",OracleDbType.Int32,staffRecordEntity.OriginalStaffStatus,ParameterDirection.Input), new OracleParameter(":originalJobs",OracleDbType.Int32,staffRecordEntity.OriginalJobs,ParameterDirection.Input), new OracleParameter(":originalPost",OracleDbType.Int32,staffRecordEntity.OriginalPost,ParameterDirection.Input), new OracleParameter(":targetOrganizationID",OracleDbType.Int32,staffRecordEntity.TargetOrganizationID,ParameterDirection.Input), new OracleParameter(":targetStaffStatus",OracleDbType.Int32,staffRecordEntity.TargetStaffStatus,ParameterDirection.Input), new OracleParameter(":targetJobs",OracleDbType.Int32,staffRecordEntity.TargetJobs,ParameterDirection.Input), new OracleParameter(":targetPost",OracleDbType.Int32,staffRecordEntity.TargetPost,ParameterDirection.Input), new OracleParameter(":applicant",OracleDbType.Int32,staffRecordEntity.Applicant,ParameterDirection.Input), new OracleParameter(":reason",OracleDbType.Varchar2,staffRecordEntity.Reason,ParameterDirection.Input), new OracleParameter(":suggestion",OracleDbType.Varchar2,staffRecordEntity.Suggestion,ParameterDirection.Input), new OracleParameter(":remarks",OracleDbType.Varchar2,staffRecordEntity.Remarks,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input) }; foreach (OracleParameter para in parmeters1) { if ((para.Value + "").Equals("")) { para.Value = DBNull.Value; } } #endregion resultEnity.OperateStatus = oracleTrConn.ExecuteNonQuery(sqlString1, parmeters1); resultEnity.HRStaffRecordID = Convert.ToInt32(strSeq); }//修改部门调整信息 else if (editStatus == WCFConstant.FormMode.Edit) { #region 员工履历被其他用户修改 sqlString1 = "SELECT Count(*) FROM TP_HR_STAFFRECORD" + " WHERE StaffRecordID = :staffRecordID And OPTimeStamp = :OPTimeStamp"; parmeters1 = new OracleParameter[] { new OracleParameter(":staffRecordID",OracleDbType.Int32,staffRecordEntity.StaffRecordID,ParameterDirection.Input), new OracleParameter(":OPTimeStamp", OracleDbType.TimeStamp, staffRecordEntity.OPTimeStamp, ParameterDirection.Input) }; string strCount = oracleTrConn.GetSqlResultToStr(sqlString1, parmeters1); if ("0".Equals(strCount)) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -2; return resultEnity; //员工履历被其他用户修改 } #endregion #region 更新部门调整信息 string sqlString = "UPDATE TP_HR_StaffRecord SET " + "RecordDate = :recordDate" + ",TargetOrganizationID = :targetOrganizationID" + ",TargetStaffStatus = :targetStaffStatus" + ",TargetJobs = :targetJobs" + ",TargetPost = :targetPost " + ",ExProbationEndDate = :exProbationEndDate" + ",Applicant = :applicant" + ",Reason = :reason" + ",Suggestion = :suggestion" + ",Remarks = :remarks" + ",UpdateUserID = :UpdateUserID" + ",UpdateTime = sysdate" + " WHERE StaffRecordID = :staffRecordID"; parmeters1 = new OracleParameter[] { new OracleParameter(":recordDate",OracleDbType.Date,staffRecordEntity.RecordDate,ParameterDirection.Input), new OracleParameter(":targetOrganizationID",OracleDbType.Int32,staffRecordEntity.TargetOrganizationID,ParameterDirection.Input), new OracleParameter(":targetStaffStatus",OracleDbType.Int32,staffRecordEntity.TargetStaffStatus,ParameterDirection.Input), new OracleParameter(":targetJobs",OracleDbType.Int32,staffRecordEntity.TargetJobs,ParameterDirection.Input), new OracleParameter(":targetPost",OracleDbType.Int32,staffRecordEntity.TargetPost,ParameterDirection.Input), new OracleParameter(":exProbationEndDate",OracleDbType.Date,staffRecordEntity.ExProbationEndDate,ParameterDirection.Input), new OracleParameter(":applicant",OracleDbType.Int32,staffRecordEntity.Applicant,ParameterDirection.Input), new OracleParameter(":reason",OracleDbType.Varchar2,staffRecordEntity.Reason,ParameterDirection.Input), new OracleParameter(":suggestion",OracleDbType.Varchar2,staffRecordEntity.Suggestion,ParameterDirection.Input), new OracleParameter(":remarks",OracleDbType.Varchar2,staffRecordEntity.Remarks,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":staffRecordID",OracleDbType.Int32,staffRecordEntity.StaffRecordID,ParameterDirection.Input) }; foreach (OracleParameter para in parmeters1) { if ((para.Value + "").Equals("")) { para.Value = DBNull.Value; } } #endregion resultEnity.OperateStatus = oracleTrConn.ExecuteNonQuery(sqlString, parmeters1); resultEnity.HRStaffRecordID = staffRecordEntity.StaffRecordID; } #region 更新员工档案表中的预计试用期结束日期 string strSql2 = "Update TP_HR_Staff Set ExProbationEndDate = :pExProbationEndDate WHERE StaffID = :pStaffID"; OracleParameter[] parmetersSql2 = new OracleParameter[] { new OracleParameter(":pExProbationEndDate", OracleDbType.Date, staffRecordEntity.ExProbationEndDate, ParameterDirection.Input), new OracleParameter(":pStaffID", OracleDbType.Int32, staffRecordEntity.StaffID , ParameterDirection.Input) }; foreach (var itemNull in parmetersSql2) { if (string.IsNullOrEmpty(itemNull.Value + "")) { itemNull.Value = DBNull.Value; } } oracleTrConn.ExecuteNonQuery(strSql2, parmetersSql2); #endregion if (staffRecordEntity.ApprovalStatus == 3) { List parmeters2 = new List(); parmeters2.Add(new OracleParameter(":organizationID", OracleDbType.Int32, staffRecordEntity.TargetOrganizationID, ParameterDirection.Input)); parmeters2.Add(new OracleParameter(":staffStatus", OracleDbType.Int32, staffRecordEntity.TargetStaffStatus, ParameterDirection.Input)); parmeters2.Add(new OracleParameter(":exProbationEndDate", OracleDbType.TimeStamp, staffRecordEntity.ExProbationEndDate, ParameterDirection.Input)); parmeters2.Add(new OracleParameter(":pUpdateUserID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input)); parmeters2.Add(new OracleParameter(":pStaffID", OracleDbType.Int32, staffRecordEntity.StaffID, ParameterDirection.Input)); #region 更新数据到员工档案表 string sqlString2 = " UPDATE TP_HR_STAFF SET " + " OrganizationID = :organizationID" + " ,StaffStatus=:staffStatus" + " ,ExProbationEndDate=:exProbationEndDate"; if (staffRecordEntity.TargetJobs != -1000) { sqlString2 += " ,Jobs = :jobs"; parmeters2.Add(new OracleParameter(":jobs", OracleDbType.Int32, staffRecordEntity.TargetJobs, ParameterDirection.Input)); } if (staffRecordEntity.TargetPost != -1000) { sqlString2 += " ,Post = :post"; parmeters2.Add(new OracleParameter(":post", OracleDbType.Int32, staffRecordEntity.TargetPost, ParameterDirection.Input)); } sqlString2 += " ,UpdateUserID=:pUpdateUserID" + " Where StaffID=:pStaffID"; #endregion resultEnity.OperateStatus += oracleTrConn.ExecuteNonQuery(sqlString2, parmeters2.ToArray()); if (staffRecordEntity.ExProbationEndDate != null) { #region 如果试用，往试用表里插入数据 string sqlString3 = "INSERT INTO TP_HR_StaffProbation(" + "StaffID" + ",JobsID" + ",BeginDate" + ",AccountID" + ",CreateUserID" + ",UpdateUserID) VALUES (" + ":StaffID" + ",:JobsID" + ",sysdate" + ",:AccountID" + ",:CreateUserID" + ",:UpdateUserID)"; OracleParameter[] parmeters3 = new OracleParameter[] { new OracleParameter(":StaffID",OracleDbType.Int32,staffRecordEntity.StaffID,ParameterDirection.Input), new OracleParameter(":JobsID",OracleDbType.Int32,staffRecordEntity.TargetJobs,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), }; resultEnity.OperateStatus += oracleTrConn.ExecuteNonQuery(sqlString3, parmeters3); #endregion } } oracleTrConn.Commit(); oracleTrConn.Disconnect(); return resultEnity; } catch (Exception ex) { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } } } ///

/// 部门调整审批保存 ///

/// 员工编号 /// 员工档案表时间戳 /// 员工履历表实体 /// 审批状态 True 通过 False 不通过 /// 审批意见 /// 用户基本信息 /// 0 没有数据被修改 -1员工档案被其他用户修改 -2存在待审批履历 -3员工履历被其他用户修改 public static HRResultEntity SaveHRStaffOrganizationApprovalInfo(int pUserId, DateTime pOPTimeStamp, StaffRecordEntity pStaffRecord, bool pState, string pMemo, SUserInfo sUserInfo) { HRResultEntity resultEnity = new HRResultEntity(); IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); #region 员工档案被其他用户修改 string sql1 = "SELECT * FROM TP_HR_Staff" + " WHERE Staffid =:Staffid And OPTimeStamp= :opTimeStamp"; OracleParameter[] parmetersSql1 = new OracleParameter[] { new OracleParameter(":Staffid", OracleDbType.Int32, pUserId , ParameterDirection.Input), new OracleParameter(":opTimeStamp", OracleDbType.TimeStamp, pOPTimeStamp , ParameterDirection.Input) }; DataTable dtStaff = oracleTrConn.GetSqlResultToDt(sql1, parmetersSql1); if (dtStaff == null || dtStaff.Rows.Count <= 0) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -3; return resultEnity;// 员工档案被其他用户修改 } #endregion #region 员工履历被其他用户修改 string sql2 = "SELECT * FROM TP_HR_STAFFRECORD" + " WHERE StaffRecordID = :staffRecordID And OPTimeStamp = :oPTimeStamp"; OracleParameter[] parmetersSql2 = new OracleParameter[] { new OracleParameter(":staffRecordID", OracleDbType.Int32, pStaffRecord.StaffRecordID, ParameterDirection.Input), new OracleParameter(":oPTimeStamp", OracleDbType.TimeStamp, pStaffRecord.OPTimeStamp, ParameterDirection.Input), }; DataTable dtStaffRecord = oracleTrConn.GetSqlResultToDt(sql2, parmetersSql2); if (dtStaffRecord == null || dtStaffRecord.Rows.Count <= 0) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -2; return resultEnity;// 员工履历被其他用户修改 } #endregion //保存数据到员工履历表和人事审批表 resultEnity.OperateStatus += UpdateHRApprovalInfo(dtStaff, dtStaffRecord, pState, pMemo, sUserInfo, oracleTrConn); if (pState) { List parmeters2 = new List(); parmeters2.Add(new OracleParameter(":organizationID", OracleDbType.Int32, pStaffRecord.TargetOrganizationID, ParameterDirection.Input)); parmeters2.Add(new OracleParameter(":staffStatus", OracleDbType.Int32, pStaffRecord.TargetStaffStatus, ParameterDirection.Input)); parmeters2.Add(new OracleParameter(":exProbationEndDate", OracleDbType.TimeStamp, pStaffRecord.ExProbationEndDate, ParameterDirection.Input)); parmeters2.Add(new OracleParameter(":pUpdateUserID",OracleDbType.Int32,sUserInfo.UserID, ParameterDirection.Input)); parmeters2.Add(new OracleParameter(":pStaffID",OracleDbType.Int32,pUserId, ParameterDirection.Input)); #region 更新数据到员工档案表 string sqlString2 = " UPDATE TP_HR_STAFF SET " + " OrganizationID = :organizationID" + " ,StaffStatus=:staffStatus" + " ,ExProbationEndDate=:exProbationEndDate"; if (pStaffRecord.TargetJobs != -1000) { sqlString2 += " ,Jobs = :jobs"; parmeters2.Add(new OracleParameter(":jobs", OracleDbType.Int32, pStaffRecord.TargetJobs, ParameterDirection.Input)); } if (pStaffRecord.TargetPost != -1000) { sqlString2 += " ,Post = :post"; parmeters2.Add(new OracleParameter(":post",OracleDbType.Int32,pStaffRecord.TargetPost,ParameterDirection.Input)); } sqlString2 += " ,UpdateUserID=:pUpdateUserID" + " Where StaffID=:pStaffID"; #endregion resultEnity.OperateStatus += oracleTrConn.ExecuteNonQuery(sqlString2, parmeters2.ToArray()); if (pStaffRecord.ExProbationEndDate != null) { #region 如果试用，往试用表里插入数据 string sqlString3 = "INSERT INTO TP_HR_StaffProbation(" + "StaffID" + ",JobsID" + ",BeginDate" + ",AccountID" + ",CreateUserID" + ",UpdateUserID) VALUES (" + ":StaffID" + ",:JobsID" + ",sysdate" + ",:AccountID" + ",:CreateUserID" + ",:UpdateUserID)"; OracleParameter[] parmeters3 = new OracleParameter[] { new OracleParameter(":StaffID",OracleDbType.Int32,pUserId,ParameterDirection.Input), new OracleParameter(":JobsID",OracleDbType.Int32,pStaffRecord.TargetJobs,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), }; resultEnity.OperateStatus += oracleTrConn.ExecuteNonQuery(sqlString3, parmeters3); #endregion } } oracleTrConn.Commit(); oracleTrConn.Disconnect(); return resultEnity; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } } } #endregion #region 员工离职审批 ///

/// 人事审批保存 ///

/// 员工编号 /// 员工档案表时间戳 /// 员工履历表实体 /// 审批状态 True 通过 False 不通过 /// 审批意见 /// 用户基本信息 /// 0 没有数据被修改 -1员工档案被其他用户修改 -2存在待审批履历 -3员工履历被其他用户修改 public static HRResultEntity SaveHRDimissionApprovalInfo(int pUserId, DateTime pOPTimeStamp, StaffRecordEntity pStaffRecord, bool pState, string pMemo, SUserInfo sUserInfo) { HRResultEntity resultEnity = new HRResultEntity(); IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); #region 员工档案被其他用户修改 string sql1 = "SELECT * FROM TP_HR_Staff" + " WHERE Staffid =:Staffid And OPTimeStamp= :opTimeStamp"; OracleParameter[] parmetersSql1 = new OracleParameter[] { new OracleParameter(":Staffid", OracleDbType.Int32, pUserId , ParameterDirection.Input), new OracleParameter(":opTimeStamp", OracleDbType.TimeStamp, pOPTimeStamp , ParameterDirection.Input) }; DataTable dtStaff = oracleTrConn.GetSqlResultToDt(sql1, parmetersSql1); if (dtStaff == null || dtStaff.Rows.Count <= 0) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -3; return resultEnity;// 员工档案被其他用户修改 } #endregion #region 员工履历被其他用户修改 string sql2 = "SELECT * FROM TP_HR_STAFFRECORD" + " WHERE StaffRecordID = " + pStaffRecord.StaffRecordID + " And OPTimeStamp = :OPTimeStamp"; OracleParameter[] parmetersSql2 = new OracleParameter[] { new OracleParameter(":OPTimeStamp", OracleDbType.TimeStamp, pStaffRecord.OPTimeStamp, ParameterDirection.Input), }; DataTable dtStaffRecord = oracleTrConn.GetSqlResultToDt(sql2, parmetersSql2); if (dtStaffRecord == null || dtStaffRecord.Rows.Count <= 0) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -2; return resultEnity;// 员工履历被其他用户修改 } #endregion //保存数据到员工履历表和人事审批表 resultEnity.OperateStatus += UpdateHRApprovalInfo(dtStaff, dtStaffRecord, pState, pMemo, sUserInfo, oracleTrConn); if (pState) //审批通过 { #region 更新数据到员工档案表 string sqlString2 = " UPDATE TP_HR_STAFF SET " + "StaffStatus=3" + ",TurnoverDate=FUN_CMN_GetAccountDate(:pACCOUNTID)" + ",UpdateUserID=:pUpdateUserID" + " Where StaffID=:pStaffID"; OracleParameter[] parmeters2 = new OracleParameter[] { new OracleParameter(":pACCOUNTID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":pUpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":pStaffID",OracleDbType.Int32,pUserId,ParameterDirection.Input), }; #endregion resultEnity.OperateStatus += oracleTrConn.ExecuteNonQuery(sqlString2, parmeters2); } oracleTrConn.Commit(); oracleTrConn.Disconnect(); return resultEnity; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } } } #endregion #region 员工履历停用 ///

/// 设置禁用员工履历 ///

/// 员工履历ID /// 用户基本信息 /// HRResultEntity public static HRResultEntity SetStaffRecordValueFlag(int staffrecordid, SUserInfo sUserInfo) { HRResultEntity resultEnity = new HRResultEntity(); IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); string sql = "Select Count(*) From TP_HR_StaffRecord Where StaffRecordID=" + staffrecordid + " And ApprovalStatus=0 And ValueFlag=1"; string strCount = oracleTrConn.GetSqlResultToStr(sql); if (!"0".Equals(strCount)) { string sqlString = "Update TP_HR_StaffRecord Set ValueFlag = 0,UpdateUserID = " + sUserInfo.UserID + " Where StaffRecordID =" + staffrecordid; resultEnity.OperateStatus = oracleTrConn.ExecuteNonQuery(sqlString); oracleTrConn.Commit(); oracleTrConn.Disconnect(); return resultEnity; } else { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -5; return resultEnity; } } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } } } #endregion #region 员工入职审批 ///

/// 员工入职审批 ///

/// 员工编号 /// 员工时间戳 /// 员工履历表实体 /// 审批状态 True 通过 False 不通过 /// 审批意见 /// 用户基本信息 /// public static HRResultEntity SaveHRStaffRecordApprovalInfo(int pUserId, DateTime pOPTimeStamp, StaffRecordEntity pStaffRecord, bool pState, string pMemo, SUserInfo sUserInfo) { HRResultEntity resultEnity = new HRResultEntity(); IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); #region 员工档案被其他用户修改 string sql1 = "SELECT * FROM TP_HR_Staff" + " WHERE Staffid =:Staffid And OPTimeStamp= :opTimeStamp"; OracleParameter[] parmetersSql1 = new OracleParameter[] { new OracleParameter(":Staffid", OracleDbType.Int32, pUserId , ParameterDirection.Input), new OracleParameter(":opTimeStamp", OracleDbType.TimeStamp, pOPTimeStamp , ParameterDirection.Input) }; DataTable dtStaff = oracleTrConn.GetSqlResultToDt(sql1, parmetersSql1); if (dtStaff == null || dtStaff.Rows.Count <= 0) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -3; return resultEnity;// 员工档案被其他用户修改 } #endregion #region 员工履历被其他用户修改 int job = 0; int Post = 0; int OrganizationID = 0; string ExProbationEndDate = ""; string sql2 = "SELECT * FROM TP_HR_STAFFRECORD" + " WHERE StaffRecordID = " + pStaffRecord.StaffRecordID + " And OPTimeStamp = :OPTimeStamp"; OracleParameter[] parmetersSql2 = new OracleParameter[] { new OracleParameter(":OPTimeStamp", OracleDbType.TimeStamp, pStaffRecord.OPTimeStamp, ParameterDirection.Input), }; DataTable dtStaffRecord = oracleTrConn.GetSqlResultToDt(sql2, parmetersSql2); if (dtStaffRecord == null || dtStaffRecord.Rows.Count <= 0) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -2; return resultEnity;// 员工履历被其他用户修改 } else { job = Convert.ToInt32(dtStaffRecord.Rows[0]["OriginalJobs"]);//工种 if (dtStaffRecord.Rows[0]["ExProbationEndDate"] != DBNull.Value) ExProbationEndDate = dtStaffRecord.Rows[0]["ExProbationEndDate"].ToString(); Post = Convert.ToInt32(dtStaffRecord.Rows[0]["OriginalPost"]);//职务 OrganizationID = Convert.ToInt32(dtStaffRecord.Rows[0]["OriginalOrganizationID"]);//组织机构 } #endregion //保存数据到员工履历表和人事审批表 resultEnity.OperateStatus += UpdateHRApprovalInfo(dtStaff, dtStaffRecord, pState, pMemo, sUserInfo, oracleTrConn); if (pState) //审批通过 { #region 更新数据到员工档案表 string sqlString2 = " UPDATE TP_HR_STAFF SET " + "StaffStatus=" + (ExProbationEndDate == "" ? "2" : "1") //试用 + ",EntryDate=FUN_CMN_GetAccountDate(:pACCOUNTID)" + ",UpdateUserID=:pUpdateUserID" + ",OrganizationID=:OrganizationID" + ",Post=:Post" + ",Jobs=:Jobs" + " Where StaffID=:pStaffID"; OracleParameter[] parmeters2 = new OracleParameter[] { new OracleParameter(":pACCOUNTID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":pUpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":pStaffID",OracleDbType.Int32,pUserId,ParameterDirection.Input), new OracleParameter(":OrganizationID",OracleDbType.Int32,OrganizationID,ParameterDirection.Input), new OracleParameter(":Post",OracleDbType.Int32,Post,ParameterDirection.Input), new OracleParameter(":Jobs",OracleDbType.Int32,job,ParameterDirection.Input), }; #endregion resultEnity.OperateStatus += oracleTrConn.ExecuteNonQuery(sqlString2, parmeters2); if (ExProbationEndDate != "") { #region 如果试用，往试用表里插入数据 string sqlString3 = "INSERT INTO TP_HR_StaffProbation(" + "StaffID" + ",JobsID" + ",BeginDate" + ",AccountID" + ",CreateUserID" + ",UpdateUserID) VALUES (" + ":StaffID" + ",:JobsID" + ",sysdate" + ",:AccountID" + ",:CreateUserID" + ",:UpdateUserID)"; OracleParameter[] parmeters3 = new OracleParameter[] { new OracleParameter(":StaffID",OracleDbType.Int32,pUserId,ParameterDirection.Input), new OracleParameter(":JobsID",OracleDbType.Int32,job,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), }; resultEnity.OperateStatus += oracleTrConn.ExecuteNonQuery(sqlString3, parmeters3); #endregion } } oracleTrConn.Commit(); oracleTrConn.Disconnect(); return resultEnity; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } } } #endregion #region 员工转正审批 ///

/// 员工转正审批 ///

/// 员工编号 /// 员工档案表的时间戳 /// 员工履历表实体 /// 审批状态 True 通过 False 不通过 /// 审批原因 /// 用户基本信息 /// HRResultEntity public static HRResultEntity SaveHrStaffPositiveApprovalInfo(int pUserId, DateTime pOPTimeStamp, StaffRecordEntity pStaffRecord, bool pState, string pMemo, SUserInfo sUserInfo) { HRResultEntity resultEnity = new HRResultEntity(); IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); #region 员工档案被其他用户修改 string sql1 = "SELECT * FROM TP_HR_Staff" + " WHERE Staffid =:Staffid And OPTimeStamp= :opTimeStamp"; OracleParameter[] parmetersSql1 = new OracleParameter[] { new OracleParameter(":Staffid", OracleDbType.Int32, pUserId , ParameterDirection.Input), new OracleParameter(":opTimeStamp", OracleDbType.TimeStamp, pOPTimeStamp , ParameterDirection.Input) }; DataTable dtStaff = oracleTrConn.GetSqlResultToDt(sql1, parmetersSql1); if (dtStaff == null || dtStaff.Rows.Count <= 0) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -3; return resultEnity;// 员工档案被其他用户修改 } #endregion #region 员工履历被其他用户修改 int job = 0; string ExProbationEndDate = ""; string sql2 = "SELECT * FROM TP_HR_STAFFRECORD" + " WHERE StaffRecordID = " + pStaffRecord.StaffRecordID + " And OPTimeStamp = :OPTimeStamp"; OracleParameter[] parmetersSql2 = new OracleParameter[] { new OracleParameter(":OPTimeStamp", OracleDbType.TimeStamp, pStaffRecord.OPTimeStamp, ParameterDirection.Input), }; DataTable dtStaffRecord = oracleTrConn.GetSqlResultToDt(sql2, parmetersSql2); if (dtStaffRecord == null || dtStaffRecord.Rows.Count <= 0) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -2; return resultEnity;// 员工履历被其他用户修改 } else { job = Convert.ToInt32(dtStaffRecord.Rows[0]["OriginalJobs"]);//工种 if (dtStaffRecord.Rows[0]["ExProbationEndDate"] != DBNull.Value) ExProbationEndDate = dtStaffRecord.Rows[0]["ExProbationEndDate"].ToString(); } #endregion //保存数据到员工履历表和人事审批表 resultEnity.OperateStatus += UpdateHRApprovalInfo(dtStaff, dtStaffRecord, pState, pMemo, sUserInfo, oracleTrConn); if (pState) //审批通过 { #region 更新数据到员工档案表 string sqlString2 = " UPDATE TP_HR_STAFF SET " + "StaffStatus=2" //2：转正 + ",UpdateUserID=:pUpdateUserID" + " Where StaffID=:pStaffID"; OracleParameter[] parmeters2 = new OracleParameter[] { new OracleParameter(":pUpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":pStaffID",OracleDbType.Int32,pUserId,ParameterDirection.Input) }; #endregion resultEnity.OperateStatus += oracleTrConn.ExecuteNonQuery(sqlString2, parmeters2); if (ExProbationEndDate != "") { UpdateHRStaffProbation(pUserId, job, sUserInfo, oracleTrConn); } } oracleTrConn.Commit(); oracleTrConn.Disconnect(); return resultEnity; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } } } #endregion #region 员工考勤 ///

/// 根据DataTable中的考勤数据更新数据表 ///

/// 考勤数据表 /// 用户基本信息 /// HRResultEntity public static HRResultEntity SaveStaffAttendanceInfo(DataTable pStaffAttendance, SUserInfo sUserInfo) { HRResultEntity resultEnity = new HRResultEntity(); IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { if (pStaffAttendance == null || pStaffAttendance.Rows.Count <= 0) { resultEnity.OperateStatus = 0; return resultEnity; } oracleTrConn.Connect(); foreach (DataRow newRowStaff in pStaffAttendance.Rows) { #region 验证该员工是否存在 if (string.IsNullOrEmpty(newRowStaff["StaffID"].ToString())) { resultEnity.OperateLogInfo += "员工编号：" + newRowStaff["StaffCode"].ToString() + " 不存在，不能被添加！" + Environment.NewLine; continue; } #endregion #region 获取DataRow中的考勤数据 int intStaffID = Convert.ToInt32(newRowStaff["STAFFID"]); string strStaffName = newRowStaff["StaffName"].ToString(); DateTime dtAttendanceDate = Convert.ToDateTime(newRowStaff["ATTENDANCEDATE"]); string strCardNumber = newRowStaff["CARDNUMBER"].ToString(); string strAttendanceStatus = newRowStaff["ATTENDANCESTATUS"].ToString(); string strAbsenceReason = newRowStaff["ABSENCEREASON"].ToString(); double douTardinessTimes = Convert.ToDouble(newRowStaff["TARDINESSTIMES"]); string strRemarks = newRowStaff["Remarks"].ToString(); #endregion #region 验证数据表中是否存在该数据 string strSql1 = "Select max(SettlementFlag) From TP_HR_StaffAttendance " + " Where StaffID = :pStaffID And AttendanceDate = :pAttendanceDate"; OracleParameter[] parmetersSql1 = new OracleParameter[] { new OracleParameter(":pStaffID", OracleDbType.Int32, intStaffID , ParameterDirection.Input), new OracleParameter(":pAttendanceDate", OracleDbType.Date, dtAttendanceDate , ParameterDirection.Input) }; #endregion string strSettlementFlag = oracleTrConn.GetSqlResultToStr(strSql1, parmetersSql1); if (string.IsNullOrEmpty(strSettlementFlag)) { #region 向员工考勤表插入新数据 string strSql2 = "Insert into TP_HR_StaffAttendance Value (" + "StaffID" + ",AttendanceDate" + ",CardNumber" + ",AttendanceStatus" + ",AbsenceReason" + ",TardinessTimes" + ",Remarks" + ",AccountID" + ",CreateUserID" + ",UpdateUserID" + ",SettlementFlag" + ")" + " VALUES (" + " :pStaffID" + " ,:pAttendanceDate" + " ,:pCardNumber" + " ,:pAttendanceStatus" + " ,:pAbsenceReason" + " ,:pTardinessTimes" + " ,:pRemarks" + " ,:pAccountID" + " ,:pCreateUserID" + " ,:pUpdateUserID" + " ,0" + " )"; OracleParameter[] parmetersSql2 = new OracleParameter[] { new OracleParameter(":pStaffID", OracleDbType.Int32, intStaffID , ParameterDirection.Input), new OracleParameter(":pAttendanceDate", OracleDbType.Date, dtAttendanceDate , ParameterDirection.Input), new OracleParameter(":pCardNumber", OracleDbType.Varchar2, strCardNumber , ParameterDirection.Input), new OracleParameter(":pAttendanceStatus", OracleDbType.Char, strAttendanceStatus , ParameterDirection.Input), new OracleParameter(":pAbsenceReason", OracleDbType.Char, strAbsenceReason , ParameterDirection.Input), new OracleParameter(":pTardinessTimes", OracleDbType.Double, douTardinessTimes , ParameterDirection.Input), new OracleParameter(":pRemarks", OracleDbType.Varchar2, strRemarks , ParameterDirection.Input), new OracleParameter(":pAccountID", OracleDbType.Int32, sUserInfo.AccountID , ParameterDirection.Input), new OracleParameter(":pCreateUserID", OracleDbType.Int32, sUserInfo.UserID , ParameterDirection.Input), new OracleParameter(":pUpdateUserID", OracleDbType.Int32, sUserInfo.UserID , ParameterDirection.Input), }; #endregion resultEnity.OperateStatus += oracleTrConn.ExecuteNonQuery(strSql2, parmetersSql2); } else { if ("1".Equals(strSettlementFlag)) { //工资已经结算不能进行编辑 resultEnity.OperateLogInfo += strStaffName + dtAttendanceDate.ToString("yyyy-MM-dd") + "工资已经结算！" + Environment.NewLine; } else { #region 更新员工考勤表的数据 string strSql3 = "Update TP_HR_StaffAttendance Set " + "CardNumber = :pCardNumber" + ",AttendanceStatus = :pAttendanceStatus" + ",AbsenceReason = :pAbsenceReason" + ",TardinessTimes = :pTardinessTimes" + ",Remarks = :pRemarks" + ",UpdateUserID = :pUpdateUserID" + " Where StaffID = :pStaffID And AttendanceDate = :pAttendanceDate And AccountID = :pAccountID"; OracleParameter[] parmetersSql3 = new OracleParameter[] { new OracleParameter(":pCardNumber", OracleDbType.Varchar2, strCardNumber , ParameterDirection.Input), new OracleParameter(":pAttendanceStatus", OracleDbType.Char, strAttendanceStatus , ParameterDirection.Input), new OracleParameter(":pAbsenceReason", OracleDbType.Char, strAbsenceReason , ParameterDirection.Input), new OracleParameter(":pTardinessTimes", OracleDbType.Double, douTardinessTimes , ParameterDirection.Input), new OracleParameter(":pRemarks", OracleDbType.Varchar2, strRemarks , ParameterDirection.Input), new OracleParameter(":pUpdateUserID", OracleDbType.Int32, sUserInfo.UserID , ParameterDirection.Input), new OracleParameter(":pStaffID", OracleDbType.Int32, intStaffID , ParameterDirection.Input), new OracleParameter(":pAttendanceDate", OracleDbType.Date, dtAttendanceDate , ParameterDirection.Input), new OracleParameter(":pAccountID", OracleDbType.Int32, sUserInfo.AccountID , ParameterDirection.Input), }; #endregion resultEnity.OperateStatus += oracleTrConn.ExecuteNonQuery(strSql3, parmetersSql3); } } } oracleTrConn.Commit(); oracleTrConn.Disconnect(); return resultEnity; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } } } #endregion #region 行政奖惩 ///

/// 根据传入的实体保存行政奖惩记录 ///

/// 行政奖惩实体 /// 用户基本信息 /// 新增还是编辑枚举 /// HRResultEntity public static HRResultEntity SaveStaffAdminRAPInfo(HRAdminRAPEntity pAdminRAPEntity, SUserInfo sUserInfo, WCFConstant.FormMode pStatus) { HRResultEntity resultEnity = new HRResultEntity(); IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { if (pAdminRAPEntity == null) { resultEnity.OperateStatus = 0; return resultEnity; } oracleTrConn.Connect(); if (pStatus == WCFConstant.FormMode.Add) { string strSeq = oracleTrConn.GetSqlResultToStr("Select SEQ_HR_AdminRAP_RAPID.nextval from dual"); #region 新建时插入数据 string strSql1 = "Insert Into TP_HR_ADMINRAP (RAPID," + "STAFFID" + ",RAPTYPE" + ",REASON" + ",RAPDATE" + ",RAPAMOUNT" + ",ADMINISTRATIONTYPE" + ",REMARKS" + ",AUDITSTATUS" + ",ACCOUNTID" + ",CREATEUSERID" + ",UPDATEUSERID)" + " Values (:RAPID," + ":pSTAFFID" + ",:pRAPTYPE" + ",:pREASON" + ",:pRAPDATE" + ",:pRAPAMOUNT" + ",:pADMINISTRATIONTYPE" + ",:pREMARKS" + ",:pAUDITSTATUS" + ",:pACCOUNTID" + ",:pCREATEUSERID" + ",:pUPDATEUSERID" + ")"; OracleParameter[] parmetersSql1 = new OracleParameter[] { new OracleParameter(":RAPID", OracleDbType.Int32,Convert.ToInt32(strSeq) , ParameterDirection.Input), new OracleParameter(":pSTAFFID", OracleDbType.Int32, pAdminRAPEntity.StaffID , ParameterDirection.Input), new OracleParameter(":pRAPTYPE", OracleDbType.Double, pAdminRAPEntity.RAPType , ParameterDirection.Input), new OracleParameter(":pREASON", OracleDbType.Varchar2, pAdminRAPEntity.Reason , ParameterDirection.Input), new OracleParameter(":pRAPDATE", OracleDbType.Date, pAdminRAPEntity.RAPDate , ParameterDirection.Input), new OracleParameter(":pRAPAMOUNT", OracleDbType.Double, pAdminRAPEntity.RAPAmount , ParameterDirection.Input), new OracleParameter(":pADMINISTRATIONTYPE", OracleDbType.Int32, pAdminRAPEntity.AdministrationType , ParameterDirection.Input), new OracleParameter(":pREMARKS", OracleDbType.Varchar2, pAdminRAPEntity.Remarks , ParameterDirection.Input), new OracleParameter(":pAUDITSTATUS", OracleDbType.Int32, 0 , ParameterDirection.Input), new OracleParameter(":pACCOUNTID", OracleDbType.Int32, sUserInfo.AccountID , ParameterDirection.Input), new OracleParameter(":pCREATEUSERID", OracleDbType.Int32, sUserInfo.UserID , ParameterDirection.Input), new OracleParameter(":pUPDATEUSERID", OracleDbType.Int32, sUserInfo.UserID , ParameterDirection.Input), }; #endregion resultEnity.OperateStatus = oracleTrConn.ExecuteNonQuery(strSql1, parmetersSql1); resultEnity.HRStaffID = Convert.ToInt32(strSeq); } else { #region 判断数据是否可以编辑 string strSlq2 = "Select max(AuditStatus) From TP_HR_AdminRAP Where RAPID = :pRAPID And OPTimeStamp = :pOPTimeStamp"; OracleParameter[] parmetersSql2 = new OracleParameter[] { new OracleParameter(":pRAPID", OracleDbType.Int32, pAdminRAPEntity.RAPID , ParameterDirection.Input), new OracleParameter(":pOPTimeStamp", OracleDbType.TimeStamp, pAdminRAPEntity.OPTimeStamp , ParameterDirection.Input), }; string strAuditStatus = oracleTrConn.GetSqlResultToStr(strSlq2, parmetersSql2); if (string.IsNullOrEmpty(strAuditStatus)) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -2; return resultEnity; } if (!"0".Equals(strAuditStatus)) { //不是待审批状态 oracleTrConn.Rollback(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -1; return resultEnity; } #endregion #region 编辑时更新数据 string strSql3 = "Update TP_HR_AdminRAP Set " + "RAPTYPE = :pRAPTYPE" + ",REASON = :pREASON" + ",RAPDATE = :pRAPDATE" + ",RAPAMOUNT = :pRAPAMOUNT" + ",ADMINISTRATIONTYPE = :pADMINISTRATIONTYPE" + ",REMARKS = :pREMARKS" + ",UPDATEUSERID = :pUPDATEUSERID" + " Where RAPID = :pRAPID And STAFFID = :pSTAFFID And AccountID = :pAccountID"; OracleParameter[] parmetersSql3 = new OracleParameter[] { new OracleParameter(":pRAPTYPE", OracleDbType.Double, pAdminRAPEntity.RAPType , ParameterDirection.Input), new OracleParameter(":pREASON", OracleDbType.Varchar2, pAdminRAPEntity.Reason , ParameterDirection.Input), new OracleParameter(":pRAPDATE", OracleDbType.Date, pAdminRAPEntity.RAPDate , ParameterDirection.Input), new OracleParameter(":pRAPAMOUNT", OracleDbType.Double, pAdminRAPEntity.RAPAmount , ParameterDirection.Input), new OracleParameter(":pADMINISTRATIONTYPE", OracleDbType.Int32, pAdminRAPEntity.AdministrationType , ParameterDirection.Input), new OracleParameter(":pREMARKS", OracleDbType.Varchar2, pAdminRAPEntity.Remarks , ParameterDirection.Input), new OracleParameter(":pUPDATEUSERID", OracleDbType.Int32, sUserInfo.UserID , ParameterDirection.Input), new OracleParameter(":pRAPID", OracleDbType.Int32, pAdminRAPEntity.RAPID , ParameterDirection.Input), new OracleParameter(":pSTAFFID", OracleDbType.Int32, pAdminRAPEntity.StaffID , ParameterDirection.Input), new OracleParameter(":pAccountID", OracleDbType.Int32, sUserInfo.AccountID , ParameterDirection.Input), }; #endregion resultEnity.OperateStatus = oracleTrConn.ExecuteNonQuery(strSql3, parmetersSql3); resultEnity.HRStaffID = pAdminRAPEntity.RAPID; } oracleTrConn.Commit(); oracleTrConn.Disconnect(); return resultEnity; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } } } ///

/// 行政奖惩审批 ///

/// 行政奖惩实体 /// 审批状态 True 通过 False 不通过 /// 审批原因 /// 用户基本信息 /// HRResultEntity public static HRResultEntity SaveStaffAdminRAPApprovalInfo(HRAdminRAPEntity pAdminRAPEntity, bool pState, SUserInfo sUserInfo) { HRResultEntity resultEnity = new HRResultEntity(); IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { if (pAdminRAPEntity == null) { resultEnity.OperateStatus = 0; return resultEnity; } oracleTrConn.Connect(); #region 判断数据是否可以编辑 string strSlq1 = "Select max(AuditStatus) From TP_HR_AdminRAP Where RAPID = :pRAPID And OPTimeStamp = :pOPTimeStamp"; OracleParameter[] parmetersSql1 = new OracleParameter[] { new OracleParameter(":pRAPID", OracleDbType.Int32, pAdminRAPEntity.RAPID , ParameterDirection.Input), new OracleParameter(":pOPTimeStamp", OracleDbType.TimeStamp, pAdminRAPEntity.OPTimeStamp , ParameterDirection.Input), }; string strAuditStatus = oracleTrConn.GetSqlResultToStr(strSlq1, parmetersSql1); if (string.IsNullOrEmpty(strAuditStatus)) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -2; return resultEnity; } if (!"0".Equals(strAuditStatus)) { //不是待审批状态 oracleTrConn.Rollback(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -1; return resultEnity; } #endregion #region 更新审批数据 int intAuditStatus = 2; //2：审核未通过 if (pState) { intAuditStatus = 1; //1：审核通过； } string strSql2 = "Update TP_HR_AdminRAP Set " + "AUDITSTATUS = :pAUDITSTATUS" + ",AUDITOR = :pAUDITOR" + ",AUDITDATE = sysdate" + ",ACCOUNTDATE = FUN_CMN_GetAccountDate(:pAccountID1)" + ",UPDATEUSERID = :pUPDATEUSERID" + " Where RAPID = :pRAPID And AccountID = :pAccountID"; OracleParameter[] parmetersSql2 = new OracleParameter[] { new OracleParameter(":pAUDITSTATUS", OracleDbType.Int32,intAuditStatus, ParameterDirection.Input), new OracleParameter(":pAUDITOR", OracleDbType.Int32, sUserInfo.UserID , ParameterDirection.Input), new OracleParameter(":pAccountID1", OracleDbType.Int32, sUserInfo.AccountID , ParameterDirection.Input), new OracleParameter(":pUPDATEUSERID", OracleDbType.Int32, sUserInfo.UserID , ParameterDirection.Input), new OracleParameter(":pRAPID", OracleDbType.Int32, pAdminRAPEntity.RAPID , ParameterDirection.Input), new OracleParameter(":pAccountID", OracleDbType.Int32, sUserInfo.AccountID , ParameterDirection.Input), }; #endregion resultEnity.OperateStatus = oracleTrConn.ExecuteNonQuery(strSql2, parmetersSql2); resultEnity.HRStaffID = pAdminRAPEntity.RAPID; oracleTrConn.Commit(); oracleTrConn.Disconnect(); return resultEnity; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } } } ///

/// 设置行政奖惩停用 ///

/// 行政奖惩编号 /// 用户基本信息 /// HRResultEntity public static HRResultEntity SetStaffAdminRAPValueFlag(int pRAPID, SUserInfo sUserInfo) { HRResultEntity resultEnity = new HRResultEntity(); IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); string sql = "Select max(SettlementFlag) From TP_HR_AdminRAP Where RAPID =" + pRAPID + " And AuditStatus = 0 And ValueFlag = 1"; string strSettlementFlag = oracleTrConn.GetSqlResultToStr(sql); if (!string.IsNullOrEmpty(strSettlementFlag)) { if ("1".Equals(strSettlementFlag)) //工资结算标识 1：已经结算 0：未结算 { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -6; return resultEnity; } string sqlString = "Update TP_HR_AdminRAP Set ValueFlag = 0,UpdateUserID = :pUpdateUserID" + " Where RAPID = :pRAPID And AccountID = :pAccountID"; OracleParameter[] parmetersSql = new OracleParameter[] { new OracleParameter(":pUpdateUserID", OracleDbType.Int32,sUserInfo.UserID, ParameterDirection.Input), new OracleParameter(":pRAPID", OracleDbType.Int32, pRAPID , ParameterDirection.Input), new OracleParameter(":pAccountID", OracleDbType.Int32, sUserInfo.AccountID , ParameterDirection.Input), }; resultEnity.OperateStatus = oracleTrConn.ExecuteNonQuery(sqlString, parmetersSql); oracleTrConn.Commit(); oracleTrConn.Disconnect(); return resultEnity; } else { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); resultEnity.OperateStatus = -5; return resultEnity; } } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } } } #endregion #region 员工报餐 ///

/// 员工报餐保存 ///

/// 待保存的数据表 /// 窗口状态枚举 /// 用户基本信息 /// HRResultEntity public static HRResultEntity SaveStaffDailyMealInfo(DataTable pStaff, WCFConstant.FormMode pStatus, SUserInfo sUserInfo) { HRResultEntity resultEnity = new HRResultEntity(); IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { if (pStaff == null || pStaff.Rows.Count <= 0) { resultEnity.OperateStatus = 0; return resultEnity; } oracleTrConn.Connect(); foreach (DataRow newRowStaff in pStaff.Rows) { #region 验证该员工是否存在 if (string.IsNullOrEmpty(newRowStaff["StaffID"].ToString())) { resultEnity.OperateLogInfo += "员工编号：" + newRowStaff["StaffCode"].ToString() + " 不存在，不能被添加！" + Environment.NewLine; continue; } #endregion #region 获取DataRow中的报餐数据 int intStaffID = Convert.ToInt32(newRowStaff["StaffID"]); DateTime dtMealDate = Convert.ToDateTime(newRowStaff["MealDate"]); string strOrderBreakfast = string.IsNullOrEmpty(newRowStaff["OrderBreakfast"].ToString()) ? "0" : newRowStaff["OrderBreakfast"].ToString(); string strOrderLunch = string.IsNullOrEmpty(newRowStaff["OrderLunch"].ToString()) ? "0" : newRowStaff["OrderLunch"].ToString(); string strOrderDinner = string.IsNullOrEmpty(newRowStaff["OrderDinner"].ToString()) ? "0" : newRowStaff["OrderDinner"].ToString(); string strMealBreakfast = string.IsNullOrEmpty(newRowStaff["MealBreakfast"].ToString()) ? "0" : newRowStaff["MealBreakfast"].ToString(); string strMealLunch = string.IsNullOrEmpty(newRowStaff["MealLunch"].ToString()) ? "0" : newRowStaff["MealLunch"].ToString(); string strMealDinner = string.IsNullOrEmpty(newRowStaff["MealDinner"].ToString()) ? "0" : newRowStaff["MealDinner"].ToString(); string strRemarks = newRowStaff["Remarks"].ToString(); if (pStatus == WCFConstant.FormMode.MealEdit) { strOrderBreakfast = "0"; strOrderLunch = "0"; strOrderDinner = "0"; strRemarks = string.Empty; } #endregion #region 验证数据表中是否存在该数据 string strSql1 = "Select max(StaffID) From TP_HR_StaffDailyMeal " + " Where StaffID = :pStaffID And MealDate = :pMealDate"; OracleParameter[] parmetersSql1 = new OracleParameter[] { new OracleParameter(":pStaffID", OracleDbType.Int32, intStaffID , ParameterDirection.Input), new OracleParameter(":pMealDate", OracleDbType.Date, dtMealDate , ParameterDirection.Input) }; #endregion string strResultStaffID = oracleTrConn.GetSqlResultToStr(strSql1, parmetersSql1); if (string.IsNullOrEmpty(strResultStaffID)) { #region 向员工报餐表插入新数据 string strSql2 = "Insert into TP_HR_StaffDailyMeal Value (" + "StaffID" + ",MealDate" + ",OrderBreakfast" + ",OrderLunch" + ",OrderDinner" + ",MealBreakfast" + ",MealLunch" + ",MealDinner" + ",Remarks" + ",AccountID" + ",CreateUserID" + ",UpdateUserID" + ")" + " VALUES (" + " :pStaffID" + " ,:pMealDate" + " ,:pOrderBreakfast" + " ,:pOrderLunch" + " ,:pOrderDinner" + " ,:pMealBreakfast" + " ,:pMealLunch" + " ,:pMealDinner" + " ,:pRemarks" + " ,:pAccountID" + " ,:pCreateUserID" + " ,:pUpdateUserID" + " )"; OracleParameter[] parmetersSql2 = new OracleParameter[] { new OracleParameter(":pStaffID", OracleDbType.Int32, intStaffID , ParameterDirection.Input), new OracleParameter(":pMealDate", OracleDbType.Date, dtMealDate , ParameterDirection.Input), new OracleParameter(":pOrderBreakfast", OracleDbType.Varchar2, strOrderBreakfast , ParameterDirection.Input), new OracleParameter(":pOrderLunch", OracleDbType.Varchar2, strOrderLunch , ParameterDirection.Input), new OracleParameter(":pOrderDinner", OracleDbType.Varchar2, strOrderDinner , ParameterDirection.Input), new OracleParameter(":pMealBreakfast", OracleDbType.Varchar2, strMealBreakfast , ParameterDirection.Input), new OracleParameter(":pMealLunch", OracleDbType.Varchar2, strMealLunch , ParameterDirection.Input), new OracleParameter(":pMealDinner", OracleDbType.Varchar2, strMealDinner , ParameterDirection.Input), new OracleParameter(":pRemarks", OracleDbType.Varchar2, strRemarks , ParameterDirection.Input), new OracleParameter(":pAccountID", OracleDbType.Int32, sUserInfo.AccountID , ParameterDirection.Input), new OracleParameter(":pCreateUserID", OracleDbType.Int32, sUserInfo.UserID , ParameterDirection.Input), new OracleParameter(":pUpdateUserID", OracleDbType.Int32, sUserInfo.UserID , ParameterDirection.Input), }; #endregion resultEnity.OperateStatus += oracleTrConn.ExecuteNonQuery(strSql2, parmetersSql2); } else { if (pStatus == WCFConstant.FormMode.Edit) { #region 更新员工报餐表的数据 string strSql3 = "Update TP_HR_StaffDailyMeal Set " + "OrderBreakfast = :pOrderBreakfast" + ",OrderLunch = :pOrderLunch" + ",OrderDinner = :pOrderDinner" + ",MealBreakfast = :pMealBreakfast" + ",MealLunch = :pMealLunch" + ",MealDinner = :pMealDinner" + ",Remarks = :pRemarks" + ",UpdateUserID = :pUpdateUserID" + " Where StaffID = :pStaffID And MealDate = :pMealDate And AccountID = :pAccountID"; OracleParameter[] parmetersSql3 = new OracleParameter[] { new OracleParameter(":pOrderBreakfast", OracleDbType.Varchar2, strOrderBreakfast , ParameterDirection.Input), new OracleParameter(":pOrderLunch", OracleDbType.Varchar2, strOrderLunch , ParameterDirection.Input), new OracleParameter(":pOrderDinner", OracleDbType.Varchar2, strOrderDinner , ParameterDirection.Input), new OracleParameter(":pMealBreakfast", OracleDbType.Varchar2, strMealBreakfast , ParameterDirection.Input), new OracleParameter(":pMealLunch", OracleDbType.Varchar2, strMealLunch, ParameterDirection.Input), new OracleParameter(":pMealDinner", OracleDbType.Varchar2, strMealDinner, ParameterDirection.Input), new OracleParameter(":pRemarks", OracleDbType.Varchar2, strRemarks, ParameterDirection.Input), new OracleParameter(":pUpdateUserID", OracleDbType.Int32, sUserInfo.UserID , ParameterDirection.Input), new OracleParameter(":pStaffID", OracleDbType.Int32, intStaffID, ParameterDirection.Input), new OracleParameter(":pMealDate", OracleDbType.Date, dtMealDate , ParameterDirection.Input), new OracleParameter(":pAccountID", OracleDbType.Int32, sUserInfo.AccountID , ParameterDirection.Input), }; #endregion resultEnity.OperateStatus += oracleTrConn.ExecuteNonQuery(strSql3, parmetersSql3); } else { #region 更新员工报餐表的数据 string strSql4 = "Update TP_HR_StaffDailyMeal Set " + "MealBreakfast = :pMealBreakfast" + ",MealLunch = :pMealLunch" + ",MealDinner = :pMealDinner" + ",UpdateUserID = :pUpdateUserID" + " Where StaffID = :pStaffID And MealDate = :pMealDate And AccountID = :pAccountID"; OracleParameter[] parmetersSql4 = new OracleParameter[] { new OracleParameter(":pMealBreakfast", OracleDbType.Varchar2, strMealBreakfast , ParameterDirection.Input), new OracleParameter(":pMealLunch", OracleDbType.Varchar2, strMealLunch, ParameterDirection.Input), new OracleParameter(":pMealDinner", OracleDbType.Varchar2, strMealDinner, ParameterDirection.Input), new OracleParameter(":pUpdateUserID", OracleDbType.Int32, sUserInfo.UserID , ParameterDirection.Input), new OracleParameter(":pStaffID", OracleDbType.Int32, intStaffID, ParameterDirection.Input), new OracleParameter(":pMealDate", OracleDbType.Date, dtMealDate , ParameterDirection.Input), new OracleParameter(":pAccountID", OracleDbType.Int32, sUserInfo.AccountID , ParameterDirection.Input), }; #endregion resultEnity.OperateStatus += oracleTrConn.ExecuteNonQuery(strSql4, parmetersSql4); } } } oracleTrConn.Commit(); oracleTrConn.Disconnect(); return resultEnity; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } } } #endregion #region 私有方法 ///

/// 根据传入的数据更新员工履历表数据 ///

/// 员工档案表 /// 员工履历表 /// 审批状态 True 通过 False 不通过 /// 审批意见 /// 用户基本信息 /// 数据库事物 /// int返回更新数据行数 private static int UpdateHRApprovalInfo(DataTable pStaff, DataTable pStaffRecord, bool pState, string pMemo, SUserInfo sUserInfo, IDBTransaction pTrConn) { int returnCount = 0; int intRECORDTYPE = Convert.ToInt32(pStaffRecord.Rows[0]["RecordType"]); int intSTAFFRECORDID = Convert.ToInt32(pStaffRecord.Rows[0]["StaffRecordID"]); int intRESULT = pState ? 1 : 0; int intApprovalStatus = pState ? 3 : 2; #region 向人事审批表中插入数据 string strSql1 = "INSERT INTO TP_HR_HRAPPROVAL (" + " RECORDTYPE" + " ,STAFFRECORDID" + " ,SUGGESTION" + " ,RESULT" + " ,REMARKS" + " ,ACCOUNTID" + " ,VALUEFLAG" + " ,CREATEUSERID" + " ,UPDATEUSERID" + ")" + " VALUES " + " (:pRECORDTYPE" + ",:pSTAFFRECORDID" + ",:pSUGGESTION" + ",:pRESULT" + ",:pREMARKS" + ",:pACCOUNTID" + ",:pVALUEFLAG" + ",:pCREATEUSERID" + ",:pUPDATEUSERID" + " )"; OracleParameter[] parmeters1 = new OracleParameter[] { new OracleParameter(":pRECORDTYPE",OracleDbType.Int32,intRECORDTYPE,ParameterDirection.Input), new OracleParameter(":pSTAFFRECORDID",OracleDbType.Int32,intSTAFFRECORDID,ParameterDirection.Input), new OracleParameter(":pSUGGESTION",OracleDbType.Varchar2,pMemo,ParameterDirection.Input), new OracleParameter(":pRESULT",OracleDbType.Int32,intRESULT,ParameterDirection.Input), new OracleParameter(":pREMARKS",OracleDbType.Varchar2,"",ParameterDirection.Input), new OracleParameter(":pACCOUNTID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":pVALUEFLAG",OracleDbType.Int32,1,ParameterDirection.Input), new OracleParameter(":pCREATEUSERID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":pUPDATEUSERID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input) }; #endregion returnCount += pTrConn.ExecuteNonQuery(strSql1, parmeters1); #region 更新数据到员工履历表 string sqlString2 = " UPDATE TP_HR_STAFFRECORD SET " + "ApprovalStatus=:pApprovalStatus" + ",Approver=:pApprover" + ",ApprovalDate=FUN_CMN_GetAccountDate(:ACCOUNTID)" + ",UpdateUserID=:pUpdateUserID" + " Where StaffRecordID=:pStaffRecordID"; OracleParameter[] parmeters2 = new OracleParameter[] { new OracleParameter(":pApprovalStatus",OracleDbType.Int32,intApprovalStatus,ParameterDirection.Input), new OracleParameter(":pApprover",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":ACCOUNTID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":pUpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":pStaffRecordID",OracleDbType.Int32,intSTAFFRECORDID,ParameterDirection.Input), }; #endregion returnCount += pTrConn.ExecuteNonQuery(sqlString2, parmeters2); return returnCount; } ///

/// 更新试用期表中的试用结束日期 ///

/// 员工编号 /// 工种编号 /// 用户基本信息 /// 数据库事务 private static void UpdateHRStaffProbation(int pStaffID, int pJobsID, SUserInfo sUserInfo, IDBTransaction pTrConn) { string strSql1 = "SELECT MAX(ProbationID) FROM TP_HR_StaffProbation WHERE StaffID = :pStaffID AND JobsID = :pJobsID"; OracleParameter[] parmeters1 = new OracleParameter[] { new OracleParameter(":pStaffID", OracleDbType.Int32, pStaffID, ParameterDirection.Input), new OracleParameter(":pJobsID", OracleDbType.Int32, pJobsID, ParameterDirection.Input) }; string strProbationID = pTrConn.GetSqlResultToStr(strSql1, parmeters1); if (!string.IsNullOrEmpty(strProbationID)) { int intProbationID = Convert.ToInt32(strProbationID); string strSql2 = "UPDATE TP_HR_StaffProbation SET EndDate = FUN_CMN_GetAccountDate(:pAccountID),UpdateUserID = :pUpdateUserID " + " WHERE ProbationID = :pProbationID"; OracleParameter[] parmeters2 = new OracleParameter[] { new OracleParameter(":pAccountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":pUpdateUserID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), new OracleParameter(":pProbationID", OracleDbType.Int32, intProbationID, ParameterDirection.Input) }; pTrConn.ExecuteNonQuery(strSql2, parmeters2); } } #endregion #region 工资结算 ///

/// 添加工资结算信息 ///

/// 总结算信息实体 /// 各明细结算信息数据集 /// 结算用生产数据 /// 结算用考勤数据 /// 当前操作用户 /// 结果值 public static int AddSalarySettlement(GetSalaryEntity salaryEntity, DataSet dsStaffSalary, DataSet dsBase,SUserInfo userInfo) { int returnRows = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); //首先添加总结算信息 StringBuilder sbSql = new StringBuilder(); sbSql.Clear(); sbSql.Append("select SEQ_SSM_SalarySettlement_ID.nextval from dual"); int id = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString())); sbSql.Clear(); sbSql.Append("Insert into TP_SSM_SalarySettlement"); sbSql.Append(" (SalarySettlementID,AccountMonth,AccountDateFrom,AccountDateTo,SettlementAmount,AdjustmentAmount,"); sbSql.Append(" TotalAmount,Remarks,AccountID,CreateUserID,UpdateUserID)"); sbSql.Append(" values (:SalarySettlementID,:AccountMonth,:AccountDateFrom,:AccountDateTo,:SettlementAmount,:AdjustmentAmount,"); sbSql.Append(" :TotalAmount,:Remarks,:AccountID,:CreateUserID,:UpdateUserID)"); OracleParameter[] Paras = new OracleParameter[] { new OracleParameter(":SalarySettlementID",OracleDbType.Int32, id,ParameterDirection.Input), new OracleParameter(":AccountMonth",OracleDbType.Date, salaryEntity.AccountMonth,ParameterDirection.Input), new OracleParameter(":AccountDateFrom",OracleDbType.Date, salaryEntity.SalaryDateS,ParameterDirection.Input), new OracleParameter(":AccountDateTo",OracleDbType.Date, salaryEntity.SalaryDateE,ParameterDirection.Input), new OracleParameter(":SettlementAmount",OracleDbType.Decimal, salaryEntity.SettlementAmount,ParameterDirection.Input), new OracleParameter(":AdjustmentAmount",OracleDbType.Decimal, salaryEntity.AdjustmentAmount,ParameterDirection.Input), new OracleParameter(":TotalAmount",OracleDbType.Decimal, salaryEntity.TotalAmount,ParameterDirection.Input), new OracleParameter(":Remarks",OracleDbType.NVarchar2, salaryEntity.Remarks,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input) }; returnRows += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), Paras); //然后循环添加各个结算员工工资的总信息 foreach(DataRow drStaffSalaryFor in dsStaffSalary.Tables[0].Rows) { sbSql.Clear(); sbSql.Append("select SEQ_SSM_SSStaffSalaryID.nextval from dual"); int ssid = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString())); sbSql.Clear(); sbSql.Append("Insert into TP_SSM_StaffSalary"); sbSql.Append(" (StaffSalaryID,SalarySettlementID,StaffID,AccountMonth,AccountDateFrom,AccountDateTo,"); sbSql.Append(" BasicSalary,ManagerSalary,WagesSalary,ScrapFine,DefectFine,ProgressSalary,"); sbSql.Append(" AdminSalary,SettlementAmount,AdjustmentAmount,TotalAmount,AuditStatus,AccountID,CreateUserID,UpdateUserID)"); sbSql.Append(" values (:StaffSalaryID,:SalarySettlementID,:StaffID,:AccountMonth,:AccountDateFrom,:AccountDateTo,"); sbSql.Append(" :BasicSalary,:ManagerSalary,:WagesSalary,:ScrapFine,:DefectFine,:ProgressSalary,"); sbSql.Append(" :AdminSalary,:SettlementAmount,:AdjustmentAmount,:TotalAmount,:AuditStatus,:AccountID,:CreateUserID,:UpdateUserID)"); OracleParameter[] SSParas = new OracleParameter[] { new OracleParameter(":StaffSalaryID",OracleDbType.Int32, ssid,ParameterDirection.Input), new OracleParameter(":SalarySettlementID",OracleDbType.Int32, id,ParameterDirection.Input), new OracleParameter(":StaffID",OracleDbType.Int32, drStaffSalaryFor["StaffID"],ParameterDirection.Input), new OracleParameter(":AccountMonth",OracleDbType.Date, drStaffSalaryFor["AccountMonth"],ParameterDirection.Input), new OracleParameter(":AccountDateFrom",OracleDbType.Date, drStaffSalaryFor["AccountDateFrom"],ParameterDirection.Input), new OracleParameter(":AccountDateTo",OracleDbType.Date, drStaffSalaryFor["AccountDateTo"],ParameterDirection.Input), new OracleParameter(":BasicSalary",OracleDbType.Decimal, drStaffSalaryFor["BasicSalary"],ParameterDirection.Input), new OracleParameter(":ManagerSalary",OracleDbType.Decimal, drStaffSalaryFor["ManagerSalary"],ParameterDirection.Input), new OracleParameter(":WagesSalary",OracleDbType.Decimal, drStaffSalaryFor["WagesSalary"],ParameterDirection.Input), new OracleParameter(":ScrapFine",OracleDbType.Decimal, drStaffSalaryFor["ScrapFine"],ParameterDirection.Input), new OracleParameter(":DefectFine",OracleDbType.Decimal, drStaffSalaryFor["DefectFine"],ParameterDirection.Input), new OracleParameter(":ProgressSalary",OracleDbType.Decimal, drStaffSalaryFor["ProgressSalary"],ParameterDirection.Input), new OracleParameter(":AdminSalary",OracleDbType.Decimal, drStaffSalaryFor["AdminSalary"],ParameterDirection.Input), new OracleParameter(":SettlementAmount",OracleDbType.Decimal, drStaffSalaryFor["SettlementAmount"],ParameterDirection.Input), new OracleParameter(":AdjustmentAmount",OracleDbType.Decimal, drStaffSalaryFor["AdjustmentAmount"],ParameterDirection.Input), new OracleParameter(":TotalAmount",OracleDbType.Decimal, drStaffSalaryFor["TotalAmount"],ParameterDirection.Input), new OracleParameter(":AuditStatus",OracleDbType.Int32, drStaffSalaryFor["AuditStatus"],ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input) }; returnRows += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), SSParas); //插入总信息成功后,根据员工ID筛选该员工的各明细信息 //基本工资 DataTable dtBasicSalary = dsStaffSalary.Tables[1]; dtBasicSalary.DefaultView.RowFilter = " StaffId=" + drStaffSalaryFor["StaffID"]; DataTable dtBasicSalaryNow = dtBasicSalary.DefaultView.ToTable(); foreach(DataRow drNow in dtBasicSalaryNow.Rows) { sbSql.Clear(); sbSql.Append("select SEQ_SSM_BSBasicSalaryID.nextval from dual"); int bsid = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString())); sbSql.Clear(); sbSql.Append("Insert into TP_SSM_BasicSalary"); sbSql.Append(" (BasicSalaryID,StaffSalaryID,StaffID,JobsID,SalaryType,BeginDate,EndDate,BasicSalary,Subsidy,"); sbSql.Append(" CheckedDays,AttendanceDays,AbsenceDays,FineAmount,TotalAmount,AccountID,CreateUserID,UpdateUserID)"); sbSql.Append(" values (:BasicSalaryID,:StaffSalaryID,:StaffID,:JobsID,:SalaryType,:BeginDate,:EndDate,:BasicSalary,:Subsidy,"); sbSql.Append(" :CheckedDays,:AttendanceDays,:AbsenceDays,:FineAmount,:TotalAmount,:AccountID,:CreateUserID,:UpdateUserID)"); OracleParameter[] BSParas = new OracleParameter[] { new OracleParameter(":BasicSalaryID",OracleDbType.Int32, bsid,ParameterDirection.Input), new OracleParameter(":StaffSalaryID",OracleDbType.Int32, ssid,ParameterDirection.Input), new OracleParameter(":StaffID",OracleDbType.Int32, drNow["StaffID"],ParameterDirection.Input), new OracleParameter(":JobsID",OracleDbType.Int32, drNow["JobsID"],ParameterDirection.Input), new OracleParameter(":SalaryType",OracleDbType.Int32, Convert.ToInt32(drNow["SalaryType"])-1,ParameterDirection.Input), new OracleParameter(":BeginDate",OracleDbType.Date, drNow["BeginDate"],ParameterDirection.Input), new OracleParameter(":EndDate",OracleDbType.Date, drNow["EndDate"],ParameterDirection.Input), new OracleParameter(":BasicSalary",OracleDbType.Decimal, drNow["BasicSalary"],ParameterDirection.Input), new OracleParameter(":Subsidy",OracleDbType.Decimal, drNow["Subsidy"],ParameterDirection.Input), new OracleParameter(":CheckedDays",OracleDbType.Decimal, drNow["CheckedDays"],ParameterDirection.Input), new OracleParameter(":AttendanceDays",OracleDbType.Decimal, drNow["AttendanceDays"],ParameterDirection.Input), new OracleParameter(":AbsenceDays",OracleDbType.Decimal, drNow["AbsenceDays"],ParameterDirection.Input), new OracleParameter(":FineAmount",OracleDbType.Decimal, drNow["FineAmount"],ParameterDirection.Input), new OracleParameter(":TotalAmount",OracleDbType.Decimal, drNow["TotalAmount"],ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input) }; returnRows += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), BSParas); } //计件工资 DataTable dtWages = dsStaffSalary.Tables[2]; dtWages.DefaultView.RowFilter = " StaffId=" + drStaffSalaryFor["StaffID"]; DataTable dtWagesNow = dtWages.DefaultView.ToTable(); foreach (DataRow drNow in dtWagesNow.Rows) { sbSql.Clear(); sbSql.Append("select SEQ_SSM_Wages_WagesID.nextval from dual"); int wid = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString())); sbSql.Clear(); sbSql.Append("Insert into TP_SSM_Wages"); sbSql.Append(" (WagesID,StaffSalaryID,StaffID,WagesJobs,PriceType,SalaryType,AccountDateFrom,AccountDateTo,BarCode,KilnID,"); sbSql.Append(@" KilnCarID,IntoKilnTime,OutKilnTime,GoodsId,GoodsLevelTypeID, Wages,Salary,Remarks,AccountID,CreateUserID,UpdateUserID)"); sbSql.Append(" values (:WagesID,:StaffSalaryID,:StaffID,:WagesJobs,:PriceType,:SalaryType,:AccountDateFrom,:AccountDateTo,:BarCode,:KilnID,"); sbSql.Append(@" :KilnCarID,:IntoKilnTime,:OutKilnTime,:GoodsId,:GoodsLevelTypeID, :Wages,:Salary,:Remarks,:AccountID,:CreateUserID,:UpdateUserID)"); OracleParameter[] WParas = new OracleParameter[] { new OracleParameter(":WagesID",OracleDbType.Int32, wid,ParameterDirection.Input), new OracleParameter(":StaffSalaryID",OracleDbType.Int32, ssid,ParameterDirection.Input), new OracleParameter(":StaffID",OracleDbType.Int32, drNow["StaffID"],ParameterDirection.Input), new OracleParameter(":WagesJobs",OracleDbType.Int32, drNow["WagesJobs"],ParameterDirection.Input), new OracleParameter(":PriceType",OracleDbType.Int32, drNow["PriceType"],ParameterDirection.Input), new OracleParameter(":SalaryType",OracleDbType.Int32, drNow["SalaryType"],ParameterDirection.Input), new OracleParameter(":AccountDateFrom",OracleDbType.Date, drNow["AccountDateFrom"],ParameterDirection.Input), new OracleParameter(":AccountDateTo",OracleDbType.Date, drNow["AccountDateTo"],ParameterDirection.Input), new OracleParameter(":BarCode",OracleDbType.NVarchar2, drNow["BarCode"],ParameterDirection.Input), new OracleParameter(":KilnID",OracleDbType.Int32, drNow["KilnID"],ParameterDirection.Input), new OracleParameter(":KilnCarID",OracleDbType.Int32, drNow["KilnCarID"],ParameterDirection.Input), new OracleParameter(":IntoKilnTime",OracleDbType.Date, drNow["IntoKilnTime"],ParameterDirection.Input), new OracleParameter(":OutKilnTime",OracleDbType.Date, drNow["OutKilnTime"],ParameterDirection.Input), new OracleParameter(":GoodsId",OracleDbType.Int32, drNow["GoodsId"],ParameterDirection.Input), new OracleParameter(":GoodsLevelTypeID",OracleDbType.Int32, drNow["GoodsLevelTypeID"],ParameterDirection.Input), new OracleParameter(":Wages",OracleDbType.Decimal, drNow["Wages"],ParameterDirection.Input), new OracleParameter(":Salary",OracleDbType.Decimal, drNow["Salary"],ParameterDirection.Input), new OracleParameter(":Remarks",OracleDbType.NVarchar2, drNow["Remarks"],ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input) }; returnRows += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), WParas); } //缺陷扣罚 DataTable dtDefectFine = dsStaffSalary.Tables[3]; dtDefectFine.DefaultView.RowFilter = " StaffId=" + drStaffSalaryFor["StaffID"]; DataTable dtDefectFineNow = dtDefectFine.DefaultView.ToTable(); foreach (DataRow drNow in dtDefectFineNow.Rows) { sbSql.Clear(); sbSql.Append("select SEQ_SSM_DefectFine_DefectID.nextval from dual"); int dfid = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString())); sbSql.Clear(); sbSql.Append("Insert into TP_SSM_DefectFine"); sbSql.Append(" (DefectFineID,StaffSalaryID,StaffID,StaffStatus,JobsID,SalaryType,BeginDate,EndDate,BarCode,"); sbSql.Append(" DefectFine,FineType,FineAmount,AccountID,CreateUserID,UpdateUserID)"); sbSql.Append(" values (:DefectFineID,:StaffSalaryID,:StaffID,:StaffStatus,:JobsID,:SalaryType,:BeginDate,:EndDate,:BarCode,"); sbSql.Append(" :DefectFine,:FineType,:FineAmount,:AccountID,:CreateUserID,:UpdateUserID)"); OracleParameter[] WParas = new OracleParameter[] { new OracleParameter(":DefectFineID",OracleDbType.Int32, dfid,ParameterDirection.Input), new OracleParameter(":StaffSalaryID",OracleDbType.Int32, ssid,ParameterDirection.Input), new OracleParameter(":StaffID",OracleDbType.Int32, drNow["StaffID"],ParameterDirection.Input), new OracleParameter(":StaffStatus",OracleDbType.Int32, drNow["StaffStatus"],ParameterDirection.Input), new OracleParameter(":JobsID",OracleDbType.Int32, drNow["JobsID"],ParameterDirection.Input), new OracleParameter(":SalaryType",OracleDbType.Int32, drNow["SalaryType"],ParameterDirection.Input), new OracleParameter(":BeginDate",OracleDbType.Date, drNow["BeginDate"],ParameterDirection.Input), new OracleParameter(":EndDate",OracleDbType.Date, drNow["EndDate"],ParameterDirection.Input), new OracleParameter(":BarCode",OracleDbType.NVarchar2, drNow["BarCode"],ParameterDirection.Input), new OracleParameter(":DefectFine",OracleDbType.Decimal, drNow["DefectFine"],ParameterDirection.Input), new OracleParameter(":FineType",OracleDbType.Int32, drNow["FineType"],ParameterDirection.Input), new OracleParameter(":FineAmount",OracleDbType.Decimal, drNow["FineAmount"],ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input) }; returnRows += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), WParas); } //损坯扣罚 DataTable dtScrap = dsStaffSalary.Tables[4]; dtScrap.DefaultView.RowFilter = " StaffId=" + drStaffSalaryFor["StaffID"]; DataTable dtScrapNow = dtScrap.DefaultView.ToTable(); foreach (DataRow drNow in dtScrapNow.Rows) { sbSql.Clear(); sbSql.Append("select SEQ_SSM_ScrapFine_ScrapID.nextval from dual"); int Sid = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString())); sbSql.Clear(); sbSql.Append("Insert into TP_SSM_ScrapFine"); sbSql.Append(" (ScrapFineID,StaffSalaryID,StaffID,StaffStatus,JobsID,BeginDate,EndDate,BarCode,"); sbSql.Append(" GoodsID,ScrapTime,ScrapFine,AccountID,CreateUserID,UpdateUserID)"); sbSql.Append(" values (:ScrapFineID,:StaffSalaryID,:StaffID,:StaffStatus,:JobsID,:BeginDate,:EndDate,:BarCode,"); sbSql.Append(" :GoodsID,:ScrapTime,:ScrapFine,:AccountID,:CreateUserID,:UpdateUserID)"); OracleParameter[] SParas = new OracleParameter[] { new OracleParameter(":ScrapFineID",OracleDbType.Int32, Sid,ParameterDirection.Input), new OracleParameter(":StaffSalaryID",OracleDbType.Int32, ssid,ParameterDirection.Input), new OracleParameter(":StaffID",OracleDbType.Int32, drNow["StaffID"],ParameterDirection.Input), new OracleParameter(":StaffStatus",OracleDbType.Int32, drNow["StaffStatus"],ParameterDirection.Input), new OracleParameter(":JobsID",OracleDbType.Int32, drNow["JobsID"],ParameterDirection.Input), new OracleParameter(":BeginDate",OracleDbType.Date, drNow["BeginDate"],ParameterDirection.Input), new OracleParameter(":EndDate",OracleDbType.Date, drNow["EndDate"],ParameterDirection.Input), new OracleParameter(":BarCode",OracleDbType.NVarchar2, drNow["BarCode"],ParameterDirection.Input), new OracleParameter(":GoodsID",OracleDbType.Int32, drNow["GoodsID"],ParameterDirection.Input), new OracleParameter(":ScrapTime",OracleDbType.Date, drNow["ScrapTime"],ParameterDirection.Input), new OracleParameter(":ScrapFine",OracleDbType.Decimal, drNow["ScrapFine"],ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input) }; returnRows += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), SParas); } //行政奖惩 DataTable dtAdmin = dsStaffSalary.Tables[5]; dtAdmin.DefaultView.RowFilter = " StaffId=" + drStaffSalaryFor["StaffID"]; DataTable dtAdminNow = dtAdmin.DefaultView.ToTable(); foreach(DataRow drNow in dtAdminNow.Rows) { sbSql.Clear(); sbSql.Append("select SEQ_SSM_Admin_ProgressID.nextval from dual"); int Aid = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString())); sbSql.Clear(); sbSql.Append("Insert into TP_SSM_Admin"); sbSql.Append(" (AdminID,StaffSalaryID,StaffID,JobsID,SalaryType,BeginDate,EndDate,TATAdminID,"); sbSql.Append(" AdministrationType,BaseAmount,IsEnd,RAPAmount,TotalAmount,AccountID,CreateUserID,UpdateUserID)"); sbSql.Append(" values (:AdminID,:StaffSalaryID,:StaffID,:JobsID,:SalaryType,:BeginDate,:EndDate,:TATAdminID,"); sbSql.Append(" :AdministrationType,:BaseAmount,:IsEnd,:RAPAmount,:TotalAmount,:AccountID,:CreateUserID,:UpdateUserID)"); OracleParameter[] AParas = new OracleParameter[] { new OracleParameter(":AdminID",OracleDbType.Int32, Aid,ParameterDirection.Input), new OracleParameter(":StaffSalaryID",OracleDbType.Int32, ssid,ParameterDirection.Input), new OracleParameter(":StaffID",OracleDbType.Int32, drNow["StaffID"],ParameterDirection.Input), new OracleParameter(":JobsID",OracleDbType.Int32, drNow["JobsID"],ParameterDirection.Input), new OracleParameter(":SalaryType",OracleDbType.Int32, drNow["SalaryType"],ParameterDirection.Input), new OracleParameter(":BeginDate",OracleDbType.Date, drNow["BeginDate"],ParameterDirection.Input), new OracleParameter(":EndDate",OracleDbType.Date, drNow["EndDate"],ParameterDirection.Input), new OracleParameter(":TATAdminID",OracleDbType.Int32, drNow["TATAdminID"],ParameterDirection.Input), new OracleParameter(":AdministrationType",OracleDbType.Int32, drNow["AdministrationType"],ParameterDirection.Input), new OracleParameter(":BaseAmount",OracleDbType.Decimal, drNow["BaseAmount"],ParameterDirection.Input), new OracleParameter(":IsEnd",OracleDbType.Int32, drNow["IsEnd"],ParameterDirection.Input), new OracleParameter(":RAPAmount",OracleDbType.Decimal, drNow["RAPAmount"],ParameterDirection.Input), new OracleParameter(":TotalAmount",OracleDbType.Decimal, drNow["TotalAmount"],ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input) }; returnRows += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), AParas); //然后根据员工ID以及策略ID,获取该条行政奖惩的明细信息 DataTable dtAdminDetail = dsStaffSalary.Tables[6]; dtAdminDetail.DefaultView.RowFilter = " StaffId=" + drStaffSalaryFor["StaffID"] + " And AdminID=" + drNow["TATAdminID"] + " And AccountID=" + drNow["SalaryType"] + " And StaffSalaryID=" + drNow["JobsId"]; DataTable dtAdminDetailNow = dtAdminDetail.DefaultView.ToTable(); foreach(DataRow drDetailNow in dtAdminDetailNow.Rows) { sbSql.Clear(); sbSql.Append("Insert into TP_SSM_AdminDetail"); sbSql.Append(" (AdminRAPID,AdminID,StaffSalaryID,StaffID,AccountID,CreateUserID,UpdateUserID)"); sbSql.Append(" values (:AdminRAPID,:AdminID,:StaffSalaryID,:StaffID,:AccountID,:CreateUserID,:UpdateUserID)"); OracleParameter[] ADParas = new OracleParameter[] { new OracleParameter(":AdminRAPID",OracleDbType.Int32, drDetailNow["AdminRAPID"],ParameterDirection.Input), new OracleParameter(":AdminID",OracleDbType.Int32, Aid,ParameterDirection.Input), new OracleParameter(":StaffSalaryID",OracleDbType.Int32, ssid,ParameterDirection.Input), new OracleParameter(":StaffID",OracleDbType.Int32, drDetailNow["StaffID"],ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input) }; returnRows += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), ADParas); } } //进度奖惩 DataTable dtProgress = dsStaffSalary.Tables[7]; dtProgress.DefaultView.RowFilter = " StaffId=" + drStaffSalaryFor["StaffID"]; DataTable dtProgressNow = dtProgress.DefaultView.ToTable(); foreach (DataRow drNow in dtProgressNow.Rows) { sbSql.Clear(); sbSql.Append("select SEQ_SSM_Progress_ProgressID.nextval from dual"); int Pid = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString())); sbSql.Clear(); sbSql.Append("Insert into TP_SSM_Progress"); sbSql.Append(" (ProgressID,StaffSalaryID,StaffID,JobsID,SalaryType,BeginDate,EndDate,TATProgressID,"); sbSql.Append(" BaseAmount,IsEnd,RAPAmount,TotalAmount,AccountID,CreateUserID,UpdateUserID)"); sbSql.Append(" values (:ProgressID,:StaffSalaryID,:StaffID,:JobsID,:SalaryType,:BeginDate,:EndDate,:TATProgressID,"); sbSql.Append(" :BaseAmount,:IsEnd,:RAPAmount,:TotalAmount,:AccountID,:CreateUserID,:UpdateUserID)"); OracleParameter[] PParas = new OracleParameter[] { new OracleParameter(":ProgressID",OracleDbType.Int32, Pid,ParameterDirection.Input), new OracleParameter(":StaffSalaryID",OracleDbType.Int32, ssid,ParameterDirection.Input), new OracleParameter(":StaffID",OracleDbType.Int32, drNow["StaffID"],ParameterDirection.Input), new OracleParameter(":JobsID",OracleDbType.Int32, drNow["JobsID"],ParameterDirection.Input), new OracleParameter(":SalaryType",OracleDbType.Int32, drNow["SalaryType"],ParameterDirection.Input), new OracleParameter(":BeginDate",OracleDbType.Date, drNow["BeginDate"],ParameterDirection.Input), new OracleParameter(":EndDate",OracleDbType.Date, drNow["EndDate"],ParameterDirection.Input), new OracleParameter(":TATProgressID",OracleDbType.Int32, drNow["TATProgressID"],ParameterDirection.Input), new OracleParameter(":BaseAmount",OracleDbType.Decimal, drNow["BaseAmount"],ParameterDirection.Input), new OracleParameter(":IsEnd",OracleDbType.Int32, drNow["IsEnd"],ParameterDirection.Input), new OracleParameter(":RAPAmount",OracleDbType.Decimal, drNow["RAPAmount"],ParameterDirection.Input), new OracleParameter(":TotalAmount",OracleDbType.Decimal, drNow["TotalAmount"],ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input) }; returnRows += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), PParas); //然后根据员工ID以及策略ID,获取该条行政奖惩的明细信息(前台把区分用的工种ID存在主信息ID中了) DataTable dtProgressDetail = dsStaffSalary.Tables[8]; dtProgressDetail.DefaultView.RowFilter = " StaffId=" + drStaffSalaryFor["StaffID"] + " And ProgressID=" + drNow["JobsID"] + " And AccountID=" + drNow["SalaryType"]; DataTable dtProgressDetailNow = dtProgressDetail.DefaultView.ToTable(); foreach (DataRow drDetailNow in dtProgressDetailNow.Rows) { sbSql.Clear(); sbSql.Append("Insert into TP_SSM_ProgressDetail"); sbSql.Append(" (ProgressRAPID,ProgressID,StaffSalaryID,StaffID,AccountID,CreateUserID,UpdateUserID)"); sbSql.Append(" values (:ProgressRAPID,:ProgressID,:StaffSalaryID,:StaffID,:AccountID,:CreateUserID,:UpdateUserID)"); OracleParameter[] PDParas = new OracleParameter[] { new OracleParameter(":ProgressRAPID",OracleDbType.Int32, drDetailNow["ProgressRAPID"],ParameterDirection.Input), new OracleParameter(":ProgressID",OracleDbType.Int32, Pid,ParameterDirection.Input), new OracleParameter(":StaffSalaryID",OracleDbType.Int32, ssid,ParameterDirection.Input), new OracleParameter(":StaffID",OracleDbType.Int32, drDetailNow["StaffID"],ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input) }; returnRows += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), PDParas); } } //管理岗位工资 DataTable dtManagerSalary = dsStaffSalary.Tables[9]; dtManagerSalary.DefaultView.RowFilter = " StaffId=" + drStaffSalaryFor["StaffID"]; DataTable dtManagerSalaryNow = dtManagerSalary.DefaultView.ToTable(); foreach (DataRow drNow in dtManagerSalaryNow.Rows) { sbSql.Clear(); sbSql.Append("select SEQ_SSM_M_ManagerSalaryID.nextval from dual"); int Mid = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString())); sbSql.Clear(); sbSql.Append("Insert into TP_SSM_ManagerSalary"); sbSql.Append(" (ManagerSalaryID,StaffSalaryID,StaffID,Member,Salary,AccountID,CreateUserID,UpdateUserID)"); sbSql.Append(" values (:ManagerSalaryID,:StaffSalaryID,:StaffID,:Member,:Salary,:AccountID,:CreateUserID,:UpdateUserID)"); OracleParameter[] MParas = new OracleParameter[] { new OracleParameter(":ManagerSalaryID",OracleDbType.Int32, Mid,ParameterDirection.Input), new OracleParameter(":StaffSalaryID",OracleDbType.Int32, ssid,ParameterDirection.Input), new OracleParameter(":StaffID",OracleDbType.Int32, drNow["StaffID"],ParameterDirection.Input), new OracleParameter(":Member",OracleDbType.Int32, drNow["Member"],ParameterDirection.Input), new OracleParameter(":Salary",OracleDbType.Decimal, drNow["Salary"],ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input) }; returnRows += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), MParas); } } //结算完毕后要把所有相关的常规数据修改为已结算 //sbSql.Clear(); //sbSql.Append("Update TP_PM_ProductionData set SettlementFlag = 1 where ProductionDataID = :ProductionDataID"); //foreach(DataRow drFor in dsProductionData.Tables[0].Rows) //{ // OracleParameter[] PDParas = new OracleParameter[] { // new OracleParameter(":ProductionDataID",OracleDbType.Int32, // drFor["ProductionDataID"],ParameterDirection.Input) // }; // returnRows = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), PDParas); //} //sbSql.Clear(); //sbSql.Append("Update TP_HR_StaffAttendance set SettlementFlag = 1 where StaffID = :StaffID and AttendanceDate=:AttendanceDate"); //foreach(DataRow drFor in dsStaffAttendance.Tables[0].Rows) //{ // OracleParameter[] HRParas = new OracleParameter[] { // new OracleParameter(":StaffID",OracleDbType.Int32, // drFor["StaffID"],ParameterDirection.Input), // new OracleParameter(":AttendanceDate",OracleDbType.Date, // drFor["AttendanceDate"],ParameterDirection.Input) // }; // returnRows = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), HRParas); //} oracleTrConn.Commit(); oracleTrConn.Disconnect(); } catch(Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } } return returnRows; } ///

/// 调整工资结算信息 ///

/// 调整工资总体信息 /// 各员工工资信息集合 /// 影响行数 public static int EditSalarySettlement(GetSalaryEntity salaryEntity, DataTable dtStaffSalary) { int returnRows = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { //首先修改工资结算整体信息 StringBuilder sbSql = new StringBuilder(); sbSql.Append(@"Update TP_SSM_SalarySettlement Set Remarks = :Remarks,AdjustmentAmount = :AdjustmentAmount,TotalAmount = :TotalAmount Where SalarySettlementID = :SalarySettlementID"); OracleParameter[] SParas = new OracleParameter[] { new OracleParameter(":Remarks",OracleDbType.NVarchar2, salaryEntity.Remarks,ParameterDirection.Input), new OracleParameter(":AdjustmentAmount",OracleDbType.Decimal, salaryEntity.AdjustmentAmount,ParameterDirection.Input), new OracleParameter(":TotalAmount",OracleDbType.Decimal, salaryEntity.TotalAmount,ParameterDirection.Input), new OracleParameter(":SalarySettlementID",OracleDbType.Int32, salaryEntity.SalarySettlementID,ParameterDirection.Input) }; returnRows += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), SParas); //然后遍历修改每一条调整信息 sbSql.Clear(); sbSql.Append(@"Update TP_SSM_StaffSalary Set AdjustmentAmount = :AdjustmentAmount, TotalAmount = :TotalAmount,Remarks = :Remarks Where StaffSalaryID = :StaffSalaryID"); foreach(DataRow drFor in dtStaffSalary.Rows) { if(drFor.RowState == DataRowState.Modified) { OracleParameter[] SSParas = new OracleParameter[] { new OracleParameter(":AdjustmentAmount",OracleDbType.Decimal, drFor["AdjustmentAmount"],ParameterDirection.Input), new OracleParameter(":TotalAmount",OracleDbType.Decimal, drFor["TotalAmount"],ParameterDirection.Input), new OracleParameter(":Remarks",OracleDbType.NVarchar2, drFor["Remarks"],ParameterDirection.Input), new OracleParameter(":StaffSalaryID",OracleDbType.Int32, drFor["StaffSalaryID"],ParameterDirection.Input) }; returnRows += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), SSParas); } } oracleTrConn.Commit(); oracleTrConn.Disconnect(); return returnRows; } catch(Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } } ///

/// 审批工资结算信息 ///

/// 各员工工资信息集合 /// 影响行数 public static int AuditSalarySettlement(DataTable dtStaffSalary,SUserInfo userInfo) { int returnRows = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { StringBuilder sbSql = new StringBuilder(); //遍历修改每一条调整信息 sbSql.Append(@"Update TP_SSM_StaffSalary Set AuditStatus = :AuditStatus, Auditor = :Auditor,AuditlDate = :AuditlDate Where StaffSalaryID = :StaffSalaryID"); foreach (DataRow drFor in dtStaffSalary.Rows) { if (drFor.RowState == DataRowState.Modified) { OracleParameter[] SSParas = new OracleParameter[] { new OracleParameter(":AuditStatus",OracleDbType.Int32, drFor["AuditStatus"],ParameterDirection.Input), new OracleParameter(":Auditor",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":AuditlDate",OracleDbType.Date, DateTime.Now,ParameterDirection.Input), new OracleParameter(":StaffSalaryID",OracleDbType.Int32, drFor["StaffSalaryID"],ParameterDirection.Input) }; returnRows += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), SSParas); } } oracleTrConn.Commit(); oracleTrConn.Disconnect(); return returnRows; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } } #endregion } }