/******************************************************************************* * 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 { /// /// 员工管理查询逻辑处理 /// public static class HRModuleLogic { #region 员工档案 /// /// 员工当案一览 /// /// 员工档案实体类/param> /// 用户基本信息 /// DataSet 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(); } } } /// /// 是否存在相同的员工编码 /// /// 员工编码 /// 用户基本信息 /// DataSet 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(); } } } /// /// 获取员工行数据 /// /// 员工ID /// DataSet 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(); } } } /// /// 获取员工图片 /// /// 用户基本信息 /// 员工ID /// 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 工资结算 /// /// 根据结算时间获取各基础信息 /// /// 时间信息实体 /// 用户信息 /// 基础信息数据集 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(); } } } /// /// 根据员工工资ID以及员工ID，获取员工工资各明细数据集 /// /// 员工工资ID /// 员工ID /// 各明细集合 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 其他方法 /// /// 获取员工和员工履历信息 /// /// 员工实体类 /// 用户基本信息 /// DataSet 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(); } } } /// /// 获取员工信息中审批信息 /// /// 员工实体类 /// 用户基本信息 /// DataSet 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(); } } } /// /// 根据工号查询员工档案信息 /// /// 员工ID /// 用户基本信息 /// DataSet /// /// 作者 日期 内容 /// 冯雪 2014-9-23 新建 /// 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(); } } } /// /// 根据查询非工号下的员工档案信息 /// /// 员工查询实体 /// 用户基本信息 /// DataSet 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 员工履历 /// /// 获了员工履历行数据 /// /// 员工履历ID /// DataSet 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 员工考勤 /// /// 根据传入的查询实体获取员工考勤表中的数据 /// /// 查询实体 /// 用户基本信息 /// DataSet员工考勤数据集集合 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(); } } } /// /// 获取员工员工考勤编辑信息 /// /// 查询实体 /// 用户基本信息 /// DataSet员工考勤数据集集合 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(); } } } /// /// 将导入Excel中的数据转换为GridView显示的数据集 /// /// Excel中的数据员工集合 /// 用户基本信息 /// DataSet员工考勤信息表 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(); } } } /// /// 获取考勤是否结算 /// /// 数据库连接 /// 员工ID /// 考勤日期 /// 用户基本信息 /// bool已结算返回为True 没有结算返回为False 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 /// /// 根据传入的实体获取行政奖惩信息 /// /// 查询实体 /// 用户基本信息 /// DataSet行政奖惩信息表 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(); } } } /// /// 获取员工报餐信息 /// /// 员工报餐查询实体 /// 用户基本信息 /// DataSet员工报餐信息表 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(); } } } /// /// 获取员工报餐编辑信息 /// /// 员工报餐查询实体 /// 用户基本信息 /// DataSet员工报餐信息表 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(); } } } /// /// 将导入Excel中的数据转换为GridView显示的数据集 /// /// Excel中的数据员工集合 /// 用户基本信息 /// DataSet员工报餐信息表 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(); } } } /// /// 获取管理者或组内员工的数据源 /// /// 员工查询实体 /// 用户基本信息 /// DataSet 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 } }