IMEX.mes.wcf/DK.Service/HRModuleLogic/HRModuleDAL.cs

/*******************************************************************************
 * 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.DataAccess.Client;

namespace Dongke.IBOSS.PRD.Service.HRModuleLogic
{
	/// <summary>
	/// 员工管理更新db逻辑处理
	/// </summary>
	public static class HRModuleDAL
    {
        #region 员工档案

        /// <summary>
        /// 添加员工档案
        /// </summary>
        /// <param name="staffInfo">员工实体类</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <param name="staffPhoto">员工图片集</param>
        /// <returns>HRResultEntity</returns>
        public static HRResultEntity AddStaffInfo(StaffEntity staffInfo, SUserInfo sUserInfo, List<StaffPhotoEntity> 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();
                }
            }
        }

		/// <summary>
		/// 导入员工档案
		/// </summary>
		/// <param name="staffInfo">员工信息</param>
		/// <param name="sUserInfo">用户基本信息</param>
		/// <returns>ServiceResultEntity</returns>
		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<string> staffCodes = new List<string>();
                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<int> ids = new List<int>();
				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();
                }
            }
		}

        /// <summary>
        /// 更新员工档案
        /// </summary>
        /// <param name="staffInfo">员工实体类</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <param name="staffPhoto">员工图片集</param>
        /// <returns>HRResultEntity</returns>
        public static HRResultEntity EditStaffInfo(StaffEntity staffInfo, SUserInfo sUserInfo, List<StaffPhotoEntity> 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();
                }
            }
        }

        /// <summary>
        /// 设置禁用员工
        /// </summary>
        /// <param name="staffid">员工ID</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>HRResultEntity</returns>
        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 StaffStatus in(0,3) 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 员工入职

        /// <summary>
        /// 添加员工履历
        /// </summary>
        /// <param name="staffrecord">员工履历实体类</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>HRResultEntity</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 编辑员工履历
        /// </summary>
        /// <param name="staffrecord">员工履历实体类</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>HRResultEntity</returns>
        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 员工离职

        /// <summary>
        /// 保存员工离职履历信息
        /// </summary>
        /// <param name="pUserId">员工编号</param>
        /// <param name="pOPTimeStamp">员工档案表时间戳</param>
        /// <param name="pStaffRecord">员工履历表实体</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <param name="pStatus">窗体是新增还是编辑</param>
        /// <returns>0 没有数据被修改  -1员工档案被其他用户修改  -2存在待审批履历  -3员工履历被其他用户修改</returns>
        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;
            }
        }

		/// <summary>
		/// 员工离职操作
		/// </summary>
		/// <param name="cre"></param>
		/// <param name="sUserInfo"></param>
		/// <returns></returns>
		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();
				}
			}
		}

		/// <summary>
		/// 导入员工离职
		/// </summary>
		/// <param name="cre"></param>
		/// <param name="sUserInfo"></param>
		/// <returns></returns>
		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 工种调整

		/// <summary>
		/// 保存工种调整方法；
		/// </summary>
		/// <param name="staffRecordEntity">员工履历实体类</param>
		/// <param name="sUserInfo">用户基本信息</param>
		/// <param name="editStatus">状态 1：新增 2：保存</param>
		/// <returns>0 没有数据被修改  -1存在待审批履历  -2员工履历被其他用户修改  -3员工档案被其他用户修改</returns>
		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();
                }
            }
        }

        /// <summary>
        /// 工种调整审批保存
        /// </summary>
        /// <param name="pUserId">员工编号</param>
        /// <param name="pOPTimeStamp">员工档案表时间戳</param>
        /// <param name="pStaffRecord">员工履历表实体</param>
        /// <param name="pState">审批状态 True 通过 False 不通过</param>
        /// <param name="pMemo">审批意见</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>0 没有数据被修改  -1员工档案被其他用户修改  -2存在待审批履历  -3员工履历被其他用户修改</returns>
        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 职务调整

        /// <summary>
        /// 保存职务调整方法
        /// </summary>
        /// <param name="staffRecordEntity">员工履历实体类</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <param name="editStatus">状态 1：新增 2：保存</param>
        /// <returns>0 没有数据被修改  -1存在待审批履历  -2员工履历被其他用户修改  -3员工档案被其他用户修改</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 职务调整审批保存
        /// </summary>
        /// <param name="pUserId">员工编号</param>
        /// <param name="pOPTimeStamp">员工档案表时间戳</param>
        /// <param name="pStaffRecord">员工履历表实体</param>
        /// <param name="pState">审批状态 True 通过 False 不通过</param>
        /// <param name="pMemo">审批意见</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>0 没有数据被修改  -1员工档案被其他用户修改  -2存在待审批履历  -3员工履历被其他用户修改</returns>
        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 部门调整

        /// <summary>
        /// 保存部门调整方法
        /// </summary>
        /// <param name="staffRecordEntity">员工履历实体类</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <param name="editStatus">状态 1：新增 2：保存</param>
        /// <returns>0 没有数据被修改  -1存在待审批履历  -2员工履历被其他用户修改  -3员工档案被其他用户修改</returns>
        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<OracleParameter> parmeters2 = new List<OracleParameter>();
					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();
                }
            }
        }

        /// <summary>
        /// 部门调整审批保存
        /// </summary>
        /// <param name="pUserId">员工编号</param>
        /// <param name="pOPTimeStamp">员工档案表时间戳</param>
        /// <param name="pStaffRecord">员工履历表实体</param>
        /// <param name="pState">审批状态 True 通过 False 不通过</param>
        /// <param name="pMemo">审批意见</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>0 没有数据被修改  -1员工档案被其他用户修改  -2存在待审批履历  -3员工履历被其他用户修改</returns>
        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<OracleParameter> parmeters2 = new List<OracleParameter>();
                    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 员工离职审批

        /// <summary>
        /// 人事审批保存
        /// </summary>
        /// <param name="pUserId">员工编号</param>
        /// <param name="pOPTimeStamp">员工档案表时间戳</param>
        /// <param name="pStaffRecord">员工履历表实体</param>
        /// <param name="pState">审批状态 True 通过 False 不通过</param>
        /// <param name="pMemo">审批意见</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>0 没有数据被修改  -1员工档案被其他用户修改  -2存在待审批履历  -3员工履历被其他用户修改</returns>
        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 员工履历停用

        /// <summary>
        /// 设置禁用员工履历
        /// </summary>
        /// <param name="staffrecordid">员工履历ID</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>HRResultEntity</returns>
        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 员工入职审批

        /// <summary>
        /// 员工入职审批
        /// </summary>
        /// <param name="pUserId">员工编号</param>
        /// <param name="pOPTimeStamp">员工时间戳</param>
        /// <param name="pStaffRecord">员工履历表实体</param>
        /// <param name="pState">审批状态 True 通过 False 不通过</param>
        /// <param name="pMemo">审批意见</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns></returns>
        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 员工转正审批

        /// <summary>
        /// 员工转正审批
        /// </summary>
        /// <param name="pUserId">员工编号</param>
        /// <param name="pOPTimeStamp">员工档案表的时间戳</param>
        /// <param name="pStaffRecord">员工履历表实体</param>
        /// <param name="pState">审批状态 True 通过  False 不通过</param>
        /// <param name="pMemo">审批原因</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>HRResultEntity</returns>
        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 员工考勤

        /// <summary>
        /// 根据DataTable中的考勤数据更新数据表
        /// </summary>
        /// <param name="pStaffAttendance">考勤数据表</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>HRResultEntity</returns>
        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 行政奖惩

        /// <summary>
        /// 根据传入的实体保存 行政奖惩记录
        /// </summary>
        /// <param name="pAdminRAPEntity">行政奖惩实体</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <param name="pStatus">新增还是编辑枚举</param>
        /// <returns>HRResultEntity</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 行政奖惩审批
        /// </summary>
        /// <param name="pAdminRAPEntity">行政奖惩实体</param>
        /// <param name="pState">审批状态 True 通过  False 不通过</param>
        /// <param name="pMemo">审批原因</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>HRResultEntity</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 设置行政奖惩停用
        /// </summary>
        /// <param name="pRAPID">行政奖惩编号</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>HRResultEntity</returns>
        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 员工报餐

        /// <summary>
        /// 员工报餐保存
        /// </summary>
        /// <param name="pStaff">待保存的数据表</param>
        /// <param name="pStatus">窗口状态枚举</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>HRResultEntity</returns>
        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 私有方法

        /// <summary>
        /// 根据传入的数据更新员工履历表数据
        /// </summary>
        /// <param name="pStaff">员工档案表</param>
        /// <param name="pStaffRecord">员工履历表</param>
        /// <param name="pState">审批状态 True 通过 False 不通过</param>
        /// <param name="pMemo">审批意见</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <param name="pTrConn">数据库事物</param>
        /// <returns>int返回更新数据行数</returns>
        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;
        }

        /// <summary>
        /// 更新试用期表中的试用结束日期
        /// </summary>
        /// <param name="pStaffID">员工编号</param>
        /// <param name="pJobsID">工种编号</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <param name="pTrConn">数据库事务</param>
        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 工资结算
        /// <summary>
        /// 添加工资结算信息
        /// </summary>
        /// <param name="salaryEntity">总结算信息实体</param>
        /// <param name="dsStaffSalary">各明细结算信息数据集</param>
        /// <param name="dsProductionData">结算用生产数据</param>
        /// <param name="dsStaffAttendance">结算用考勤数据</param>
        /// <param name="userInfo">当前操作用户</param>
        /// <returns>结果值</returns>
        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;
        }

        /// <summary>
        /// 调整工资结算信息
        /// </summary>
        /// <param name="salaryEntity">调整工资总体信息</param>
        /// <param name="dtStaffSalary">各员工工资信息集合</param>
        /// <returns>影响行数</returns>
        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;
            }
        }

        /// <summary>
        /// 审批工资结算信息
        /// </summary>
        /// <param name="dtStaffSalary">各员工工资信息集合</param>
        /// <returns>影响行数</returns>
        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
    }
}