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

/*******************************************************************************
 * Copyright(c) 2014 DongkeSoft All rights reserved. / Confidential
 * 类的信息：
 *      1.程序名称：HRModuleLogic.cs
 *      2.功能描述：员工管理查询逻辑处理
 * 编辑履历：
 *      作者            日期                版本            修改内容
 *      王鑫            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.Basics.Library;
using Dongke.IBOSS.PRD.Service.DataModels;
using Dongke.IBOSS.PRD.WCF.DataModels;
using Dongke.IBOSS.PRD.WCF.DataModels.HRModule;
using Oracle.ManagedDataAccess.Client;

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

        /// <summary>
        /// 员工当案一览
        /// </summary>
        /// <param name="pSearchStaff">员工档案实体类/param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet</returns>
        public static DataSet SearchHrStaff(SearchStaffEntity pSearchStaff, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                var strValueFlag = "";
                if (pSearchStaff.ValueFlag != null)
                {
                    if (pSearchStaff.ValueFlag.Value)
                    {
                        strValueFlag = "1";
                    }
                    else
                    {
                        strValueFlag = "0";
                    }
                }
                string strIdList = string.Empty;
                if (pSearchStaff.RStaffRecordIDList != null && pSearchStaff.RStaffRecordIDList.Length > 0)
                {
                    strIdList = DataConvert.ConvertListToSqlInWhere(pSearchStaff.RStaffRecordIDList);
                }
                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter("in_accountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
                    new OracleParameter("in_sStaffID", OracleDbType.Int32, pSearchStaff.StaffID, ParameterDirection.Input),
                    new OracleParameter("in_sStaffCode", OracleDbType.Varchar2, pSearchStaff.StaffCode, ParameterDirection.Input),
                    new OracleParameter("in_sStaffName", OracleDbType.Varchar2, pSearchStaff.StaffName, ParameterDirection.Input),
                    new OracleParameter("in_sIDCardNo", OracleDbType.Varchar2, pSearchStaff.IDCardNo, ParameterDirection.Input),
                    new OracleParameter("in_sStartBirthday", OracleDbType.Date, pSearchStaff.StartBirthday, ParameterDirection.Input),
                    new OracleParameter("in_sEndBirthday", OracleDbType.Date, pSearchStaff.EndBirthday, ParameterDirection.Input),
                    new OracleParameter("in_sGender", OracleDbType.Varchar2, pSearchStaff.Gender, ParameterDirection.Input),
                    new OracleParameter("in_sMaritalStatus", OracleDbType.Int32, pSearchStaff.MaritalStatus, ParameterDirection.Input),
                    new OracleParameter("in_sHomeTown", OracleDbType.Varchar2, pSearchStaff.HomeTown, ParameterDirection.Input),
                    new OracleParameter("in_sPolicitalStatus", OracleDbType.Varchar2, pSearchStaff.PolicitalStatus, ParameterDirection.Input),
                    new OracleParameter("in_sNational", OracleDbType.Int32, pSearchStaff.National, ParameterDirection.Input),
                    new OracleParameter("in_sEducational", OracleDbType.Int32, pSearchStaff.Educational, ParameterDirection.Input),
                    new OracleParameter("in_sGraduated", OracleDbType.Varchar2, pSearchStaff.Graduated, ParameterDirection.Input),
                    new OracleParameter("in_sSpecialField", OracleDbType.Varchar2, pSearchStaff.SpecialField, ParameterDirection.Input),
                    new OracleParameter("in_sTelephone", OracleDbType.Varchar2, pSearchStaff.Telephone, ParameterDirection.Input),
                    new OracleParameter("in_sValueFlag", OracleDbType.Varchar2, strValueFlag, ParameterDirection.Input),
                    new OracleParameter("in_sIDList", OracleDbType.Varchar2, strIdList, ParameterDirection.Input),
                    new OracleParameter("in_staffStatusArray", OracleDbType.Varchar2, pSearchStaff.StaffStatusArray, ParameterDirection.Input),
                    new OracleParameter("out_result", OracleDbType.RefCursor, ParameterDirection.Output),
                };
                con.Open();
                DataSet ds = con.ExecStoredProcedure("PRO_HR_SearchHrStaff", paras);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 是否存在相同的员工编码
        /// </summary>
        /// <param name="staffCode">员工编码</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet</returns>
        public static DataSet IsExistsStaffCode(string staffCode, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                string sqlString = "select 1 from TP_HR_STAFF where accountID=" + sUserInfo.AccountID + " and staffCode='" + staffCode + "'";
                DataSet ds = con.GetSqlResultToDs(sqlString);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 获取员工行数据
        /// </summary>
        /// <param name="staffid">员工ID</param>
        /// <returns>DataSet</returns>
        public static DataSet GetRowData(int staffid)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                string sqlString = @"select staff.*,jobs.jobsname,organ.organizationname,
                                     p.postname,ss.staffstatusname
                                     from TP_HR_STAFF staff
                                     Left join TP_MST_Jobs jobs
                                     on staff.jobs = jobs.jobsid
                                     Left join TP_MST_Organization organ
                                     on staff.organizationid = organ.organizationid
                                     Left join TP_MST_Post p
                                     on staff.post = p.postid
                                     Left join TP_SYS_StaffStatus ss
                                     on staff.staffstatus = ss.staffstatusid
                                     where staff.staffID=" + staffid;
                DataSet ds = con.GetSqlResultToDs(sqlString);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 获取员工图片
        /// </summary>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <param name="staffId">员工ID</param>
        /// <returns></returns>
        public static DataSet GetImageByStaffId(SUserInfo sUserInfo, int staffId)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                string sqlString1 = "SELECT * FROM TP_HR_STAFFPHOTO "
                                    + " WHERE staffid=: staffID AND accountID=:accountID";
                OracleParameter[] paras = new OracleParameter[]
                    {
                        new OracleParameter(":staffID",OracleDbType.Int32,staffId,ParameterDirection.Input),
                        new OracleParameter(":accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
                    };
                return con.GetSqlResultToDs(sqlString1, paras);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        #endregion

        #region 工资结算
        /// <summary>
        /// 根据结算时间获取各基础信息
        /// </summary>
        /// <param name="gsEntity">时间信息实体</param>
        /// <param name="userInfo">用户信息</param>
        /// <returns>基础信息数据集</returns>
        public static DataSet GetSalaryData(GetSalaryEntity gsEntity,SUserInfo userInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter("In_SalaryDateS", OracleDbType.Date, Convert.ToDateTime(gsEntity.SalaryDateS).Date, ParameterDirection.Input),
                    new OracleParameter("In_SalaryDateE", OracleDbType.Date, Convert.ToDateTime(gsEntity.SalaryDateE).Date, ParameterDirection.Input),
                    new OracleParameter("In_AccountID", OracleDbType.Int32, userInfo.AccountID, ParameterDirection.Input),
                    new OracleParameter("In_WagesMainId", OracleDbType.Int32, gsEntity.WagesMainId, ParameterDirection.Input),
                    new OracleParameter("In_WagesMainIdE", OracleDbType.Int32, gsEntity.WagesMainIdE, ParameterDirection.Input),
                    new OracleParameter("Out_ProductionData", OracleDbType.RefCursor, ParameterDirection.Output),
                    new OracleParameter("Out_StaffAttendance", OracleDbType.RefCursor, ParameterDirection.Output),
                    new OracleParameter("Out_AdminRAP", OracleDbType.RefCursor, ParameterDirection.Output),
                    new OracleParameter("Out_Defect", OracleDbType.RefCursor, ParameterDirection.Output),
                    new OracleParameter("Out_ProgressRAP", OracleDbType.RefCursor, ParameterDirection.Output),
                    new OracleParameter("Out_KilnCar", OracleDbType.RefCursor, ParameterDirection.Output),
                    new OracleParameter("Out_Staff", OracleDbType.RefCursor, ParameterDirection.Output),
                    new OracleParameter("Out_StaffRecord", OracleDbType.RefCursor, ParameterDirection.Output),
                    new OracleParameter("Out_UpStaffRecord", OracleDbType.RefCursor, ParameterDirection.Output),
                    new OracleParameter("Out_DisProductionData", OracleDbType.RefCursor, ParameterDirection.Output),
                    new OracleParameter("Out_ScrapProduct", OracleDbType.RefCursor, ParameterDirection.Output),
                    new OracleParameter("Out_Weight", OracleDbType.RefCursor, ParameterDirection.Output)
                };
                con.Open();
                DataSet ds = con.ExecStoredProcedure("PRO_HR_GetSalaryData", paras);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        public static DataSet GetSettlementMain(GetSalaryEntity gsEntity,SUserInfo userInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter("In_SalaryDateS", OracleDbType.Date, gsEntity.SalaryDateS, ParameterDirection.Input),
                    new OracleParameter("In_SalaryDateE", OracleDbType.Date, gsEntity.SalaryDateE, ParameterDirection.Input),
                    new OracleParameter("In_AccountID", OracleDbType.Int32, userInfo.AccountID, ParameterDirection.Input),
                    new OracleParameter("In_Remarks", OracleDbType.NVarchar2, gsEntity.Remarks, ParameterDirection.Input),
                    new OracleParameter("Out_SettlementMain", OracleDbType.RefCursor, ParameterDirection.Output)
                };
                con.Open();
                DataSet ds = con.ExecStoredProcedure("PRO_SSM_GetSettlementMain", paras);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        public static DataSet GetSettlementDetail(int MainId)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                String strSql = @"Select TP_SSM_StaffSalary.*,staffInfo.StaffName,organizationInfo.organizationName,
                                  postInfo.postName from TP_SSM_StaffSalary
                                  Inner join TP_HR_Staff staffInfo
                                  on TP_SSM_StaffSalary.StaffID = staffInfo.StaffID
                                  Inner join TP_MST_ORGANIZATION organizationInfo 
                                  ON staffInfo.Organizationid = organizationInfo.Organizationid
                                  Inner join TP_MST_POST postInfo
                                  ON staffInfo.Post = postInfo.Postid 
                                  where TP_SSM_StaffSalary.SalarySettlementID = " + MainId;
                DataSet ds = con.GetSqlResultToDs(strSql, null);

                if(MainId == 0)
                {
                    //将基本工资的表结构返回
                    strSql = "Select * from TP_SSM_BasicSalary where StaffSalaryID = 0";
                    ds.Tables.Add(con.GetSqlResultToDt(strSql, null));
                    //将出勤考核的表结构带回
                    strSql = "Select * from TP_SSM_Attendance where StaffSalaryID = 0";
                    ds.Tables.Add(con.GetSqlResultToDt(strSql, null));
                    //将计件工资的表结构带回
                    strSql = "Select * from TP_SSM_Wages where StaffSalaryID = 0";
                    ds.Tables.Add(con.GetSqlResultToDt(strSql, null));
                    //将缺陷扣罚的表结构带回
                    strSql = "Select * from TP_SSM_DefectFine where StaffSalaryID = 0";
                    ds.Tables.Add(con.GetSqlResultToDt(strSql, null));
                    //将损坯扣罚的表结构带回
                    strSql = "Select * from TP_SSM_ScrapFine where StaffSalaryID = 0";
                    ds.Tables.Add(con.GetSqlResultToDt(strSql, null));
                    //将行政奖惩的表结构带回(总表以及明细表)
                    strSql = "Select * from TP_SSM_Admin where StaffSalaryID = 0";
                    ds.Tables.Add(con.GetSqlResultToDt(strSql, null));
                    strSql = "Select * from TP_SSM_AdminDetail where StaffSalaryID = 0";
                    ds.Tables.Add(con.GetSqlResultToDt(strSql, null));
                    //将进度奖惩的表结构带回(总表以及明细表)
                    strSql = "Select * from TP_SSM_Progress where StaffSalaryID = 0";
                    ds.Tables.Add(con.GetSqlResultToDt(strSql, null));
                    strSql = "Select * from TP_SSM_ProgressDetail where StaffSalaryID = 0";
                    ds.Tables.Add(con.GetSqlResultToDt(strSql, null));
                    //将管理者工资的表结构带回
                    strSql = "Select * from TP_SSM_ManagerSalary where StaffSalaryID = 0";
                    ds.Tables.Add(con.GetSqlResultToDt(strSql, null));
                }
                else
                {
                    strSql = "Select * from TP_SSM_SalarySettlement where SalarySettlementID = " + MainId;
                    ds.Tables.Add(con.GetSqlResultToDt(strSql, null));
                }

                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 根据员工工资ID以及员工ID，获取员工工资各明细数据集
        /// </summary>
        /// <param name="StaffSalaryID">员工工资ID</param>
        /// <param name="StaffID">员工ID</param>
        /// <returns>各明细集合</returns>
        public static DataSet GetStaffSalaryDetail(int StaffSalaryID, int StaffID)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter("In_StaffSalaryID", OracleDbType.Int32, StaffSalaryID, ParameterDirection.Input),
                    new OracleParameter("In_StaffID", OracleDbType.Int32, StaffID, ParameterDirection.Input),
                    new OracleParameter("Out_BasicSalary", OracleDbType.RefCursor, ParameterDirection.Output),
                    new OracleParameter("Out_Progress", OracleDbType.RefCursor, ParameterDirection.Output),
                    new OracleParameter("Out_Admin", OracleDbType.RefCursor, ParameterDirection.Output),
                    new OracleParameter("Out_Wages", OracleDbType.RefCursor, ParameterDirection.Output),
                    new OracleParameter("Out_DefectFine", OracleDbType.RefCursor, ParameterDirection.Output),
                    new OracleParameter("Out_ScrapFine", OracleDbType.RefCursor, ParameterDirection.Output),
                    new OracleParameter("Out_ManagerSalary", OracleDbType.RefCursor, ParameterDirection.Output),
                    new OracleParameter("Out_ProgressDetail", OracleDbType.RefCursor, ParameterDirection.Output),
                    new OracleParameter("Out_AdminDetail", OracleDbType.RefCursor, ParameterDirection.Output)
                };
                con.Open();
                DataSet ds = con.ExecStoredProcedure("PRO_PM_GetStaffSalaryDetail", paras);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        #endregion

        #region 其他方法
        /// <summary>
        /// 获取员工和员工履历信息
        /// </summary>
        /// <param name="staffEntity">员工实体类</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet</returns>
        public static DataSet SearchHrStaffAndRecord(SearchStaffEntity staffEntity, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                string strIdList = string.Empty;
                if (staffEntity.RStaffRecordIDList != null && staffEntity.RStaffRecordIDList.Length > 0)
                {
                    strIdList = DataConvert.ConvertListToSqlInWhere(staffEntity.RStaffRecordIDList);
                }
                con.Open();
                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter("in_accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
                    new OracleParameter("in_sStaffID",OracleDbType.Int32,staffEntity.StaffID,ParameterDirection.Input),
                    new OracleParameter("in_sstaffcode",OracleDbType.Varchar2,staffEntity.StaffCode,ParameterDirection.Input),
                    new OracleParameter("in_sstaffname",OracleDbType.Varchar2,staffEntity.StaffName,ParameterDirection.Input),
                    new OracleParameter("in_sidcardno",OracleDbType.Varchar2,staffEntity.IDCardNo,ParameterDirection.Input),
                    new OracleParameter("in_sstartbirthday",OracleDbType.Date,staffEntity.StartBirthday,ParameterDirection.Input),
                    new OracleParameter("in_sendbirthday",OracleDbType.Date,staffEntity.EndBirthday,ParameterDirection.Input),
                    new OracleParameter("in_sgender",OracleDbType.Varchar2,staffEntity.Gender,ParameterDirection.Input),
                    new OracleParameter("in_smaritalstatus",OracleDbType.Int32,staffEntity.MaritalStatus,ParameterDirection.Input),
                    new OracleParameter("in_shometown",OracleDbType.Varchar2,staffEntity.HomeTown,ParameterDirection.Input),
                    new OracleParameter("in_spolicitalstatus",OracleDbType.Varchar2,staffEntity.PolicitalStatus,ParameterDirection.Input),
                    new OracleParameter("in_snational",OracleDbType.Int32,staffEntity.National,ParameterDirection.Input),
                    new OracleParameter("in_seducational",OracleDbType.Int32,staffEntity.Educational,ParameterDirection.Input),
                    new OracleParameter("in_sgraduated",OracleDbType.Varchar2,staffEntity.Graduated,ParameterDirection.Input),
                    new OracleParameter("in_sspecialfield",OracleDbType.Varchar2,staffEntity.SpecialField,ParameterDirection.Input),
                    new OracleParameter("in_stelephone",OracleDbType.Varchar2,staffEntity.Telephone,ParameterDirection.Input),
                    new OracleParameter("in_rStaffRecordID",OracleDbType.Int32,staffEntity.RStaffRecordID,ParameterDirection.Input),
                    new OracleParameter("in_rrecordtype",OracleDbType.Int32,staffEntity.Recordtype,ParameterDirection.Input),
                    new OracleParameter("in_rvalueflag",OracleDbType.Int32,staffEntity.RValueflag,ParameterDirection.Input),
                    new OracleParameter("in_sIDList", OracleDbType.Varchar2, strIdList, ParameterDirection.Input),
                    new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output),
                };
                DataSet ds = con.ExecStoredProcedure("PRO_HR_SearchHrStaffAndRecord", paras);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 获取员工信息中审批信息
        /// </summary>
        /// <param name="staffEntity">员工实体类</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet</returns>
        public static DataSet SearchHrStaffApprove(SearchStaffEntity staffEntity, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                string strIdList = string.Empty;
                if (staffEntity.RStaffRecordIDList != null && staffEntity.RStaffRecordIDList.Length > 0)
                {
                    strIdList = DataConvert.ConvertListToSqlInWhere(staffEntity.RStaffRecordIDList);
                }
                con.Open();
                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter("in_userid ",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input),
                    new OracleParameter("in_accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
                    new OracleParameter("in_sStaffID",OracleDbType.Int32,staffEntity.StaffID,ParameterDirection.Input),
                    new OracleParameter("in_sstaffcode",OracleDbType.Varchar2,staffEntity.StaffCode,ParameterDirection.Input),
                    new OracleParameter("in_sstaffname",OracleDbType.Varchar2,staffEntity.StaffName,ParameterDirection.Input),
                    new OracleParameter("in_sidcardno",OracleDbType.Varchar2,staffEntity.IDCardNo,ParameterDirection.Input),
                    new OracleParameter("in_sstartbirthday",OracleDbType.Date,staffEntity.StartBirthday,ParameterDirection.Input),
                    new OracleParameter("in_sendbirthday",OracleDbType.Date,staffEntity.EndBirthday,ParameterDirection.Input),
                    new OracleParameter("in_sgender",OracleDbType.Varchar2,staffEntity.Gender,ParameterDirection.Input),
                    new OracleParameter("in_smaritalstatus",OracleDbType.Int32,staffEntity.MaritalStatus,ParameterDirection.Input),
                    new OracleParameter("in_shometown",OracleDbType.Varchar2,staffEntity.HomeTown,ParameterDirection.Input),
                    new OracleParameter("in_spolicitalstatus",OracleDbType.Varchar2,staffEntity.PolicitalStatus,ParameterDirection.Input),
                    new OracleParameter("in_snational",OracleDbType.Int32,staffEntity.National,ParameterDirection.Input),
                    new OracleParameter("in_seducational",OracleDbType.Int32,staffEntity.Educational,ParameterDirection.Input),
                    new OracleParameter("in_sgraduated",OracleDbType.Varchar2,staffEntity.Graduated,ParameterDirection.Input),
                    new OracleParameter("in_sspecialfield",OracleDbType.Varchar2,staffEntity.SpecialField,ParameterDirection.Input),
                    new OracleParameter("in_stelephone",OracleDbType.Varchar2,staffEntity.Telephone,ParameterDirection.Input),
                    new OracleParameter("in_rStaffRecordID",OracleDbType.Int32,staffEntity.RStaffRecordID,ParameterDirection.Input),
                    new OracleParameter("in_rrecordtype",OracleDbType.Int32,staffEntity.Recordtype,ParameterDirection.Input),
                    new OracleParameter("in_rvalueflag",OracleDbType.Int32,staffEntity.RValueflag,ParameterDirection.Input),
                    new OracleParameter("in_sIDList", OracleDbType.Varchar2, strIdList, ParameterDirection.Input),
                    new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output),
                };
                DataSet ds = con.ExecStoredProcedure("PRO_HR_SearchHrStaffApprove", paras);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 根据工号查询员工档案信息
        /// </summary>
        /// <param name="userId">员工ID</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet</returns>
        /// <remarks>
        /// 作者                  日期                  内容
        /// 冯雪                  2014-9-23       新建
        /// </remarks>
        public static DataSet SearchHrStaffInfo(int userId, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                string strSql = " SELECT TUser.userid,Staff.Staffid,Staff.Staffname,Staff.Organizationid,"
                                   + "  Staff.Jobs,Staff.Staffstatus,Staff.Staffcode,'' Remarks,"
                                   + "  (CASE Staff.staffStatus"
                                   + "  WHEN 0 THEN '未入职'"
                                   + "  WHEN 1 THEN '试用 '"
                                   + "  WHEN 2 THEN '转正'"
                                   + "  WHEN 3 THEN '离职'"
                                   + "  ELSE '' END) AS staffStatusName,"
                                   + "  Jobs.Jobsname,Org.Organizationname,TUser.UserCode,TUserJobs.Jobsname as UJobsName,TUserJobs.JobsId as UJobsId"
                                   + "  FROM TP_HR_Staff Staff "
                                   + "  LEFT JOIN TP_MST_Jobs Jobs ON Jobs.Jobsid = Staff.Jobs "
                                   + "  LEFT JOIN TP_MST_Organization Org ON Org.Organizationid = Staff.Organizationid "
                                   + "  LEFT JOIN TP_MST_UserStaff TUserStaff on TUserStaff.StaffID = Staff.StaffID "
                                   + "  LEFT JOIN TP_MST_User TUser on TUserStaff.Userid = TUser.Userid"
                                   + "  LEFT JOIN TP_MST_Jobs TUserJobs on TUserJobs.Jobsid = TUserStaff.Ujobsid"
                                   + "  WHERE Staff.Accountid = :accountID "
                                   + "  AND TUser.Userid  = :userId"
                                   + "  AND Staff.Staffstatus in(1,2)";
                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter(":accountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
                    new OracleParameter(":userId", OracleDbType.Int32, userId, ParameterDirection.Input),
                };
                DataSet ds = con.GetSqlResultToDs(strSql, paras);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 根据查询非工号下的员工档案信息
        /// </summary>
        /// <param name="searchStaffEntity">员工查询实体</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet</returns>
        public static DataSet SearchStaffInfo(SearchStaffEntity searchStaffEntity, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                string strSql = " SELECT Staff.Staffid,Staff.Staffname,Staff.Organizationid,"
                                   + "  Staff.Jobs,Staff.Staffstatus,Staff.Staffcode, Staff.Post,"
                                   + "  (CASE Staff.staffStatus"
                                   + "  WHEN 0 THEN '未入职'"
                                   + "  WHEN 1 THEN '试用 '"
                                   + "  WHEN 2 THEN '转正'"
                                   + "  WHEN 3 THEN '离职'"
                                   + "  ELSE '' END) AS staffStatusName,"
                                   + "  Jobs.Jobsname,Org.Organizationname,Org.OrganizationFullName,Post.PostName,0 Sel"
                                   + "  FROM TP_HR_Staff Staff"
                                   + "  LEFT JOIN TP_MST_Jobs Jobs ON  Jobs.Jobsid = Staff.Jobs "
                                   + "  LEFT JOIN TP_MST_Organization Org ON Org.Organizationid = Staff.Organizationid "
                                   + "  LEFT JOIN TP_MST_Post Post on Post.Postid = Staff.Post "
                                   + "  WHERE Staff.Accountid = :accountID "
                                   + "  AND Staff.Staffstatus in(1,2)"
                                   + "  AND (Staff.Jobs = :jobs OR :jobs is null)";
                if (!string.IsNullOrEmpty(searchStaffEntity.StaffCode))
                {
                    strSql += " AND Staff.Staffcode like '%" + searchStaffEntity.StaffCode + "%'";
                }
                if (!string.IsNullOrEmpty(searchStaffEntity.StaffName))
                {
                    strSql += " AND Staff.Staffname like '%" + searchStaffEntity.StaffName + "%'";
                }
                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter(":accountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
                    new OracleParameter(":jobs", OracleDbType.Int32, searchStaffEntity.Jobs, ParameterDirection.Input),
                    new OracleParameter(":jobs", OracleDbType.Int32, searchStaffEntity.Jobs, ParameterDirection.Input),
                };
                DataSet ds = con.GetSqlResultToDs(strSql, paras);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        #region 员工履历

        /// <summary>
        /// 获了员工履历行数据
        /// </summary>
        /// <param name="staffrecordid">员工履历ID</param>
        /// <returns>DataSet</returns>
        public static DataSet GetStaffRecorsRowData(int staffrecordid)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                string sqlString = "select TP_HR_STAFFRECORD.*,tp_mst_organization.organizationcode, tp_mst_organization.organizationname,TP_HR_STAFF.StaffName as ApplicantName,A.StaffName from TP_HR_STAFFRECORD left join tp_mst_organization  on TP_HR_STAFFRECORD.OriginalOrganizationID=tp_mst_organization.organizationid left join TP_HR_STAFF on TP_HR_STAFFRECORD.Applicant=TP_HR_STAFF.StaffID  left join TP_HR_STAFF A on TP_HR_STAFFRECORD.StaffID=A.StaffID where TP_HR_STAFFRECORD.StaffRecordID=" + staffrecordid;
                DataSet ds = con.GetSqlResultToDs(sqlString);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        #endregion

        #region 员工考勤

        /// <summary>
        /// 根据传入的查询实体获取员工考勤表中的数据
        /// </summary>
        /// <param name="searchAttendanceEntity">查询实体</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet员工考勤数据集集合</returns>
        public static DataSet SearcStaffAttendance(SearchAttendanceEntity searchAttendanceEntity, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter("in_accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
                    new OracleParameter("in_StaffID",OracleDbType.Int32,searchAttendanceEntity.StaffID,ParameterDirection.Input),
                    new OracleParameter("in_sIDCardNo",OracleDbType.Varchar2,searchAttendanceEntity.IDCardNo,ParameterDirection.Input),
                    new OracleParameter("in_sStartBirthday",OracleDbType.Date,searchAttendanceEntity.StartBirthday,ParameterDirection.Input),
                    new OracleParameter("in_sEndBirthday",OracleDbType.Date,searchAttendanceEntity.EndBirthday,ParameterDirection.Input),
                    new OracleParameter("in_sGender",OracleDbType.Varchar2,searchAttendanceEntity.Gender,ParameterDirection.Input),
                    new OracleParameter("in_sMaritalStatus",OracleDbType.Int32,searchAttendanceEntity.MaritalStatus,ParameterDirection.Input),
                    new OracleParameter("in_sHomeTown",OracleDbType.Varchar2,searchAttendanceEntity.HomeTown,ParameterDirection.Input),
                    new OracleParameter("in_sPolicitalStatus",OracleDbType.Varchar2,searchAttendanceEntity.PolicitalStatus,ParameterDirection.Input),
                    new OracleParameter("in_sNational",OracleDbType.Int32,searchAttendanceEntity.National,ParameterDirection.Input),
                    new OracleParameter("in_sEducational",OracleDbType.Int32,searchAttendanceEntity.Educational,ParameterDirection.Input),
                    new OracleParameter("in_sGraduated",OracleDbType.Varchar2,searchAttendanceEntity.Graduated,ParameterDirection.Input),
                    new OracleParameter("in_sSpecialField",OracleDbType.Varchar2,searchAttendanceEntity.SpecialField,ParameterDirection.Input),
                    new OracleParameter("in_sTelephone",OracleDbType.Varchar2,searchAttendanceEntity.Telephone,ParameterDirection.Input),
                    new OracleParameter("in_sStartAttendanceDate",OracleDbType.Date,searchAttendanceEntity.StartAttendanceDate,ParameterDirection.Input),
                    new OracleParameter("in_sEndAttendanceDate",OracleDbType.Date,searchAttendanceEntity.EndAttendanceDate,ParameterDirection.Input),
                    new OracleParameter("in_sCardNumber",OracleDbType.Varchar2,searchAttendanceEntity.CardNumber,ParameterDirection.Input),
                    new OracleParameter("in_sAttendanceStatus",OracleDbType.Char,searchAttendanceEntity.AttendanceStatus,ParameterDirection.Input),
                    new OracleParameter("in_sAbsenceReason",OracleDbType.Char,searchAttendanceEntity.AbsenceReason,ParameterDirection.Input),
                    new OracleParameter("in_sSettlementFlag",OracleDbType.Char,searchAttendanceEntity.SettlementFlag,ParameterDirection.Input),
                    new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output),
                };
                return con.ExecStoredProcedure("PRO_HR_StaffAttendance", paras);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 获取员工员工考勤编辑信息
        /// </summary>
        /// <param name="searchAttendanceEntity">查询实体</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet员工考勤数据集集合</returns>
        public static DataSet SearcStaffAttendanceForEdit(SearchAttendanceEntity searchAttendanceEntity, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                #region 获取员工的已有考勤信息 到dsAttenance中
                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter("in_accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
                    new OracleParameter("in_StaffID",OracleDbType.Int32,searchAttendanceEntity.StaffID,ParameterDirection.Input),
                    new OracleParameter("in_sIDCardNo",OracleDbType.Varchar2,searchAttendanceEntity.IDCardNo,ParameterDirection.Input),
                    new OracleParameter("in_sStartBirthday",OracleDbType.Date,searchAttendanceEntity.StartBirthday,ParameterDirection.Input),
                    new OracleParameter("in_sEndBirthday",OracleDbType.Date,searchAttendanceEntity.EndBirthday,ParameterDirection.Input),
                    new OracleParameter("in_sGender",OracleDbType.Varchar2,searchAttendanceEntity.Gender,ParameterDirection.Input),
                    new OracleParameter("in_sMaritalStatus",OracleDbType.Int32,searchAttendanceEntity.MaritalStatus,ParameterDirection.Input),
                    new OracleParameter("in_sHomeTown",OracleDbType.Varchar2,searchAttendanceEntity.HomeTown,ParameterDirection.Input),
                    new OracleParameter("in_sPolicitalStatus",OracleDbType.Varchar2,searchAttendanceEntity.PolicitalStatus,ParameterDirection.Input),
                    new OracleParameter("in_sNational",OracleDbType.Int32,searchAttendanceEntity.National,ParameterDirection.Input),
                    new OracleParameter("in_sEducational",OracleDbType.Int32,searchAttendanceEntity.Educational,ParameterDirection.Input),
                    new OracleParameter("in_sGraduated",OracleDbType.Varchar2,searchAttendanceEntity.Graduated,ParameterDirection.Input),
                    new OracleParameter("in_sSpecialField",OracleDbType.Varchar2,searchAttendanceEntity.SpecialField,ParameterDirection.Input),
                    new OracleParameter("in_sTelephone",OracleDbType.Varchar2,searchAttendanceEntity.Telephone,ParameterDirection.Input),
                    new OracleParameter("in_sStartAttendanceDate",OracleDbType.Date,searchAttendanceEntity.StartAttendanceDate,ParameterDirection.Input),
                    new OracleParameter("in_sEndAttendanceDate",OracleDbType.Date,searchAttendanceEntity.EndAttendanceDate,ParameterDirection.Input),
                    new OracleParameter("in_sCardNumber",OracleDbType.Varchar2,searchAttendanceEntity.CardNumber,ParameterDirection.Input),
                    new OracleParameter("in_sAttendanceStatus",OracleDbType.Char,searchAttendanceEntity.AttendanceStatus,ParameterDirection.Input),
                    new OracleParameter("in_sAbsenceReason",OracleDbType.Char,searchAttendanceEntity.AbsenceReason,ParameterDirection.Input),
                    new OracleParameter("in_sSettlementFlag",OracleDbType.Char,searchAttendanceEntity.SettlementFlag,ParameterDirection.Input),
                    new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output),
                };
                DataSet dsAttenance = con.ExecStoredProcedure("PRO_HR_StaffAttendance", paras);
                #endregion
                DataTable dtStaffAttend = dsAttenance.Tables[0].Clone();
                DataSet dsStaffAttendance = new DataSet();
                dsStaffAttendance.Tables.Add(dtStaffAttend);
                if (searchAttendanceEntity.StaffID == null || searchAttendanceEntity.StartAttendanceDate == null
                    || searchAttendanceEntity.EndAttendanceDate == null)
                {
                    return dsStaffAttendance;
                }
                #region 获取员工信息 到dtStaff中
                string strSql1 = "Select TP_HR_Staff.STAFFID,"
                            + " TP_HR_Staff.StaffCode,"
                            + " TP_HR_Staff.StaffName,"
                            + " TP_HR_Staff.IDCardNo"
                            + " From TP_HR_Staff"
                            + " Where TP_HR_Staff.StaffID = :pStaffID AND TP_HR_Staff.Accountid = :pAccountid";
                OracleParameter[] paras1 = new OracleParameter[]{
                    new OracleParameter(":pStaffID",OracleDbType.Int32,searchAttendanceEntity.StaffID,ParameterDirection.Input),
                    new OracleParameter(":pAccountid",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
                };
                DataTable dtStaff = con.GetSqlResultToDt(strSql1, paras1);
                #endregion
                #region 对相关属性进行赋值
                foreach (DataRow newRowStaff in dtStaff.Rows)
                {
                    DateTime dtBegin = searchAttendanceEntity.StartAttendanceDate.Value;
                    DateTime dtEnd = searchAttendanceEntity.EndAttendanceDate.Value;
                    if (dtEnd >= DateTime.Now)
                    {
                        dtEnd = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
                    }
                    for (DateTime dtItem = dtBegin; dtItem <= dtEnd; dtItem = dtItem.AddDays(1))
                    {
                        DataRow newRowAtt = dtStaffAttend.NewRow();
                        newRowAtt["StaffID"] = newRowStaff["StaffID"];
                        newRowAtt["StaffCode"] = newRowStaff["StaffCode"];
                        newRowAtt["StaffName"] = newRowStaff["StaffName"];
                        newRowAtt["IDCardNo"] = newRowStaff["IDCardNo"];
                        newRowAtt["AttendanceDate"] = dtItem;
                        newRowAtt["CardNumber"] = string.Empty;
                        newRowAtt["AttendanceStatus"] = 1;
                        newRowAtt["AttendanceStatusName"] = "全天";
                        newRowAtt["AbsenceReason"] = 4;
                        newRowAtt["AbsenceReasonName"] = "其它";
                        newRowAtt["TardinessTimes"] = 0;
                        newRowAtt["SettlementFlag"] = 0;
                        newRowAtt["SettlementFlagName"] = "未结算";
                        newRowAtt["REMARKS"] = string.Empty;
                        if (dsAttenance != null && dsAttenance.Tables.Count > 0)
                        {
                            if (dsAttenance.Tables[0].Rows.Count > 0)
                            {
                                string strWhere = "StaffID = " + dsAttenance.Tables[0].Rows[0]["StaffID"].ToString()
                                        + " And AttendanceDate = '" + dtItem + "'";
                                DataRow[] rowAttendances = dsAttenance.Tables[0].Select(strWhere);
                                if (rowAttendances != null & rowAttendances.Length > 0)
                                {
                                    newRowAtt["CardNumber"] = rowAttendances[0]["CardNumber"];
                                    newRowAtt["AttendanceStatus"] = rowAttendances[0]["AttendanceStatus"];
                                    newRowAtt["AttendanceStatusName"] = rowAttendances[0]["AttendanceStatusName"];
                                    newRowAtt["AbsenceReason"] = rowAttendances[0]["AbsenceReason"];
                                    newRowAtt["AbsenceReasonName"] = rowAttendances[0]["AbsenceReasonName"];
                                    newRowAtt["TardinessTimes"] = rowAttendances[0]["TardinessTimes"];
                                    newRowAtt["SettlementFlag"] = rowAttendances[0]["SettlementFlag"];
                                    newRowAtt["SettlementFlagName"] = rowAttendances[0]["SettlementFlagName"];
                                    newRowAtt["REMARKS"] = rowAttendances[0]["REMARKS"];
                                    newRowAtt["CREATETIME"] = rowAttendances[0]["CREATETIME"];
                                    newRowAtt["CREATEUSERID"] = rowAttendances[0]["CREATEUSERID"];
                                    newRowAtt["UPDATETIME"] = rowAttendances[0]["UPDATETIME"];
                                    newRowAtt["UPDATEUSERID"] = rowAttendances[0]["UPDATEUSERID"];
                                    newRowAtt["OPTIMESTAMP"] = rowAttendances[0]["OPTIMESTAMP"];
                                    newRowAtt["CreateUserCode"] = rowAttendances[0]["CreateUserCode"];
                                    newRowAtt["CreateUserName"] = rowAttendances[0]["CreateUserName"];
                                    newRowAtt["UpdateUserCode"] = rowAttendances[0]["UpdateUserCode"];
                                    newRowAtt["UpdateUserName"] = rowAttendances[0]["UpdateUserName"];
                                }
                            }
                        }
                        int? intStaffID = null;
                        if (!string.IsNullOrEmpty(newRowAtt["StaffID"].ToString()))
                        {
                            intStaffID = Convert.ToInt32(newRowAtt["StaffID"].ToString());
                        }
                        bool bolSettlementFlag = GetStaffAttendanceIsSettlementFlag(con, intStaffID, dtItem, sUserInfo);
                        if (!bolSettlementFlag)
                        {
                            dtStaffAttend.Rows.Add(newRowAtt);
                        }
                    }
                }
                #endregion
                return dsStaffAttendance;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 将导入Excel中的数据转换为GridView显示的数据集
        /// </summary>
        /// <param name="pStaff">Excel中的数据员工集合</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet员工考勤信息表</returns>
        public static DataSet GetStaffAttendanceInfoForExcel(DataTable pStaff, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                #region 获取员工的已有考勤信息 到dsAttenance中
                SearchAttendanceEntity searchAttendanceEntity = new SearchAttendanceEntity();
                searchAttendanceEntity.StaffID = -999;
                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter("in_accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
                    new OracleParameter("in_StaffID",OracleDbType.Int32,searchAttendanceEntity.StaffID,ParameterDirection.Input),
                    new OracleParameter("in_sIDCardNo",OracleDbType.Varchar2,searchAttendanceEntity.IDCardNo,ParameterDirection.Input),
                    new OracleParameter("in_sStartBirthday",OracleDbType.Date,searchAttendanceEntity.StartBirthday,ParameterDirection.Input),
                    new OracleParameter("in_sEndBirthday",OracleDbType.Date,searchAttendanceEntity.EndBirthday,ParameterDirection.Input),
                    new OracleParameter("in_sGender",OracleDbType.Varchar2,searchAttendanceEntity.Gender,ParameterDirection.Input),
                    new OracleParameter("in_sMaritalStatus",OracleDbType.Int32,searchAttendanceEntity.MaritalStatus,ParameterDirection.Input),
                    new OracleParameter("in_sHomeTown",OracleDbType.Varchar2,searchAttendanceEntity.HomeTown,ParameterDirection.Input),
                    new OracleParameter("in_sPolicitalStatus",OracleDbType.Varchar2,searchAttendanceEntity.PolicitalStatus,ParameterDirection.Input),
                    new OracleParameter("in_sNational",OracleDbType.Int32,searchAttendanceEntity.National,ParameterDirection.Input),
                    new OracleParameter("in_sEducational",OracleDbType.Int32,searchAttendanceEntity.Educational,ParameterDirection.Input),
                    new OracleParameter("in_sGraduated",OracleDbType.Varchar2,searchAttendanceEntity.Graduated,ParameterDirection.Input),
                    new OracleParameter("in_sSpecialField",OracleDbType.Varchar2,searchAttendanceEntity.SpecialField,ParameterDirection.Input),
                    new OracleParameter("in_sTelephone",OracleDbType.Varchar2,searchAttendanceEntity.Telephone,ParameterDirection.Input),
                    new OracleParameter("in_sStartAttendanceDate",OracleDbType.Date,searchAttendanceEntity.StartAttendanceDate,ParameterDirection.Input),
                    new OracleParameter("in_sEndAttendanceDate",OracleDbType.Date,searchAttendanceEntity.EndAttendanceDate,ParameterDirection.Input),
                    new OracleParameter("in_sCardNumber",OracleDbType.Varchar2,searchAttendanceEntity.CardNumber,ParameterDirection.Input),
                    new OracleParameter("in_sAttendanceStatus",OracleDbType.Char,searchAttendanceEntity.AttendanceStatus,ParameterDirection.Input),
                    new OracleParameter("in_sAbsenceReason",OracleDbType.Char,searchAttendanceEntity.AbsenceReason,ParameterDirection.Input),
                    new OracleParameter("in_sSettlementFlag",OracleDbType.Char,searchAttendanceEntity.SettlementFlag,ParameterDirection.Input),
                    new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output),
                };
                DataSet dsAttenance = con.ExecStoredProcedure("PRO_HR_StaffAttendance", paras);
                #endregion
                foreach (DataRow newRowStaff in pStaff.Rows)
                {
                    DataRow newStaffAnce = dsAttenance.Tables[0].NewRow();
                    foreach (DataColumn newColStaff in pStaff.Columns)
                    {
                        #region 设置对应的值到表格中
                        string strColValue = newRowStaff[newColStaff.ColumnName].ToString();
                        if ("员工编号".Equals(newColStaff.ColumnName))
                        {
                            #region 根据员工编号获取员工相关信息保存到dtStaffInfo中
                            string strSql1 = "Select TP_HR_Staff.StaffID,"
                                    + " TP_HR_Staff.Staffcode,"
                                    + " TP_HR_Staff.StaffName,"
                                    + " TP_HR_Staff.IDCardNo"
                                    + " From TP_HR_Staff"
                                    + " Where TP_HR_Staff.Accountid = :pAccountid  And TP_HR_Staff.Staffcode = :pStaffcode";
                            OracleParameter[] paras1 = new OracleParameter[]{
                                    new OracleParameter("pAccountid",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
                                    new OracleParameter("pStaffcode",OracleDbType.Varchar2,strColValue,ParameterDirection.Input),
                                };
                            DataTable dtStaffInfo = con.GetSqlResultToDt(strSql1, paras1);
                            #endregion
                            newStaffAnce["StaffCode"] = strColValue;
                            if (dtStaffInfo != null && dtStaffInfo.Rows.Count > 0)
                            {
                                newStaffAnce["StaffID"] = dtStaffInfo.Rows[0]["StaffID"];
                                newStaffAnce["StaffName"] = dtStaffInfo.Rows[0]["StaffName"];
                                newStaffAnce["IDCardNo"] = dtStaffInfo.Rows[0]["IDCardNo"];
                            }
                        }
                        else if ("考勤日期".Equals(newColStaff.ColumnName))
                        {
                            DateTime dtMeal = Convert.ToDateTime(strColValue);
                            newStaffAnce["AttendanceDate"] = new DateTime(dtMeal.Year, dtMeal.Month, dtMeal.Day);
                        }
                        else if ("考勤卡号".Equals(newColStaff.ColumnName))
                        {
                            newStaffAnce["CardNumber"] = strColValue;
                        }
                        else if ("出勤状态".Equals(newColStaff.ColumnName))
                        {
                            //1：全天 2：半天 3：缺勤
                            if ("全天".Equals(strColValue))
                            {
                                newStaffAnce["AttendanceStatus"] = 1;
                                newStaffAnce["AttendanceStatusName"] = "全天";
                            }
                            else if ("半天".Equals(strColValue))
                            {
                                newStaffAnce["AttendanceStatus"] = 2;
                                newStaffAnce["AttendanceStatusName"] = "半天";
                            }
                            else if ("缺勤".Equals(strColValue))
                            {
                                newStaffAnce["AttendanceStatus"] = 3;
                                newStaffAnce["AttendanceStatusName"] = "缺勤";
                            }
                            else
                            {
                                newStaffAnce["AttendanceStatus"] = string.Empty;
                                newStaffAnce["AttendanceStatusName"] = string.Empty;
                            }
                        }
                        else if ("缺勤原因".Equals(newColStaff.ColumnName))
                        {
                            //0：休息  1：病假  2：事假  3：旷工
                            if ("休息".Equals(strColValue))
                            {
                                newStaffAnce["AbsenceReason"] = 0;
                                newStaffAnce["AbsenceReasonName"] = "休息";
                            }
                            else if ("病假".Equals(strColValue))
                            {
                                newStaffAnce["AbsenceReason"] = 1;
                                newStaffAnce["AbsenceReasonName"] = "病假";
                            }
                            else if ("事假".Equals(strColValue))
                            {
                                newStaffAnce["AbsenceReason"] = 2;
                                newStaffAnce["AbsenceReasonName"] = "事假";
                            }
                            else if ("旷工".Equals(strColValue))
                            {
                                newStaffAnce["AbsenceReason"] = 3;
                                newStaffAnce["AbsenceReasonName"] = "旷工";
                            }
                            else
                            {
                                newStaffAnce["AbsenceReason"] = string.Empty;
                                newStaffAnce["AbsenceReasonName"] = "其它";
                            }
                        }
                        else if ("迟到早退次数".Equals(newColStaff.ColumnName))
                        {
                            Int32 intTardinessTimes = Convert.ToInt32(strColValue);
                            newStaffAnce["TardinessTimes"] = intTardinessTimes;
                        }
                        else if ("备注".Equals(newColStaff.ColumnName))
                        {
                            newStaffAnce["REMARKS"] = strColValue;
                        }
                        #endregion
                    }
                    newStaffAnce["SettlementFlag"] = 0;
                    newStaffAnce["SettlementFlagName"] = "未结算";
                    int? intStaffID = null;
                    if (!string.IsNullOrEmpty(newStaffAnce["StaffID"].ToString()))
                    {
                        intStaffID = Convert.ToInt32(newStaffAnce["StaffID"].ToString());
                    }
                    DateTime? dtAttendanceDate = null;
                    if (!string.IsNullOrEmpty(newStaffAnce["AttendanceDate"].ToString()))
                    {
                        dtAttendanceDate = Convert.ToDateTime(newStaffAnce["AttendanceDate"].ToString());
                    }
                    bool bolSettlementFlag = GetStaffAttendanceIsSettlementFlag(con, intStaffID, dtAttendanceDate, sUserInfo);
                    if (!bolSettlementFlag)
                    {
                        dsAttenance.Tables[0].Rows.Add(newStaffAnce);
                    }
                }
                return dsAttenance;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 获取考勤是否结算
        /// </summary>
        /// <param name="pConn">数据库连接</param>
        /// <param name="pStaffID">员工ID</param>
        /// <param name="pAttendanceDate">考勤日期</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>bool已结算返回为True 没有结算返回为False</returns>
        private static bool GetStaffAttendanceIsSettlementFlag(IDBConnection pConn, int? pStaffID, DateTime? pAttendanceDate, SUserInfo sUserInfo)
        {
            string strSql = "Select max(SettlementFlag) From TP_HR_StaffAttendance "
                    + "Where StaffID = :pStaffID And AttendanceDate = :pAttendanceDate And AccountID = :pAccountID";
            OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter("pStaffID",OracleDbType.Int32,pStaffID,ParameterDirection.Input),
                    new OracleParameter("pAttendanceDate",OracleDbType.Date,pAttendanceDate,ParameterDirection.Input),
                    new OracleParameter("pAccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
                };
            string strSettlementFlag = pConn.GetSqlResultToStr(strSql, paras);
            if (!string.IsNullOrEmpty(strSettlementFlag))
            {
                if (Constant.SettlementFlag.Settled.GetHashCode().ToString().Equals(strSettlementFlag))
                {
                    return true;
                }
            }
            return false;
        }

        #endregion

        /// <summary>
        /// 根据传入的实体获取行政奖惩信息
        /// </summary>
        /// <param name="searchAdminRAPEntity">查询实体</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet行政奖惩信息表</returns>
        public static DataSet SearcStaffAdminRAPInfo(SearchAdminRAPEntity searchAdminRAPEntity, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                string strIdList = string.Empty;
                if (searchAdminRAPEntity.IDList != null && searchAdminRAPEntity.IDList.Length > 0)
                {
                    strIdList = DataConvert.ConvertListToSqlInWhere(searchAdminRAPEntity.IDList);
                }
                con.Open();
                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter("in_accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
                    new OracleParameter("in_staffID",OracleDbType.Int32,searchAdminRAPEntity.StaffID,ParameterDirection.Input),
                    new OracleParameter("in_rapType",OracleDbType.Double,searchAdminRAPEntity.RAPType,ParameterDirection.Input),
                    new OracleParameter("in_startRAPDate",OracleDbType.Date,searchAdminRAPEntity.StartRAPDate,ParameterDirection.Input),
                    new OracleParameter("in_endRAPDate",OracleDbType.Date,searchAdminRAPEntity.EndRAPDate,ParameterDirection.Input),
                    new OracleParameter("in_startRAPAmount",OracleDbType.Double,searchAdminRAPEntity.StartRAPAmount,ParameterDirection.Input),
                    new OracleParameter("in_endRAPAmount",OracleDbType.Double,searchAdminRAPEntity.EndRAPAmount,ParameterDirection.Input),
                    new OracleParameter("in_administrationType",OracleDbType.Int32,searchAdminRAPEntity.AdministrationType,ParameterDirection.Input),
                    new OracleParameter("in_auditStatus",OracleDbType.Int32,searchAdminRAPEntity.AuditStatus,ParameterDirection.Input),
                    new OracleParameter("in_settlementFlag",OracleDbType.Char,searchAdminRAPEntity.SettlementFlag,ParameterDirection.Input),
                    new OracleParameter("in_valueFlag",OracleDbType.Char,searchAdminRAPEntity.ValueFlag,ParameterDirection.Input),
                    new OracleParameter("in_sIDList",OracleDbType.Varchar2,strIdList,ParameterDirection.Input),
                    new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output),
                };
                return con.ExecStoredProcedure("PRO_HR_StaffAdminRAP", paras);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 获取员工报餐信息
        /// </summary>
        /// <param name="searchDailyMealEntity">员工报餐查询实体</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet员工报餐信息表</returns>
        public static DataSet SearcStaffDailyMealInfo(SearchDailyMealEntity searchDailyMealEntity, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter("in_accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
                    new OracleParameter("in_sStaffID",OracleDbType.Int32,searchDailyMealEntity.StaffID,ParameterDirection.Input),
                    new OracleParameter("in_sIDCardNo",OracleDbType.Varchar2,searchDailyMealEntity.IDCardNo,ParameterDirection.Input),
                    new OracleParameter("in_sStartBirthday",OracleDbType.Date,searchDailyMealEntity.StartBirthday,ParameterDirection.Input),
                    new OracleParameter("in_sEndBirthday",OracleDbType.Date,searchDailyMealEntity.EndBirthday,ParameterDirection.Input),
                    new OracleParameter("in_sGender",OracleDbType.Varchar2,searchDailyMealEntity.Gender,ParameterDirection.Input),
                    new OracleParameter("in_sMaritalStatus",OracleDbType.Int32,searchDailyMealEntity.MaritalStatus,ParameterDirection.Input),
                    new OracleParameter("in_sHomeTown",OracleDbType.Varchar2,searchDailyMealEntity.HomeTown,ParameterDirection.Input),
                    new OracleParameter("in_sPolicitalStatus",OracleDbType.Varchar2,searchDailyMealEntity.PolicitalStatus,ParameterDirection.Input),
                    new OracleParameter("in_sNational",OracleDbType.Int32,searchDailyMealEntity.National,ParameterDirection.Input),
                    new OracleParameter("in_sEducational",OracleDbType.Int32,searchDailyMealEntity.Educational,ParameterDirection.Input),
                    new OracleParameter("in_sGraduated",OracleDbType.Varchar2,searchDailyMealEntity.Graduated,ParameterDirection.Input),
                    new OracleParameter("in_sSpecialField",OracleDbType.Varchar2,searchDailyMealEntity.SpecialField,ParameterDirection.Input),
                    new OracleParameter("in_sTelephone",OracleDbType.Varchar2,searchDailyMealEntity.Telephone,ParameterDirection.Input),
                    new OracleParameter("in_sStartMealDate",OracleDbType.Date,searchDailyMealEntity.StartMealDate,ParameterDirection.Input),
                    new OracleParameter("in_sEndMealDate",OracleDbType.Date,searchDailyMealEntity.EndMealDate,ParameterDirection.Input),
                    new OracleParameter("in_sOrderBreakfast",OracleDbType.Varchar2,searchDailyMealEntity.OrderBreakfast,ParameterDirection.Input),
                    new OracleParameter("in_sOrderLunch", OracleDbType.Varchar2, searchDailyMealEntity.OrderLunch, ParameterDirection.Input),
                    new OracleParameter("in_sOrderDinner", OracleDbType.Varchar2, searchDailyMealEntity.OrderDinner, ParameterDirection.Input),
                    new OracleParameter("in_sMealBreakfast",OracleDbType.Varchar2,searchDailyMealEntity.MealBreakfast,ParameterDirection.Input),
                    new OracleParameter("in_sMealLunch", OracleDbType.Varchar2, searchDailyMealEntity.MealLunch, ParameterDirection.Input),
                    new OracleParameter("in_sMealDinner", OracleDbType.Varchar2, searchDailyMealEntity.MealDinner, ParameterDirection.Input),
                    new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output),
                };
                return con.ExecStoredProcedure("PRO_HR_StaffDailyMeal", paras);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 获取员工报餐编辑信息
        /// </summary>
        /// <param name="searchDailyMealEntity">员工报餐查询实体</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet员工报餐信息表</returns>
        public static DataSet SearcStaffDailyMealInfoForEdit(SearchDailyMealEntity searchDailyMealEntity, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter("in_accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
                    new OracleParameter("in_sStaffID",OracleDbType.Int32,searchDailyMealEntity.StaffID,ParameterDirection.Input),
                    new OracleParameter("in_OrganizationID",OracleDbType.Int32,searchDailyMealEntity.OrganizationID,ParameterDirection.Input),
                    new OracleParameter("in_Jobs",OracleDbType.Int32,searchDailyMealEntity.Jobs,ParameterDirection.Input),
                    new OracleParameter("in_sMealDate",OracleDbType.Date,searchDailyMealEntity.MealDate,ParameterDirection.Input),
                    new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output),
                };
                DataSet dsStaff = con.ExecStoredProcedure("PRO_HR_StaffDailyMealEdit", paras);
                if (dsStaff != null && dsStaff.Tables.Count > 0 && searchDailyMealEntity.MealDate != null)
                {
                    foreach (DataRow newRow in dsStaff.Tables[0].Rows)
                    {
                        newRow["MealDate"] = searchDailyMealEntity.MealDate.Value;
                    }
                }
                return dsStaff;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 将导入Excel中的数据转换为GridView显示的数据集
        /// </summary>
        /// <param name="pStaff">Excel中的数据员工集合</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet员工报餐信息表</returns>
        public static DataSet GetStaffDailyMealInfoForExcel(DataTable pStaff, WCFConstant.FormMode pStatus, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                #region 获取员工用餐编辑结构保存到dsStaff中
                SearchDailyMealEntity searchDailyMealEntity = new SearchDailyMealEntity();
                searchDailyMealEntity.StaffID = -999;
                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter("in_accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
                    new OracleParameter("in_sStaffID",OracleDbType.Int32,searchDailyMealEntity.StaffID,ParameterDirection.Input),
                    new OracleParameter("in_OrganizationID",OracleDbType.Int32,searchDailyMealEntity.OrganizationID,ParameterDirection.Input),
                    new OracleParameter("in_Jobs",OracleDbType.Int32,searchDailyMealEntity.Jobs,ParameterDirection.Input),
                    new OracleParameter("in_sMealDate",OracleDbType.Date,searchDailyMealEntity.MealDate,ParameterDirection.Input),
                    new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output),
                };
                DataSet dsStaff = con.ExecStoredProcedure("PRO_HR_StaffDailyMealEdit", paras);
                #endregion
                foreach (DataRow newRowStaff in pStaff.Rows)
                {
                    DataRow newStaffMeal = dsStaff.Tables[0].NewRow();
                    foreach (DataColumn newColStaff in pStaff.Columns)
                    {
                        #region 设置对应的值到表格中
                        string strColValue = newRowStaff[newColStaff.ColumnName].ToString();
                        if ("员工编号".Equals(newColStaff.ColumnName))
                        {
                            #region 根据员工编号获取员工相关信息保存到dtStaffInfo中
                            string strSql1 = "Select TP_HR_Staff.StaffID,"
                                    + " TP_HR_Staff.Staffcode,"
                                    + " TP_HR_Staff.StaffName,"
                                    + " TP_HR_Staff.Jobs,"
                                    + " TP_HR_Staff.OrganizationID,"
                                    + " TP_MST_Jobs.Jobsname Jobsname,"
                                    + " TP_MST_Organization.Organizationname Organizationname"
                                + " From TP_HR_Staff  left join"
                                    + " TP_MST_Jobs on TP_MST_Jobs.Jobsid = TP_HR_Staff.Jobs left join"
                                    + " TP_MST_Organization on TP_MST_Organization.Organizationid = TP_HR_Staff.Organizationid"
                                + " Where TP_HR_Staff.Accountid = :pAccountid  And TP_HR_Staff.Staffcode = :pStaffcode";
                            OracleParameter[] paras1 = new OracleParameter[]{
                                    new OracleParameter("pAccountid",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
                                    new OracleParameter("pStaffcode",OracleDbType.Varchar2,strColValue,ParameterDirection.Input),
                                };
                            DataTable dtStaffInfo = con.GetSqlResultToDt(strSql1, paras1);
                            #endregion
                            newStaffMeal["StaffCode"] = strColValue;
                            if (dtStaffInfo != null && dtStaffInfo.Rows.Count > 0)
                            {
                                newStaffMeal["StaffID"] = dtStaffInfo.Rows[0]["StaffID"];
                                newStaffMeal["StaffName"] = dtStaffInfo.Rows[0]["StaffName"];
                                newStaffMeal["Jobs"] = dtStaffInfo.Rows[0]["Jobs"];
                                newStaffMeal["OrganizationID"] = dtStaffInfo.Rows[0]["OrganizationID"];
                                newStaffMeal["Jobsname"] = dtStaffInfo.Rows[0]["Jobsname"];
                                newStaffMeal["Organizationname"] = dtStaffInfo.Rows[0]["Organizationname"];
                            }
                        }
                        else if ("报餐日期".Equals(newColStaff.ColumnName))
                        {
                            DateTime dtMeal = Convert.ToDateTime(strColValue);
                            newStaffMeal["MealDate"] = new DateTime(dtMeal.Year, dtMeal.Month, dtMeal.Day);
                        }
                        else if ("预定早餐".Equals(newColStaff.ColumnName) && pStatus == WCFConstant.FormMode.Edit)
                        {
                            if ("是".Equals(strColValue))
                            {
                                newStaffMeal["OrderBreakfast"] = "1";
                                newStaffMeal["OrderBreakfastName"] = "报餐";
                            }
                            else
                            {
                                newStaffMeal["OrderBreakfast"] = "0";
                                newStaffMeal["OrderBreakfastName"] = "未报餐";
                            }
                        }
                        else if ("预定午餐".Equals(newColStaff.ColumnName) && pStatus == WCFConstant.FormMode.Edit)
                        {
                            if ("是".Equals(strColValue))
                            {
                                newStaffMeal["OrderLunch"] = "1";
                                newStaffMeal["OrderLunchName"] = "报餐";
                            }
                            else
                            {
                                newStaffMeal["OrderLunch"] = "0";
                                newStaffMeal["OrderLunchName"] = "未报餐";
                            }
                        }
                        else if ("预定晚餐".Equals(newColStaff.ColumnName) && pStatus == WCFConstant.FormMode.Edit)
                        {
                            if ("是".Equals(strColValue))
                            {
                                newStaffMeal["OrderDinner"] = "1";
                                newStaffMeal["OrderDinnerName"] = "报餐";
                            }
                            else
                            {
                                newStaffMeal["OrderDinner"] = "0";
                                newStaffMeal["OrderDinnerName"] = "未报餐";
                            }
                        }
                        else if ("备注".Equals(newColStaff.ColumnName) && pStatus == WCFConstant.FormMode.Edit)
                        {
                            newStaffMeal["Remarks"] = strColValue;
                        }
                        else if ("吃早餐".Equals(newColStaff.ColumnName) && pStatus == WCFConstant.FormMode.MealEdit)
                        {
                            if ("是".Equals(strColValue))
                            {
                                newStaffMeal["MealBreakfast"] = "1";
                                newStaffMeal["MealBreakfastName"] = "用餐";
                            }
                            else
                            {
                                newStaffMeal["MealBreakfast"] = "0";
                                newStaffMeal["MealBreakfastName"] = "未用餐";
                            }
                        }
                        else if ("吃午餐".Equals(newColStaff.ColumnName) && pStatus == WCFConstant.FormMode.MealEdit)
                        {
                            if ("是".Equals(strColValue))
                            {
                                newStaffMeal["MealLunch"] = "1";
                                newStaffMeal["MealLunchName"] = "用餐";
                            }
                            else
                            {
                                newStaffMeal["MealLunch"] = "0";
                                newStaffMeal["MealLunchName"] = "未用餐";
                            }
                        }
                        else if ("吃晚餐".Equals(newColStaff.ColumnName) && pStatus == WCFConstant.FormMode.MealEdit)
                        {
                            if ("是".Equals(strColValue))
                            {
                                newStaffMeal["MealDinner"] = "1";
                                newStaffMeal["MealDinnerName"] = "用餐";
                            }
                            else
                            {
                                newStaffMeal["MealDinner"] = "0";
                                newStaffMeal["MealDinnerName"] = "未用餐";
                            }
                        }
                        #endregion
                    }
                    if (pStatus == WCFConstant.FormMode.MealEdit)
                    {
                        #region 根据用户编号和报餐日期获取对应的报餐信息保存到dtMeal中
                        if (!string.IsNullOrEmpty(newStaffMeal["StaffID"].ToString()))
                        {
                            int intStaffID = Convert.ToInt32(newStaffMeal["StaffID"]);
                            DateTime dtMealDate = Convert.ToDateTime(newStaffMeal["MealDate"]);
                            string strSql2 = "Select TP_HR_StaffDailyMeal.OrderBreakfast,"
                                                + " TP_HR_StaffDailyMeal.OrderLunch,"
                                                + " TP_HR_StaffDailyMeal.OrderDinner"
                                                + " From TP_HR_StaffDailyMeal"
                                                + " Where TP_HR_StaffDailyMeal.AccountID = :pAccountID"
                                                + " And TP_HR_StaffDailyMeal.Staffid = :pStaffid"
                                                + " And TP_HR_StaffDailyMeal.MealDate = :pMealDate";
                            OracleParameter[] paras2 = new OracleParameter[]{
                                    new OracleParameter("pAccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input),
                                    new OracleParameter("pStaffid",OracleDbType.Int32,intStaffID,ParameterDirection.Input),
                                    new OracleParameter("pMealDate",OracleDbType.Date,dtMealDate,ParameterDirection.Input),
                                };
                            DataTable dtMeal = con.GetSqlResultToDt(strSql2, paras2);
                        #endregion
                            if (dtMeal != null && dtMeal.Rows.Count > 0)
                            {
                                #region 根据数据表中的报餐值对集合进行赋值
                                string strOrderBreakfast = dtMeal.Rows[0]["OrderBreakfast"].ToString();
                                newStaffMeal["OrderBreakfast"] = "0";
                                newStaffMeal["OrderBreakfastName"] = "未报餐";
                                if ("1".Equals(strOrderBreakfast))
                                {
                                    newStaffMeal["OrderBreakfast"] = "1";
                                    newStaffMeal["OrderBreakfastName"] = "报餐";
                                }
                                string strOrderLunch = dtMeal.Rows[0]["OrderLunch"].ToString();
                                newStaffMeal["OrderLunch"] = "0";
                                newStaffMeal["OrderLunchName"] = "未报餐";
                                if ("1".Equals(strOrderLunch))
                                {
                                    newStaffMeal["OrderLunch"] = "1";
                                    newStaffMeal["OrderLunchName"] = "报餐";
                                }
                                string strOrderDinner = dtMeal.Rows[0]["OrderDinner"].ToString();
                                newStaffMeal["OrderDinner"] = "0";
                                newStaffMeal["OrderDinnerName"] = "未报餐";
                                if ("1".Equals(strOrderDinner))
                                {
                                    newStaffMeal["OrderDinner"] = "1";
                                    newStaffMeal["OrderDinnerName"] = "报餐";
                                }
                                #endregion
                            }
                        }
                    }
                    dsStaff.Tables[0].Rows.Add(newStaffMeal);
                }
                return dsStaff;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 获取管理者或组内员工的数据源
        /// </summary>
        /// <param name="searchStaffEntity">员工查询实体</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet</returns>
        public static DataSet SearchManagersOrMembers(SearchStaffEntity searchStaffEntity, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);

            try
            {
                con.Open();
                string strSql = " SELECT Staff.Staffid,Staff.Staffname,Staff.Organizationid,"
                                   + "  Staff.Jobs,Staff.Staffstatus,Staff.Staffcode, Staff.Post,"
                                   + "  (CASE Staff.staffStatus"
                                   + "  WHEN 0 THEN '未入职'"
                                   + "  WHEN 1 THEN '试用 '"
                                   + "  WHEN 2 THEN '转正'"
                                   + "  WHEN 3 THEN '离职'"
                                   + "  ELSE '' END) AS staffStatusName,"
                                   + "  Jobs.Jobsname,Org.Organizationname,Org.OrganizationFullName,Post.PostName,0 Sel"
                                   + "  FROM TP_HR_Staff Staff"
                                   + "  LEFT JOIN TP_MST_Jobs Jobs ON  Jobs.Jobsid = Staff.Jobs "
                                   + "  LEFT JOIN TP_MST_Organization Org ON Org.Organizationid = Staff.Organizationid "
                                   + "  LEFT JOIN TP_MST_Post Post on Post.Postid = Staff.Post "
                                   + "  WHERE Staff.Accountid = :accountID "
                                   + "  AND Staff.Staffstatus in(1,2)"
                                   + "  AND (Staff.Jobs = :jobs OR :jobs is null)";
                if (!string.IsNullOrEmpty(searchStaffEntity.StaffCode))
                {
                    strSql += " AND Staff.Staffcode like '%" + searchStaffEntity.StaffCode + "%'";
                }

                if (!string.IsNullOrEmpty(searchStaffEntity.StaffName))
                {
                    strSql += " AND Staff.Staffname like '%" + searchStaffEntity.StaffName + "%'";
                }

                if (searchStaffEntity.ManagerSalaryID != 0)
                {
                    strSql += @" AND not Exists (
                                    Select * from TP_TAT_MANAGERS managers
                                    where Staff.STAFFID = managers.MANAGER and managers.ManagerSalaryID= " + searchStaffEntity.ManagerSalaryID + @")
                                 AND not Exists (
                                    Select * from TP_TAT_Members members 
                                    where Staff.STAFFID = members.Member and members.ManagerSalaryID= " + searchStaffEntity.ManagerSalaryID + ")";
                }
                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter(":accountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
                    new OracleParameter(":jobs", OracleDbType.Int32, searchStaffEntity.Jobs, ParameterDirection.Input),
                    new OracleParameter(":jobs", OracleDbType.Int32, searchStaffEntity.Jobs, ParameterDirection.Input),
                };
                DataSet ds = con.GetSqlResultToDs(strSql, paras);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }
        #endregion
    }
}