using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CY.IDAL;
using System.Data.SqlClient;
using System.Data;
using CY.Model;
using System.Transactions;
namespace CY.SQLDAL
{
    //工资管理
    public class OA_WageManageDAL : IOA_WageManageDAL
    {
        private Database _dataBase = null;

        public OA_WageManageDAL()
        {
            _dataBase = new Database();
        }

        public OA_WageManageDAL(Database dataBase)
        {
            _dataBase = dataBase;
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.OA_WageManage trueModel = model as Model.OA_WageManage;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {            	
                new SqlParameter("@FirmId", trueModel.FirmId) ,            
                new SqlParameter("@MemberId", trueModel.MemberId) ,            
                new SqlParameter("@SW_Type", trueModel.SW_Type) ,            
                new SqlParameter("@SW_Process", trueModel.SW_Process) ,            
                new SqlParameter("@SW_BasicSalary", trueModel.SW_BasicSalary) ,            
                new SqlParameter("@SW_BasicSalaryType", trueModel.SW_BasicSalaryType) ,            
                new SqlParameter("@SW_WorkAge", trueModel.SW_WorkAge) ,            
                new SqlParameter("@SW_PieceMoney", trueModel.SW_PieceMoney) ,            
                new SqlParameter("@SW_OutputMoneyCommission", trueModel.SW_OutputMoneyCommission) ,            
                new SqlParameter("@SW_ProfitCommission", trueModel.SW_ProfitCommission) ,            
                new SqlParameter("@SW_WorkAgeMoney", trueModel.SW_WorkAgeMoney) ,            
                new SqlParameter("@SW_Allowance", trueModel.SW_Allowance) ,            
                new SqlParameter("@SW_LateDeductions", trueModel.SW_LateDeductions) ,            
                new SqlParameter("@SW_LeaveEarlyDeductions", trueModel.SW_LeaveEarlyDeductions) ,            
                new SqlParameter("@SW_AbsentDeductions", trueModel.SW_AbsentDeductions) ,            
                new SqlParameter("@SW_LeaveDeductions", trueModel.SW_LeaveDeductions) ,            
                new SqlParameter("@SW_SocialSecurityComment", trueModel.SW_SocialSecurityComment) ,            
                new SqlParameter("@SW_SocialSecurityYou", trueModel.SW_SocialSecurityYou) ,            
                new SqlParameter("@SW_IsDel", trueModel.SW_IsDel) ,            
                new SqlParameter("@OrderNum", trueModel.OrderNum) ,            
                new SqlParameter("@LastUpdateTime", trueModel.LastUpdateTime) ,            
                new SqlParameter("@Operator", trueModel.Operator)           ,            
                new SqlParameter("@SW_MissionMoney", trueModel.SW_MissionMoney)             ,            
                new SqlParameter("@SW_MissionRate", trueModel.SW_MissionRate)     
            };
            try
            {
                _dataBase.Query("sp_OA_WageManage_Insert", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.OA_WageManage trueModel = model as Model.OA_WageManage;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {                
                new SqlParameter("@Keyid", trueModel.Keyid) ,            
                new SqlParameter("@FirmId", trueModel.FirmId) ,            
                new SqlParameter("@MemberId", trueModel.MemberId) ,            
                new SqlParameter("@SW_Type", trueModel.SW_Type) ,            
                new SqlParameter("@SW_Process", trueModel.SW_Process) ,            
                new SqlParameter("@SW_BasicSalary", trueModel.SW_BasicSalary) ,            
                new SqlParameter("@SW_BasicSalaryType", trueModel.SW_BasicSalaryType) ,            
                new SqlParameter("@SW_WorkAge", trueModel.SW_WorkAge) ,            
                new SqlParameter("@SW_PieceMoney", trueModel.SW_PieceMoney) ,            
                new SqlParameter("@SW_OutputMoneyCommission", trueModel.SW_OutputMoneyCommission) ,            
                new SqlParameter("@SW_ProfitCommission", trueModel.SW_ProfitCommission) ,            
                new SqlParameter("@SW_WorkAgeMoney", trueModel.SW_WorkAgeMoney) ,            
                new SqlParameter("@SW_Allowance", trueModel.SW_Allowance) ,            
                new SqlParameter("@SW_LateDeductions", trueModel.SW_LateDeductions) ,            
                new SqlParameter("@SW_LeaveEarlyDeductions", trueModel.SW_LeaveEarlyDeductions) ,            
                new SqlParameter("@SW_AbsentDeductions", trueModel.SW_AbsentDeductions) ,            
                new SqlParameter("@SW_LeaveDeductions", trueModel.SW_LeaveDeductions) ,            
                new SqlParameter("@SW_SocialSecurityComment", trueModel.SW_SocialSecurityComment) ,            
                new SqlParameter("@SW_SocialSecurityYou", trueModel.SW_SocialSecurityYou) ,            
                new SqlParameter("@SW_IsDel", trueModel.SW_IsDel) ,            
                new SqlParameter("@OrderNum", trueModel.OrderNum) ,            
                new SqlParameter("@LastUpdateTime", trueModel.LastUpdateTime) ,            
                new SqlParameter("@Operator", trueModel.Operator)             ,            
                new SqlParameter("@SW_MissionMoney", trueModel.SW_MissionMoney)             ,            
                new SqlParameter("@SW_MissionRate", trueModel.SW_MissionRate)             
            };
            try
            {
                _dataBase.Query("sp_OA_WageManage_Update", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool DeleteModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.OA_WageManage trueModel = model as Model.OA_WageManage;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
                {
                	new SqlParameter("@Keyid",trueModel.Keyid)
                };
            try
            {
                _dataBase.Query("sp_OA_WageManage_DeleteRow", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return true;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="query"></param>
        /// <param name="pagination"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_WageManage> SelectModelPage(Infrastructure.Query.Query query, Infrastructure.Query.Pagination pagination)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 单个查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_WageManage> SelectAllModel(Infrastructure.Query.Query query)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 根据编号获得信息
        /// </summary>
        /// <param name="Keyid">编号</param>
        /// <returns></returns>
        public OA_WageManage GetModelByKeyid(int? Keyid)
        {
            try
            {
                if (Keyid == null || Keyid < 0)
                    return null;//错误数据返会空 

                IList<OA_WageManage> result = _dataBase.SelectModel<OA_WageManage>(" * ", " OA_WageManage ", string.Format(" Keyid='{0}'", Keyid)) as IList<OA_WageManage>;//执行查询

                return (null == result || result.Count == 0) ? null : result[0];//返回结果
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="pagination"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_WageManage> SelectModelPage(Infrastructure.Query.Pagination pagination, Guid _FirmId, Guid _MemberId, string _SW_Type, string _SW_Process, decimal? _SW_BasicSalary, string _SW_BasicSalaryType, string _SW_WorkAge, decimal? _SW_PieceMoney, decimal? _SW_OutputMoneyCommission, decimal? _SW_ProfitCommission, decimal? _SW_WorkAgeMoney, decimal? _SW_Allowance, decimal? _SW_LateDeductions, decimal? _SW_LeaveEarlyDeductions, decimal? _SW_AbsentDeductions, decimal? _SW_LeaveDeductions, decimal? _SW_SocialSecurityComment, decimal? _SW_SocialSecurityYou, bool _SW_IsDel, int? _OrderNum, DateTime? _BeginLastUpdateTime, DateTime? _EndLastUpdateTime, string _Operator)
        {
            try
            {
                string condition = " 1=1 ";

                if (_FirmId != Guid.Empty)
                    condition += " and FirmId = '" + _FirmId + "'";

                if (_MemberId != Guid.Empty)
                    condition += " and MemberId = '" + _MemberId + "'";

                if (!string.IsNullOrEmpty(_SW_Type))
                    condition += " and SW_Type = '" + _SW_Type + "'";

                if (!string.IsNullOrEmpty(_SW_Process))
                    condition += " and SW_Process = '" + _SW_Process + "'";

                if (_SW_BasicSalary.HasValue)
                    condition += " and SW_BasicSalary = '" + _SW_BasicSalary + "'";

                if (!string.IsNullOrEmpty(_SW_BasicSalaryType))
                    condition += " and SW_BasicSalaryType = '" + _SW_BasicSalaryType + "'";

                if (!string.IsNullOrEmpty(_SW_WorkAge))
                    condition += " and SW_WorkAge = '" + _SW_WorkAge + "'";

                if (_SW_PieceMoney.HasValue)
                    condition += " and SW_PieceMoney = '" + _SW_PieceMoney + "'";

                if (_SW_OutputMoneyCommission.HasValue)
                    condition += " and SW_OutputMoneyCommission = '" + _SW_OutputMoneyCommission + "'";

                if (_SW_ProfitCommission.HasValue)
                    condition += " and SW_ProfitCommission = '" + _SW_ProfitCommission + "'";

                if (_SW_WorkAgeMoney.HasValue)
                    condition += " and SW_WorkAgeMoney = '" + _SW_WorkAgeMoney + "'";

                if (_SW_Allowance.HasValue)
                    condition += " and SW_Allowance = '" + _SW_Allowance + "'";

                if (_SW_LateDeductions.HasValue)
                    condition += " and SW_LateDeductions = '" + _SW_LateDeductions + "'";

                if (_SW_LeaveEarlyDeductions.HasValue)
                    condition += " and SW_LeaveEarlyDeductions = '" + _SW_LeaveEarlyDeductions + "'";

                if (_SW_AbsentDeductions.HasValue)
                    condition += " and SW_AbsentDeductions = '" + _SW_AbsentDeductions + "'";

                if (_SW_LeaveDeductions.HasValue)
                    condition += " and SW_LeaveDeductions = '" + _SW_LeaveDeductions + "'";

                if (_SW_SocialSecurityComment.HasValue)
                    condition += " and SW_SocialSecurityComment = '" + _SW_SocialSecurityComment + "'";

                if (_SW_SocialSecurityYou.HasValue)
                    condition += " and SW_SocialSecurityYou = '" + _SW_SocialSecurityYou + "'";

                condition += " and SW_IsDel = '" + _SW_IsDel + "'";

                if (_OrderNum.HasValue)
                    condition += " and OrderNum = '" + _OrderNum + "'";

                if (_BeginLastUpdateTime.HasValue)
                    condition += string.Format(" and CAST(LastUpdateTime AS DATE) >='{0}'", _BeginLastUpdateTime);

                if (_EndLastUpdateTime.HasValue)
                    condition += string.Format(" and CAST(LastUpdateTime AS DATE) <='{0}'", _EndLastUpdateTime);

                if (!string.IsNullOrEmpty(_Operator))
                    condition += " and Operator = '" + _Operator + "'";

                return _dataBase.SelectModelPage<Model.OA_WageManage>(pagination, " * ", " OA_WageManage ", " Keyid desc", " Keyid desc ", condition);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="pagination"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_WageManage> SelectModelPage(Infrastructure.Query.Pagination pagination, Guid _FirmId, int? _SW_Process, string _SW_Type, int? DepartMentId, string StaffName, string StaffStatus)
        {
            try
            {
                string condition = " 1=1";
                if (_FirmId != Guid.Empty)
                    condition += " and a.FirmId = '" + _FirmId + "'";
                if (DepartMentId.HasValue)
                    condition += " and a.DepartmentId = '" + DepartMentId + "'";
                if (!string.IsNullOrEmpty(StaffName))
                    condition += " and a.Name = '" + StaffName + "'";
                if (!string.IsNullOrEmpty(StaffStatus))
                    condition += " and a.Status = '" + StaffStatus + "'";

                string condition2 = "";
                if (!string.IsNullOrEmpty(_SW_Type))
                    condition += " and b.SW_Type = '" + _SW_Type + "'";
                if (_SW_Process.HasValue)
                    condition += " and b.SW_Process like '%," + _SW_Process + ",%'";

                return _dataBase.SelectModelPage<Model.OA_WageManage>(pagination, " b.*,c.Name as DepartmentName,a.DepartmentId,a.Name as StaffName,a.MemberId as StaffId,a.Status,a.SM_EndWorkTime ", " OA_Staff as a left join OA_WageManage as b on a.MemberId = b.MemberId " + condition2 + " left join OA_Department as c on a.DepartmentId = c.keyid ", " a.Status DESC,a.DepartmentId asc,a.Name asc", " Status DESC,DepartmentId asc ", condition);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据员工会员编号获得信息
        /// </summary>
        /// <param name="MemberId">编号</param>
        /// <returns></returns>
        public OA_WageManage GetModelByMemberId(Guid MemberId)
        {
            try
            {
                if (MemberId == null || MemberId == Guid.Empty)
                    return null;//错误数据返会空 

                IList<OA_WageManage> result = _dataBase.SelectModel<OA_WageManage>(" * ", " dbo.OA_WageManage ", string.Format(" MemberId='{0}'", MemberId)) as IList<OA_WageManage>;//执行查询

                return (null == result || result.Count == 0) ? null : result[0];//返回结果
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}