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

/*******************************************************************************
 * Copyright(c) 2014 DongkeSoft All rights reserved. / Confidential
 * 类的信息：
 *      1.程序名称：TATModuleLogic.cs
 *      2.功能描述：策略管理信息与数据库的交互类
 * 编辑履历：
 *      作者            日期                版本            修改内容
 *      庄天威          2014/11/18          1.00            新建
 *******************************************************************************/

using System;
using System.Data;
using System.Text;

using Dongke.IBOSS.PRD.Basics.BaseResources;
using Dongke.IBOSS.PRD.Basics.DataAccess;
using Dongke.IBOSS.PRD.Service.DataModels;
using Dongke.IBOSS.PRD.WCF.DataModels;
using Oracle.ManagedDataAccess.Client;

namespace Dongke.IBOSS.PRD.Service.TATModuleLogic
{
    /// <summary>
    /// 策略管理信息与数据库的交互类
    /// </summary>
    public partial class TATModuleLogic
    {
        #region 缺陷扣罚

        /// <summary>
        /// 查询缺陷扣罚信息
        /// </summary>
        /// <param name="dfEntity">查询条件实体</param>
        /// <param name="userInfo">基本用户信息</param>
        /// <returns>ServiceResultEntity查询结果实体</returns>
        public static ServiceResultEntity GetDefectFine(DefectFineEntity dfEntity, SUserInfo userInfo)
        {
            ServiceResultEntity srEntity = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                OracleParameter[] DFParas = new OracleParameter[]{
                    new OracleParameter("In_DefectFineID",OracleDbType.Int32,
                        dfEntity.DefectFineID,ParameterDirection.Input),
                    new OracleParameter("In_AccountID",OracleDbType.Int32,
                        userInfo.AccountID,ParameterDirection.Input),
                    new OracleParameter("In_DefectFineName",OracleDbType.NVarchar2,
                        dfEntity.DefectFineName,ParameterDirection.Input),
                    new OracleParameter("In_SalaryType",OracleDbType.Int32,
                        dfEntity.SalaryType,ParameterDirection.Input),
                    new OracleParameter("In_DefectFine",OracleDbType.Int32,
                        dfEntity.DefectFine,ParameterDirection.Input),
                    new OracleParameter("In_FineType",OracleDbType.Int32,
                        dfEntity.FineType,ParameterDirection.Input),
                    new OracleParameter("In_BeginAccountMonthS",OracleDbType.Date,
                        dfEntity.BeginAccountMonth,ParameterDirection.Input),
                    new OracleParameter("In_BeginAccountMonthE",OracleDbType.Date,
                        dfEntity.BeginAccountMonthEnd,ParameterDirection.Input),
                    new OracleParameter("In_EndAccountMonthS",OracleDbType.Date,
                        dfEntity.EndAccountMonth,ParameterDirection.Input),
                    new OracleParameter("In_EndAccountMonthE",OracleDbType.Date,
                        dfEntity.EndAccountMonthEnd,ParameterDirection.Input),
                    new OracleParameter("In_Remarks",OracleDbType.NVarchar2,
                        dfEntity.Remarks,ParameterDirection.Input),
                    new OracleParameter("In_AuditStatus",OracleDbType.Int32,
                        dfEntity.AuditStatus,ParameterDirection.Input),
                    new OracleParameter("Out_Result",OracleDbType.RefCursor,
                        ParameterDirection.Output),
                };
                DataSet dsDefectFine = con.ExecStoredProcedure("PRO_PM_GetDefectFine", DFParas);
                if (dsDefectFine.Tables.Count == 0 || dsDefectFine.Tables[0].Rows.Count == 0)
                {
                    srEntity.Status = Constant.ServiceResultStatus.NoSearchResults;
                    srEntity.Message = Messages.MSG_CMN_I002;
                }
                else
                {
                    srEntity.Status = Constant.ServiceResultStatus.Success;
                    //srEntity.Result = dsDefectFine;
                    srEntity.Data = dsDefectFine;
                }
                return srEntity;
            }
            catch (Exception ex)
            {
                srEntity.Status = Constant.ServiceResultStatus.SystemError;
                srEntity.Exception = ex;
                return srEntity;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 根据主信息ID获取明细信息
        /// </summary>
        /// <param name="DefectId">主信息ID</param>
        /// <returns>数据集中两个Table，第一个是工种集合，第二个是全部信息</returns>
        public static ServiceResultEntity GetDefectFineDetailById(int DefectId)
        {
            ServiceResultEntity srEntity = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            DataSet dsDetail = new DataSet();
            try
            {
                StringBuilder sbSql = new StringBuilder();
                //首先根据主信息ID查询工种信息（绑定在前台明细页左侧）
                sbSql.Append(@"Select Distinct detail.jobsId,jobs.jobscode,jobs.jobsname,1 as isHave
                               From TP_TAT_DefectFineDetail detail
                               Inner join TP_MST_JOBS jobs
                               On detail.jobsid = jobs.jobsid
                               where detail.DefectFineID = :DefectFineID and detail.ValueFlag = 1");
                OracleParameter[] JobsParas = new OracleParameter[]{
                    new OracleParameter(":DefectFineID",OracleDbType.Int32,
                        DefectId,ParameterDirection.Input),
                };
                DataTable dtJobs = con.GetSqlResultToDt(sbSql.ToString(), JobsParas);
                dsDetail.Tables.Add(dtJobs);
                //然后根据主信息ID查询所有信息
                sbSql.Clear();
                sbSql.Append(@"select detail.defectfineid,detail.JobsID,detail.GoodsId,
                               detail.FinEamount,goods.goodscode,goods.goodsname 
                               from TP_TAT_DefectFineDetail detail
                               inner join TP_MST_GOODS goods 
                               on detail.goodsid = goods.goodsid
                               where detail.DefectFineID = :DefectFineID and detail.ValueFlag = 1");
                OracleParameter[] DetailParas = new OracleParameter[]{
                    new OracleParameter(":DefectFineID",OracleDbType.Int32,
                        DefectId,ParameterDirection.Input),
                };
                DataTable dtDetail = con.GetSqlResultToDt(sbSql.ToString(), DetailParas);
                dsDetail.Tables.Add(dtDetail);
                srEntity.Status = Constant.ServiceResultStatus.Success;
                srEntity.Result = dsDetail;
                return srEntity;
            }
            catch (Exception ex)
            {
                srEntity.Status = Constant.ServiceResultStatus.SystemError;
                srEntity.Exception = ex;
                return srEntity;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        #endregion

        #region 基本工资策略

        /// <summary>
        /// 查询基本工资策略信息
        /// </summary>
        /// <param name="basicSalaryEntity">查询条件实体</param>
        /// <param name="userInfo">基本用户信息</param>
        /// <returns>ServiceResultEntity查询结果实体</returns>
        /// <remarks>
        /// 2014.12.10          任海          新建
        /// </remarks>
        public static ServiceResultEntity GetBasicSalary(BasicSalaryEntity basicSalaryEntity, SUserInfo userInfo)
        {
            ServiceResultEntity srEntity = new ServiceResultEntity();
            IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                oracleConn.Open();
                OracleParameter[] DFParas = new OracleParameter[]{
                    new OracleParameter("In_BasicSalaryID",OracleDbType.Int32,
                        basicSalaryEntity.BasicSalaryID,ParameterDirection.Input),
                    new OracleParameter("In_AccountID",OracleDbType.Int32,
                        userInfo.AccountID,ParameterDirection.Input),
                    new OracleParameter("In_BasicSalaryName",OracleDbType.NVarchar2,
                        basicSalaryEntity.BasicSalaryName,ParameterDirection.Input),
                    new OracleParameter("In_SalaryType",OracleDbType.Int32,
                        basicSalaryEntity.SalaryType,ParameterDirection.Input),
                    new OracleParameter("In_BeginAccountMonthS",OracleDbType.Date,
                        basicSalaryEntity.BeginAccountMonth,ParameterDirection.Input),
                    new OracleParameter("In_BeginAccountMonthE",OracleDbType.Date,
                        basicSalaryEntity.BeginAccountMonthEnd,ParameterDirection.Input),
                    new OracleParameter("In_EndAccountMonthS",OracleDbType.Date,
                        basicSalaryEntity.EndAccountMonth,ParameterDirection.Input),
                    new OracleParameter("In_EndAccountMonthE",OracleDbType.Date,
                        basicSalaryEntity.EndAccountMonthEnd,ParameterDirection.Input),
                    new OracleParameter("In_Remarks",OracleDbType.NVarchar2,
                        basicSalaryEntity.Remarks,ParameterDirection.Input),
                    new OracleParameter("In_AuditStatus",OracleDbType.Int32,
                        basicSalaryEntity.AuditStatus,ParameterDirection.Input),
                    new OracleParameter("Out_Result",OracleDbType.RefCursor,
                        ParameterDirection.Output),
                };
                DataSet dsBasicSalary = oracleConn.ExecStoredProcedure("PRO_PM_GetBasicSalary", DFParas);
                if (dsBasicSalary.Tables.Count == 0 || dsBasicSalary.Tables[0].Rows.Count == 0)
                {
                    srEntity.Status = Constant.ServiceResultStatus.NoSearchResults;
                    srEntity.Message = Messages.MSG_CMN_I002;
                }
                else
                {
                    srEntity.Status = Constant.ServiceResultStatus.Success;
                    //srEntity.Result = dsBasicSalary;
                    srEntity.Data = dsBasicSalary;
                }
                return srEntity;
            }
            catch (Exception ex)
            {
                srEntity.Status = Constant.ServiceResultStatus.SystemError;
                srEntity.Exception = ex;
                return srEntity;
            }
            finally
            {
                if (oracleConn.ConnState == ConnectionState.Open)
                {
                    oracleConn.Close();
                }
            }
        }

        /// <summary>
        /// 查询基本工资策略明细
        /// </summary>
        /// <param name="BasicSalaryID">主信息ID</param>
        /// <param name="sUserInfo">基本用户信息</param>
        /// <returns>ServiceResultEntity服务实体对象,包含工种明细数据源</returns>
        /// <remarks>
        /// 2014.12.12          任海          新建
        /// </remarks>
        public static ServiceResultEntity GetBasicSalaryDetailById(int BasicSalaryID, SUserInfo sUserInfo)
        {
            ServiceResultEntity srEntity = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            DataSet dsDetail = new DataSet();
            try
            {
                StringBuilder sbSql = new StringBuilder();
                sbSql.Append(@"Select bs.basicsalaryid,bs.jobsid,jobs.jobscode,jobs.jobsname,bs.basicsalary,bs.subsidy,bs.minimumsalary,
                                 bs.accountid,bs.valueflag,bs.createtime,bs.createuserid,bs.updatetime,bs.updateuserid,bs.optimestamp
                                 from TP_TAT_BasicSalaryDetail bs
                                 inner join TP_MST_JOBS jobs
                                 On bs.jobsid = jobs.jobsid
                                 where bs.AccountID = :AccountID
                                 and bs.BasicSalaryID = :BasicSalaryID
                                 and bs.valueflag = 1");
                OracleParameter[] JobsParas = new OracleParameter[]{
                    new OracleParameter(":BasicSalaryID",OracleDbType.Int32,
                        BasicSalaryID,ParameterDirection.Input),
                    new OracleParameter(":AccountID",OracleDbType.Int32,
                        sUserInfo.AccountID,ParameterDirection.Input),
                };
                DataTable dtJobs = con.GetSqlResultToDt(sbSql.ToString(), JobsParas);
                dsDetail.Tables.Add(dtJobs);
                srEntity.Status = Constant.ServiceResultStatus.Success;
                //srEntity.Result = dsDetail;
                srEntity.Data = dsDetail;
                return srEntity;
            }
            catch (Exception ex)
            {
                srEntity.Status = Constant.ServiceResultStatus.SystemError;
                srEntity.Exception = ex;
                return srEntity;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        #endregion

        #region 出勤考核

        /// <summary>
        /// 查询出勤考核信息
        /// </summary>
        /// <param name="attendanceEntity">查询条件实体</param>
        /// <param name="userInfo">基本用户信息</param>
        /// <returns>ServiceResultEntity查询结果实体</returns>
        /// <remarks>
        /// 2014.12.15          任海          新建
        /// </remarks>
        public static ServiceResultEntity GetAttendance(AttendanceEntity attendanceEntity, SUserInfo userInfo)
        {
            ServiceResultEntity srEntity = new ServiceResultEntity();
            IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                oracleConn.Open();
                OracleParameter[] DFParas = new OracleParameter[]{
                    new OracleParameter("In_AttendanceID",OracleDbType.Int32,
                        attendanceEntity.AttendanceID,ParameterDirection.Input),
                    new OracleParameter("In_AccountID",OracleDbType.Int32,
                        userInfo.AccountID,ParameterDirection.Input),
                    new OracleParameter("In_AttendanceName",OracleDbType.NVarchar2,
                        attendanceEntity.AttendanceName,ParameterDirection.Input),
                    new OracleParameter("In_BeginAccountMonthS",OracleDbType.Date,
                        attendanceEntity.BeginAccountMonth,ParameterDirection.Input),
                    new OracleParameter("In_BeginAccountMonthE",OracleDbType.Date,
                        attendanceEntity.BeginAccountMonthEnd,ParameterDirection.Input),
                    new OracleParameter("In_EndAccountMonthS",OracleDbType.Date,
                        attendanceEntity.EndAccountMonth,ParameterDirection.Input),
                    new OracleParameter("In_EndAccountMonthE",OracleDbType.Date,
                        attendanceEntity.EndAccountMonthEnd,ParameterDirection.Input),
                    new OracleParameter("In_Remarks",OracleDbType.NVarchar2,
                        attendanceEntity.Remarks,ParameterDirection.Input),
                    new OracleParameter("In_AuditStatus",OracleDbType.Int32,
                        attendanceEntity.AuditStatus,ParameterDirection.Input),
                    new OracleParameter("Out_Result",OracleDbType.RefCursor,
                        ParameterDirection.Output),
                };
                DataSet dsAttendance = oracleConn.ExecStoredProcedure("PRO_PM_GetAttendance", DFParas);
                if (dsAttendance.Tables.Count == 0 || dsAttendance.Tables[0].Rows.Count == 0)
                {
                    srEntity.Status = Constant.ServiceResultStatus.NoSearchResults;
                    srEntity.Message = Messages.MSG_CMN_I002;
                }
                else
                {
                    srEntity.Status = Constant.ServiceResultStatus.Success;
                   // srEntity.Result = dsAttendance;
                    srEntity.Data = dsAttendance;
                }
                return srEntity;
            }
            catch (Exception ex)
            {
                srEntity.Status = Constant.ServiceResultStatus.SystemError;
                srEntity.Exception = ex;
                return srEntity;
            }
            finally
            {
                if (oracleConn.ConnState == ConnectionState.Open)
                {
                    oracleConn.Close();
                }
            }
        }

        /// <summary>
        /// 查询出勤考核的明细对应工种
        /// </summary>
        /// <param name="AttendanceID">主信息ID</param>
        /// <param name="sUserInfo">基本用户信息</param>
        /// <returns>ServiceResultEntity服务实体对象,包含工种明细数据源</returns>
        /// <remarks>
        /// 2014.12.16          任海              新建
        /// </remarks>
        public static ServiceResultEntity GetAttendanceEntityById(int AttendanceID, SUserInfo sUserInfo)
        {
            ServiceResultEntity srEntity = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            DataSet dsDetail = new DataSet();
            try
            {
                StringBuilder sbSql = new StringBuilder();
                sbSql.Append(@"Select at.AttendanceID,at.jobsid,jobs.jobscode,jobs.jobsname,
                                 at.accountid,at.valueflag,at.createtime,at.createuserid,at.updatetime,at.updateuserid,at.optimestamp,1 as isHave
                                 from TP_TAT_AttendanceDetail at
                                 inner join TP_MST_JOBS jobs
                                 On at.jobsid = jobs.jobsid
                                 where at.AccountID = :AccountID
                                 and at.AttendanceID = :AttendanceID
                                 and at.valueflag = 1");
                OracleParameter[] JobsParas = new OracleParameter[]{
                    new OracleParameter(":AttendanceID",OracleDbType.Int32,
                        AttendanceID,ParameterDirection.Input),
                    new OracleParameter(":AccountID",OracleDbType.Int32,
                        sUserInfo.AccountID,ParameterDirection.Input),
                };
                DataTable dtJobs = con.GetSqlResultToDt(sbSql.ToString(), JobsParas);
                dsDetail.Tables.Add(dtJobs);
                srEntity.Status = Constant.ServiceResultStatus.Success;
                //srEntity.Result = dsDetail;
                srEntity.Data = dsDetail;
                return srEntity;
            }
            catch (Exception ex)
            {
                srEntity.Status = Constant.ServiceResultStatus.SystemError;
                srEntity.Exception = ex;
                return srEntity;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 查询出勤考核明细的信息
        /// </summary>
        /// <param name="attendanceDetailEntity">查询条件实体</param>
        /// <param name="userInfo">基本用户信息</param>
        /// <returns>ServiceResultEntity查询结果实体</returns>
        /// <remarks>
        /// 2014.12.1           任海          新建
        /// </remarks>
        public static ServiceResultEntity GetAttendanceDetail(AttendanceDetailEntity attendanceDetailEntity, SUserInfo userInfo)
        {
            ServiceResultEntity srEntity = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            DataSet dsDetail = new DataSet();
            try
            {
                StringBuilder sbSql = new StringBuilder();
                sbSql.Append(@"Select AttendanceID,JobsID,RestmMode,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,RestDays,PunishMode,
                               AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp 
                               From TP_TAT_AttendanceDetail 
                               where AccountID = :AccountID and ValueFlag = 1 and AttendanceID = :AttendanceID and JobsID = :JobsID 
                                     ");
                OracleParameter[] JobsParas = new OracleParameter[]{
                   new OracleParameter(":AttendanceID",OracleDbType.Int32,
                        attendanceDetailEntity.AttendanceID,ParameterDirection.Input),
                        new OracleParameter(":AccountID",OracleDbType.Int32,
                        userInfo.AccountID,ParameterDirection.Input),
                        new OracleParameter(":JobsID",OracleDbType.Int32,
                        attendanceDetailEntity.JobsID,ParameterDirection.Input),
                };
                DataTable dtJobs = con.GetSqlResultToDt(sbSql.ToString(), JobsParas);
                dsDetail.Tables.Add(dtJobs);
                srEntity.Status = Constant.ServiceResultStatus.Success;
                //srEntity.Result = dsDetail;
                srEntity.Data = dsDetail;
                return srEntity;
            }
            catch (Exception ex)
            {
                srEntity.Status = Constant.ServiceResultStatus.SystemError;
                srEntity.Exception = ex;
                return srEntity;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 查询出勤考核的明细对应扣款
        /// </summary>
        /// <param name="AttendanceID">主信息ID</param>
        /// <param name="sUserInfo">基本用户信息</param>
        /// <returns>ServiceResultEntity服务实体对象,包含工种明细数据源</returns>
        /// <remarks>
        /// 2014.12.18          任海          新建
        /// </remarks>
        public static ServiceResultEntity GetAttFinesRulesById(int AttendanceID, int JobsID, SUserInfo sUserInfo)
        {
            ServiceResultEntity srEntity = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            DataSet dsDetail = new DataSet();
            try
            {
                StringBuilder sbSql = new StringBuilder();
                sbSql.Append(@"Select at.AttendanceID,at.jobsid,afr.FinesRulesID,afr.BeginAbsentDays,afr.EndAbsentDays,afr.PunishAmount,
                                 at.accountid,at.valueflag,at.createtime,at.createuserid,at.updatetime,at.updateuserid,at.optimestamp 
                                 from TP_TAT_AttendanceDetail at 
                                 inner join TP_TAT_AttFinesRules afr 
                                 On at.jobsid = afr.jobsid 
                                 and at.AttendanceID = afr.AttendanceID 
                                 where at.AccountID = :AccountID 
                                 and at.AttendanceID = :AttendanceID 
                                 and at.JobsID=:JobsID 
                                 and at.valueflag = 1");
                OracleParameter[] JobsParas = new OracleParameter[]{
                    new OracleParameter(":AttendanceID",OracleDbType.Int32,
                        AttendanceID,ParameterDirection.Input),
                    new OracleParameter(":JobsID",OracleDbType.Int32,
                        JobsID,ParameterDirection.Input),
                    new OracleParameter(":AccountID",OracleDbType.Int32,
                        sUserInfo.AccountID,ParameterDirection.Input),
                };
                DataTable dtJobs = con.GetSqlResultToDt(sbSql.ToString(), JobsParas);
                dsDetail.Tables.Add(dtJobs);
                srEntity.Status = Constant.ServiceResultStatus.Success;
                //srEntity.Result = dsDetail;
                srEntity.Data = dsDetail;
                return srEntity;
            }
            catch (Exception ex)
            {
                srEntity.Status = Constant.ServiceResultStatus.SystemError;
                srEntity.Exception = ex;
                return srEntity;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        #endregion

        #region 工价策略

        /// <summary>
        /// 查询工价策略信息
        /// </summary>
        /// <param name="wagesEntity">工价策略查询条件实体</param>
        /// <param name="userInfo">基本用户信息</param>
        /// <returns>ServiceResultEntity查询结果实体</returns>
        public static ServiceResultEntity GetWages(WagesEntity wagesEntity, SUserInfo userInfo)
        {
            ServiceResultEntity srEntity = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                OracleParameter[] DFParas = new OracleParameter[]{
                    new OracleParameter("In_WagesID",OracleDbType.Int32,
                        wagesEntity.WagesID,ParameterDirection.Input),
                    new OracleParameter("In_AccountID",OracleDbType.Int32,
                        userInfo.AccountID,ParameterDirection.Input),
                    new OracleParameter("In_WagesName",OracleDbType.NVarchar2,
                        wagesEntity.WagesName,ParameterDirection.Input),
                    new OracleParameter("In_SalaryType",OracleDbType.Int32,
                        wagesEntity.SalaryType,ParameterDirection.Input),
                    new OracleParameter("In_BeginAccountMonthS",OracleDbType.Date,
                        wagesEntity.BeginAccountMonth,ParameterDirection.Input),
                    new OracleParameter("In_BeginAccountMonthE",OracleDbType.Date,
                        wagesEntity.BeginAccountMonthEnd,ParameterDirection.Input),
                    new OracleParameter("In_EndAccountMonthS",OracleDbType.Date,
                        wagesEntity.EndAccountMonth,ParameterDirection.Input),
                    new OracleParameter("In_EndAccountMonthE",OracleDbType.Date,
                        wagesEntity.EndAccountMonthEnd,ParameterDirection.Input),
                    new OracleParameter("In_Remarks",OracleDbType.NVarchar2,
                        wagesEntity.Remarks,ParameterDirection.Input),
                    new OracleParameter("In_AuditStatus",OracleDbType.Int32,
                        wagesEntity.AuditStatus,ParameterDirection.Input),
                    new OracleParameter("Out_Result",OracleDbType.RefCursor,
                        ParameterDirection.Output),
                };
                DataSet dsWages = con.ExecStoredProcedure("PRO_PM_GetWages", DFParas);
                if (dsWages.Tables.Count == 0 || dsWages.Tables[0].Rows.Count == 0)
                {
                    srEntity.Status = Constant.ServiceResultStatus.NoSearchResults;
                    srEntity.Message = Messages.MSG_CMN_I002;
                }
                else
                {
                    srEntity.Status = Constant.ServiceResultStatus.Success;
                    //srEntity.Result = dsWages;
                    srEntity.Data = dsWages;
                }
                return srEntity;
            }
            catch (Exception ex)
            {
                srEntity.Status = Constant.ServiceResultStatus.SystemError;
                srEntity.Exception = ex;
                return srEntity;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 根据工价策略主信息ID获取对应工种明细信息
        /// </summary>
        /// <param name="WagesId">工价策略主ID</param>
        /// <returns>ServiceResultEntity服务实体对象,包含工种明细数据源</returns>
        public static ServiceResultEntity GetWagesDetailById(int WagesId)
        {
            ServiceResultEntity srEntity = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            DataSet dsDetail = new DataSet();
            try
            {
                StringBuilder sbSql = new StringBuilder();
                //首先根据主信息ID查询工种信息（绑定在前台明细页左侧）
                sbSql.Append(@"Select detail.wagesid,jobs.jobscode,jobs.jobsname,detail.jobsid,detail.pricetype,
                               decode(detail.pricetype, 0, null, 1, null,2, null,3, detail.Kilncarnum,4, detail.Weight, null) as OneCount,
                               decode(detail.pricetype,0, null, 1, null,2, null,3, detail.CarPricing,4, detail.WeightPricing, null) as OnePrice,
                               1 as isHave
                               From TP_TAT_WagesDetail detail
                               Inner join TP_MST_JOBS jobs
                               On detail.jobsid = jobs.jobsid
                               where detail.WagesID = :WagesID and detail.ValueFlag = 1");
                OracleParameter[] JobsParas = new OracleParameter[]{
                    new OracleParameter(":WagesID",OracleDbType.Int32,
                        WagesId,ParameterDirection.Input),
                };
                DataTable dtJobs = con.GetSqlResultToDt(sbSql.ToString(), JobsParas);
                dsDetail.Tables.Add(dtJobs);
                srEntity.Status = Constant.ServiceResultStatus.Success;
                //srEntity.Result = dsDetail;
                srEntity.Data = dsDetail;
                return srEntity;
            }
            catch (Exception ex)
            {
                srEntity.Status = Constant.ServiceResultStatus.SystemError;
                srEntity.Exception = ex;
                return srEntity;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 根据工价策略主信息ID以及对应的工种ID获取对应的产品设定数据源
        /// </summary>
        /// <param name="WagesId">工价策略主ID</param>
        /// <param name="JobsId">对应工种ID</param>
        /// <returns>ServiceResultEntity包含对应产品数据源的服务返回实体</returns>
        public static ServiceResultEntity GetWagesGoodsDetail(int WagesId, int JobsId)
        {
            ServiceResultEntity srEntity = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            DataSet dsDetail = new DataSet();
            try
            {
                StringBuilder sbSql = new StringBuilder();
                //根据主信息ID以及工种ID获取对应的对应产品数据源
                sbSql.Append(@"Select * From TP_TAT_WagesGoodsDetail detail
                               Inner join TP_MST_JOBS jobs
                               On detail.jobsid = jobs.jobsid
                               Inner join TP_MST_Goods goods
                               On Detail.goodsid = goods.goodsid
                               where detail.WagesID = :WagesID 
                               and detail.JobsId = :JobsId and detail.ValueFlag = 1");
                OracleParameter[] GoodsParas = new OracleParameter[]{
                    new OracleParameter(":WagesID",OracleDbType.Int32,
                        WagesId,ParameterDirection.Input),
                    new OracleParameter(":JobsId",OracleDbType.Int32,
                        JobsId,ParameterDirection.Input),
                };
                DataTable dtGoods = con.GetSqlResultToDt(sbSql.ToString(), GoodsParas);
                dsDetail.Tables.Add(dtGoods);
                srEntity.Status = Constant.ServiceResultStatus.Success;
                //srEntity.Result = dsDetail;
                srEntity.Data = dsDetail;
                return srEntity;
            }
            catch (Exception ex)
            {
                srEntity.Status = Constant.ServiceResultStatus.SystemError;
                srEntity.Exception = ex;
                return srEntity;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        #endregion

        #region 质量考核

        /// <summary>
        /// 查询质量考核信息
        /// </summary>
        /// <param name="quality">质量考核查询条件实体</param>
        /// <param name="userInfo">基本用户信息</param>
        /// <returns>ServiceResultEntity查询结果实体</returns>
        public static ServiceResultEntity GetQuality(QualityEntity quality, SUserInfo userInfo)
        {
            ServiceResultEntity srEntity = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                OracleParameter[] DFParas = new OracleParameter[]{
                    new OracleParameter("in_qualityId",OracleDbType.Int32,
                        quality.QualityID,ParameterDirection.Input),  
                    new OracleParameter("in_qualityName",OracleDbType.Varchar2,
                        quality.QualityName,ParameterDirection.Input),      
                    new OracleParameter("in_remarks",OracleDbType.Varchar2,
                        quality.Remarks,ParameterDirection.Input),  
                    new OracleParameter("in_salaryType",OracleDbType.Int32,
                        quality.SalaryType,ParameterDirection.Input), 
                    new OracleParameter("in_auditStatus",OracleDbType.Int32,
                        quality.AuditStatus,ParameterDirection.Input),
                    new OracleParameter("in_beginAccountMonthStart",OracleDbType.Varchar2,
                        quality.BeginAccountMonthStart,ParameterDirection.Input),
                    new OracleParameter("in_beginAccountMonthEnd",OracleDbType.Varchar2,
                        quality.BeginAccountMonthEnd,ParameterDirection.Input),
                    new OracleParameter("in_endAccountMonthStart",OracleDbType.Varchar2,
                        quality.EndAccountMonthStart,ParameterDirection.Input),
                    new OracleParameter("in_endAccountMonthEnd",OracleDbType.Varchar2,
                        quality.EndAccountMonthEnd,ParameterDirection.Input),
                    new OracleParameter("In_AccountID",OracleDbType.Int32,
                        userInfo.AccountID,ParameterDirection.Input),                    
                    new OracleParameter("Out_Result",OracleDbType.RefCursor,
                        ParameterDirection.Output),
                };
                DataSet dsSearchReport = con.ExecStoredProcedure("PRO_TAT_GetQualityData", DFParas);
                if (dsSearchReport.Tables.Count == 0 || dsSearchReport.Tables[0].Rows.Count == 0)
                {
                    srEntity.Status = Constant.ServiceResultStatus.NoSearchResults;
                    srEntity.Message = Messages.MSG_CMN_I002;
                }
                else
                {
                    srEntity.Status = Constant.ServiceResultStatus.Success;
                }
                //srEntity.Result = dsSearchReport;
                srEntity.Data = dsSearchReport;
                return srEntity;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 根据主信息ID获取明细信息
        /// </summary>
        /// <param name="qualityID">主信息ID</param>
        /// <returns>ServiceResultEntity数据集中两个Table，第一个是工种集合，第二个是全部信息</returns>
        public static ServiceResultEntity GetQualityDetailById(int qualityID)
        {
            ServiceResultEntity srEntity = new ServiceResultEntity();
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                OracleParameter[] DFParas = new OracleParameter[]{    
                    new OracleParameter("in_qualityId",OracleDbType.Int32,
                        qualityID,ParameterDirection.Input),  
                    new OracleParameter("out_resultDetail",OracleDbType.RefCursor,
                        ParameterDirection.Output),
                    new OracleParameter("out_resultGoods",OracleDbType.RefCursor,
                        ParameterDirection.Output),
                };
                DataSet dsSearchReport = con.ExecStoredProcedure("PRO_TAT_GetQualityDetailById", DFParas);
                if (dsSearchReport.Tables.Count == 0 || dsSearchReport.Tables[0].Rows.Count == 0)
                {
                    srEntity.Status = Constant.ServiceResultStatus.NoSearchResults;
                    srEntity.Message = Messages.MSG_CMN_I002;
                }
                else
                {
                    srEntity.Status = Constant.ServiceResultStatus.Success;
                }
               // srEntity.Result = dsSearchReport;
                srEntity.Data = dsSearchReport;
                return srEntity;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        #endregion

        #region 进度考核

        /// <summary>
        /// 查询进度考核信息
        /// </summary>
        /// <param name="progres">查询条件实体</param>
        /// <param name="userInfo">基本用户信息</param>
        /// <returns>ServiceResultEntity查询结果实体</returns>
        public static ServiceResultEntity GetProgress(ProgressEntity progres, SUserInfo userInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                OracleParameter[] DFParas = new OracleParameter[]{
                    new OracleParameter("in_progressId",OracleDbType.Int32,
                        progres.ProgressID,ParameterDirection.Input),  
                    new OracleParameter("in_progressName",OracleDbType.Varchar2,
                        progres.ProgressName,ParameterDirection.Input),  
                    new OracleParameter("in_remarks",OracleDbType.Varchar2,
                        progres.Remarks,ParameterDirection.Input),  
                    new OracleParameter("in_salaryType",OracleDbType.Int32,
                        progres.SalaryType,ParameterDirection.Input), 
                    new OracleParameter("in_auditStatus",OracleDbType.Int32,
                        progres.AuditStatus,ParameterDirection.Input),
                    new OracleParameter("in_beginAccountMonthStart",OracleDbType.Varchar2,
                        progres.BeginAccountMonthStart,ParameterDirection.Input),
                    new OracleParameter("in_beginAccountMonthEnd",OracleDbType.Varchar2,
                        progres.BeginAccountMonthEnd,ParameterDirection.Input),
                    new OracleParameter("in_endAccountMonthStart",OracleDbType.Varchar2,
                        progres.EndAccountMonthStart,ParameterDirection.Input),
                    new OracleParameter("in_endAccountMonthEnd",OracleDbType.Varchar2,
                        progres.EndAccountMonthEnd,ParameterDirection.Input),
                    new OracleParameter("In_AccountID",OracleDbType.Int32,
                        userInfo.AccountID,ParameterDirection.Input),                    
                    new OracleParameter("Out_Result",OracleDbType.RefCursor,
                        ParameterDirection.Output),
                };
                DataSet dsSearchReport = con.ExecStoredProcedure("PRO_TAT_GetProgressData", DFParas);
                ServiceResultEntity srEntity = new ServiceResultEntity();
                if (dsSearchReport.Tables.Count == 0 || dsSearchReport.Tables[0].Rows.Count == 0)
                {
                    srEntity.Status = Constant.ServiceResultStatus.NoSearchResults;
                    srEntity.Message = Messages.MSG_CMN_I002;
                }
                else
                {
                    srEntity.Status = Constant.ServiceResultStatus.Success;
                }
                //srEntity.Result = dsSearchReport;
                srEntity.Data = dsSearchReport;
                return srEntity;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 根据主信息ID获取明细信息
        /// </summary>
        /// <param name="progresID">主信息ID</param>
        /// <returns>ServiceResultEntity数据集中两个Table，第一个是工种集合，第二个是全部信息</returns>
        public static ServiceResultEntity GetProgressDetailById(int progressID)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                OracleParameter[] DFParas = new OracleParameter[]{
                    new OracleParameter("in_progressID",OracleDbType.Int32,
                        progressID,ParameterDirection.Input),  
                    new OracleParameter("Out_Result",OracleDbType.RefCursor,
                        ParameterDirection.Output),
                };
                DataSet dsSearchReport = con.ExecStoredProcedure("PRO_TAT_GetProgressDetailById", DFParas);
                ServiceResultEntity srEntity = new ServiceResultEntity();
                if (dsSearchReport.Tables.Count == 0 || dsSearchReport.Tables[0].Rows.Count == 0)
                {
                    srEntity.Status = Constant.ServiceResultStatus.NoSearchResults;
                    srEntity.Message = Messages.MSG_CMN_I002;
                }
                else
                {
                    srEntity.Status = Constant.ServiceResultStatus.Success;
                }
                //srEntity.Result = dsSearchReport;
                srEntity.Data = dsSearchReport;
                return srEntity;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        #endregion

        #region 行政考核

        /// <summary>
        /// 查询行政考核信息
        /// </summary>
        /// <param name="administration">查询条件实体</param>
        /// <param name="userInfo">基本用户信息</param>
        /// <returns>ServiceResultEntity查询结果实体</returns>
        public static ServiceResultEntity GetAdministration(AdministrationEntity administration, SUserInfo userInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                OracleParameter[] DFParas = new OracleParameter[]{
                     new OracleParameter("in_administrationID",OracleDbType.Int32,
                        administration.AdministrationID,ParameterDirection.Input),  
                    new OracleParameter("in_administrationName",OracleDbType.Varchar2,
                        administration.AdministrationName,ParameterDirection.Input),  
                    new OracleParameter("in_remarks",OracleDbType.Varchar2,
                        administration.Remarks,ParameterDirection.Input),  
                    new OracleParameter("in_salaryType",OracleDbType.Int32,
                        administration.SalaryType,ParameterDirection.Input), 
                    new OracleParameter("in_administrationType",OracleDbType.Int32,
                        administration.AdministrationType,ParameterDirection.Input), 
                    new OracleParameter("in_auditStatus",OracleDbType.Int32,
                        administration.AuditStatus,ParameterDirection.Input),
                    new OracleParameter("in_beginAccountMonthStart",OracleDbType.Varchar2,
                        administration.BeginAccountMonthStart,ParameterDirection.Input),
                    new OracleParameter("in_beginAccountMonthEnd",OracleDbType.Varchar2,
                        administration.BeginAccountMonthEnd,ParameterDirection.Input),
                    new OracleParameter("in_endAccountMonthStart",OracleDbType.Varchar2,
                        administration.EndAccountMonthStart,ParameterDirection.Input),
                    new OracleParameter("in_endAccountMonthEnd",OracleDbType.Varchar2,
                        administration.EndAccountMonthEnd,ParameterDirection.Input),
                    new OracleParameter("in_accountID",OracleDbType.Int32,
                        userInfo.AccountID,ParameterDirection.Input),
                    new OracleParameter("out_result",OracleDbType.RefCursor,
                        ParameterDirection.Output),
                };
                DataSet dsSearchReport = con.ExecStoredProcedure("PRO_TAT_GetAdministrationData", DFParas);
                ServiceResultEntity srEntity = new ServiceResultEntity();
                if (dsSearchReport.Tables.Count == 0 || dsSearchReport.Tables[0].Rows.Count == 0)
                {
                    srEntity.Status = Constant.ServiceResultStatus.NoSearchResults;
                    srEntity.Message = Messages.MSG_CMN_I002;
                }
                else
                {
                    srEntity.Status = Constant.ServiceResultStatus.Success;
                }
                //srEntity.Result = dsSearchReport;
                srEntity.Data = dsSearchReport;
                return srEntity;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 根据主信息ID获取明细信息
        /// </summary>
        /// <param name="administrationID">主信息ID</param>
        /// <returns>ServiceResultEntity数据集中两个Table，第一个是工种集合，第二个是全部信息</returns>
        public static ServiceResultEntity GetAdminDetailEntityById(int administrationID)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                OracleParameter[] DFParas = new OracleParameter[]{
                     new OracleParameter("in_administrationID",OracleDbType.Int32,
                        administrationID,ParameterDirection.Input),                                        
                    new OracleParameter("out_resultDetail",OracleDbType.RefCursor,
                        ParameterDirection.Output),
                };
                DataSet dsSearchReport = con.ExecStoredProcedure("PRO_TAT_GetAdminDetailById", DFParas);
                ServiceResultEntity srEntity = new ServiceResultEntity();
                if (dsSearchReport.Tables.Count == 0 || dsSearchReport.Tables[0].Rows.Count == 0)
                {
                    srEntity.Status = Constant.ServiceResultStatus.NoSearchResults;
                    srEntity.Message = Messages.MSG_CMN_I002;
                }
                else
                {
                    srEntity.Status = Constant.ServiceResultStatus.Success;
                }
                //srEntity.Result = dsSearchReport;
                srEntity.Data = dsSearchReport;
                return srEntity;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        #endregion

        /// <summary>
        /// 获取全部使用中的策略明细数据集
        /// </summary>
        /// <param name="SalaryTimeS">结算时间起</param>
        /// <param name="userInfo">当前用户</param>
        /// <returns>各策略明细集</returns>
        public static DataSet GetAllUseStrategy(DateTime SalaryTimeS,SUserInfo userInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                OracleParameter[] DFParas = new OracleParameter[]{
                    new OracleParameter("In_SalaryTimeS",OracleDbType.Date,
                        SalaryTimeS.Date,ParameterDirection.Input),
                    new OracleParameter("In_AccountID",OracleDbType.Int32,
                        userInfo.AccountID,ParameterDirection.Input),
                    new OracleParameter("Out_BasicSalary",OracleDbType.RefCursor,
                        ParameterDirection.Output),
                    new OracleParameter("Out_Attendance",OracleDbType.RefCursor,
                        ParameterDirection.Output),
                    new OracleParameter("Out_WagesS",OracleDbType.RefCursor,
                        ParameterDirection.Output),
                    new OracleParameter("Out_WagesE",OracleDbType.RefCursor,
                        ParameterDirection.Output),
                    new OracleParameter("Out_DefectFine",OracleDbType.RefCursor,
                        ParameterDirection.Output),
                    new OracleParameter("Out_Administration",OracleDbType.RefCursor,
                        ParameterDirection.Output),
                    new OracleParameter("Out_Progress",OracleDbType.RefCursor,
                        ParameterDirection.Output),
                    new OracleParameter("Out_Managers",OracleDbType.RefCursor,
                        ParameterDirection.Output)
                };
                DataSet dsDefectFine = con.ExecStoredProcedure("PRO_TAT_GetAllUseStrategy", DFParas);
                return dsDefectFine;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }
    }
}