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_WorkPlanDAL : IOA_WorkPlanDAL
    {
        private Database _dataBase = null;

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

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.OA_WorkPlan trueModel = model as Model.OA_WorkPlan;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                	new SqlParameter("@PlanRunTime", trueModel.PlanRunTime) ,            
                    new SqlParameter("@PlanMoney", trueModel.PlanMoney) ,            
                    new SqlParameter("@PlanRemark", trueModel.PlanRemark) ,            
                    new SqlParameter("@PlanStartTime", trueModel.PlanStartTime) ,            
                    new SqlParameter("@PlanPeople", trueModel.PlanPeople) ,            
                    new SqlParameter("@PlanComplany", trueModel.PlanComplany) ,            
                    new SqlParameter("@PlanStatus", trueModel.PlanStatus) ,            
                    new SqlParameter("@ApprovalStaffId", trueModel.ApprovalStaffId) ,            
                    new SqlParameter("@ApprovalStatus", trueModel.ApprovalStatus) ,            
                    new SqlParameter("@ApprovalTime", trueModel.ApprovalTime) ,            
                    new SqlParameter("@MemberId", trueModel.MemberId) ,            
                    new SqlParameter("@ApprovalContent", trueModel.ApprovalContent) ,            
                    new SqlParameter("@LastUpdateTime", trueModel.LastUpdateTime) ,            
                    new SqlParameter("@Operator", trueModel.Operator) ,            
                    new SqlParameter("@EvaluationStatus", trueModel.EvaluationStatus) ,            
                    new SqlParameter("@EvaluationContent", trueModel.EvaluationContent) ,            
                    new SqlParameter("@EvaluationStaffId", trueModel.EvaluationStaffId) ,            
                    new SqlParameter("@EvaluationTime", trueModel.EvaluationTime) ,            
                    new SqlParameter("@RewardType", trueModel.RewardType) ,            
                    new SqlParameter("@RewardContent", trueModel.RewardContent) ,            
                    new SqlParameter("@RewardResult", trueModel.RewardResult) ,            
                    new SqlParameter("@SentStaffId", trueModel.SentStaffId) ,            
                    new SqlParameter("@RewardMoney", trueModel.RewardMoney) ,            
                    new SqlParameter("@DepartId", trueModel.DepartId) ,            
                    new SqlParameter("@ReciveStaffId", trueModel.ReciveStaffId) ,            
                    new SqlParameter("@PlanType", trueModel.PlanType) ,            
                    new SqlParameter("@PlanDataType", trueModel.PlanDataType) ,            
                    new SqlParameter("@PlanTitle", trueModel.PlanTitle) ,            
                    new SqlParameter("@PlanContent", trueModel.PlanContent)  ,
                     new SqlParameter("@HoubufapiaoStatus", trueModel.HoubufapiaoStatus.HasValue?trueModel.HoubufapiaoStatus.Value:3) ,
                      new SqlParameter("@WufapiaoStatus", trueModel.WufapiaoStatus.HasValue?trueModel.WufapiaoStatus.Value:3) ,
            };
            try
            {
                _dataBase.Query("sp_OA_WorkPlan_Insert", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }



        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int InserModelAndGetId(OA_WorkPlan trueModel)
        {
           
            if (trueModel == null)
            {
                return 0;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                    new SqlParameter("@PlanRunTime", trueModel.PlanRunTime) ,
                    new SqlParameter("@PlanMoney", trueModel.PlanMoney) ,
                    new SqlParameter("@PlanRemark", trueModel.PlanRemark) ,
                    new SqlParameter("@PlanStartTime", trueModel.PlanStartTime) ,
                    new SqlParameter("@PlanPeople", trueModel.PlanPeople) ,
                    new SqlParameter("@PlanComplany", trueModel.PlanComplany) ,
                    new SqlParameter("@PlanStatus", trueModel.PlanStatus) ,
                    new SqlParameter("@ApprovalStaffId", trueModel.ApprovalStaffId) ,
                    new SqlParameter("@ApprovalStatus", trueModel.ApprovalStatus) ,
                    new SqlParameter("@ApprovalTime", trueModel.ApprovalTime) ,
                    new SqlParameter("@MemberId", trueModel.MemberId) ,
                    new SqlParameter("@ApprovalContent", trueModel.ApprovalContent) ,
                    new SqlParameter("@LastUpdateTime", trueModel.LastUpdateTime) ,
                    new SqlParameter("@Operator", trueModel.Operator) ,
                    new SqlParameter("@EvaluationStatus", trueModel.EvaluationStatus) ,
                    new SqlParameter("@EvaluationContent", trueModel.EvaluationContent) ,
                    new SqlParameter("@EvaluationStaffId", trueModel.EvaluationStaffId) ,
                    new SqlParameter("@EvaluationTime", trueModel.EvaluationTime) ,
                    new SqlParameter("@RewardType", trueModel.RewardType) ,
                    new SqlParameter("@RewardContent", trueModel.RewardContent) ,
                    new SqlParameter("@RewardResult", trueModel.RewardResult) ,
                    new SqlParameter("@SentStaffId", trueModel.SentStaffId) ,
                    new SqlParameter("@RewardMoney", trueModel.RewardMoney) ,
                    new SqlParameter("@DepartId", trueModel.DepartId) ,
                    new SqlParameter("@ReciveStaffId", trueModel.ReciveStaffId) ,
                    new SqlParameter("@PlanType", trueModel.PlanType) ,
                    new SqlParameter("@PlanDataType", trueModel.PlanDataType) ,
                    new SqlParameter("@PlanTitle", trueModel.PlanTitle) ,
                    new SqlParameter("@PlanContent", trueModel.PlanContent)  ,
                     new SqlParameter("@HoubufapiaoStatus", trueModel.HoubufapiaoStatus.HasValue?trueModel.HoubufapiaoStatus.Value:3) ,
                      new SqlParameter("@WufapiaoStatus", trueModel.WufapiaoStatus.HasValue?trueModel.WufapiaoStatus.Value:3) ,
            };
            string sql = "Insert Into OA_WorkPlan ([PlanRunTime],[PlanMoney],[PlanRemark],[PlanStartTime], [PlanPeople], [PlanComplany],[PlanStatus], [ApprovalStaffId],[ApprovalStatus], [ApprovalTime],[MemberId],[ApprovalContent],[LastUpdateTime],[Operator],EvaluationStatus,EvaluationContent,EvaluationStaffId,EvaluationTime,RewardType,RewardContent,RewardResult,SentStaffId,RewardMoney,DepartId,ReciveStaffId,PlanType,PlanDataType,PlanTitle,PlanContent,HoubufapiaoStatus,WufapiaoStatus)"
                                            + " Values ( @PlanRunTime,@PlanMoney,@PlanRemark, @PlanStartTime, @PlanPeople, @PlanComplany, @PlanStatus, @ApprovalStaffId, @ApprovalStatus, @ApprovalTime,@MemberId,@ApprovalContent,@LastUpdateTime,@Operator,@EvaluationStatus,@EvaluationContent,@EvaluationStaffId,@EvaluationTime,@RewardType,@RewardContent,@RewardResult,@SentStaffId,@RewardMoney,@DepartId,@ReciveStaffId,@PlanType,@PlanDataType,@PlanTitle,@PlanContent,@HoubufapiaoStatus,@WufapiaoStatus )";

            int id = 0;
            try
            {
                id = _dataBase.InsertDataAndGetId(sql, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return id;
        }

        

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.OA_WorkPlan trueModel = model as Model.OA_WorkPlan;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {	
                    new SqlParameter("@Keyid",trueModel.Keyid),
                    new SqlParameter("@PlanRunTime", trueModel.PlanRunTime) ,            
                    new SqlParameter("@PlanMoney", trueModel.PlanMoney) ,            
                    new SqlParameter("@PlanRemark", trueModel.PlanRemark) ,            
                    new SqlParameter("@PlanStartTime", trueModel.PlanStartTime) ,            
                    new SqlParameter("@PlanPeople", trueModel.PlanPeople) ,            
                    new SqlParameter("@PlanComplany", trueModel.PlanComplany) ,            
                    new SqlParameter("@PlanStatus", trueModel.PlanStatus) ,            
                    new SqlParameter("@ApprovalStaffId", trueModel.ApprovalStaffId) ,            
                    new SqlParameter("@ApprovalStatus", trueModel.ApprovalStatus) ,            
                    new SqlParameter("@ApprovalTime", trueModel.ApprovalTime) ,            
                    new SqlParameter("@MemberId", trueModel.MemberId) ,            
                    new SqlParameter("@ApprovalContent", trueModel.ApprovalContent) ,            
                    new SqlParameter("@LastUpdateTime", trueModel.LastUpdateTime) ,            
                    new SqlParameter("@Operator", trueModel.Operator) ,            
                    new SqlParameter("@EvaluationStatus", trueModel.EvaluationStatus) ,            
                    new SqlParameter("@EvaluationContent", trueModel.EvaluationContent) ,            
                    new SqlParameter("@EvaluationStaffId", trueModel.EvaluationStaffId) ,            
                    new SqlParameter("@EvaluationTime", trueModel.EvaluationTime) ,            
                    new SqlParameter("@RewardType", trueModel.RewardType) ,            
                    new SqlParameter("@RewardContent", trueModel.RewardContent) ,            
                    new SqlParameter("@RewardResult", trueModel.RewardResult) ,            
                    new SqlParameter("@SentStaffId", trueModel.SentStaffId) ,            
                    new SqlParameter("@RewardMoney", trueModel.RewardMoney) ,            
                    new SqlParameter("@DepartId", trueModel.DepartId) ,            
                    new SqlParameter("@ReciveStaffId", trueModel.ReciveStaffId) ,            
                    new SqlParameter("@PlanType", trueModel.PlanType) ,            
                    new SqlParameter("@PlanDataType", trueModel.PlanDataType) ,            
                    new SqlParameter("@PlanTitle", trueModel.PlanTitle) ,            
                    new SqlParameter("@PlanContent", trueModel.PlanContent)  ,
                      new SqlParameter("@HoubufapiaoStatus", trueModel.HoubufapiaoStatus.HasValue?trueModel.HoubufapiaoStatus.Value:3) ,
                      new SqlParameter("@WufapiaoStatus", trueModel.WufapiaoStatus.HasValue?trueModel.WufapiaoStatus.Value:3) ,
            };
            try
            {
                _dataBase.Query("sp_OA_WorkPlan_Update", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

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

            return true;
        }

        public bool DeleteModel(Infrastructure.Domain.IAggregateRoot model)
        {
            return true;
        }

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

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

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

                IList<OA_WorkPlan> result = _dataBase.SelectModel<OA_WorkPlan>(" a.*,b.Name as SentStaffName,c.Name as ReciveStaffName,d.Name as ApprovalStaffName,e.Name as EvaluationStaff,f.Name as PlanDataTypeName,g.Name as DepartName ", " OA_WorkPlan AS a LEFT JOIN dbo.EC_MemberBasic AS b ON a.SentStaffId  = b.MemberId LEFT JOIN dbo.EC_MemberBasic AS c ON a.ReciveStaffId = c.MemberId LEFT JOIN dbo.EC_MemberBasic AS d ON a.ApprovalStaffId  = d.MemberId LEFT JOIN dbo.EC_MemberBasic AS e ON a.EvaluationStaffId = e.MemberId LEFT JOIN dbo.Sys_Dictionary AS f ON a.PlanDataType = f.MeanValue AND f.DicType='工作计划性质' LEFT JOIN dbo.OA_Department AS g ON a.DepartId = g.Keyid ", string.Format(" a.Keyid='{0}'", Keyid)) as IList<OA_WorkPlan>;//执行查询

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

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="MemberId"></param>
        /// <param name="SentStaffId"></param>
        /// <param name="ReciveStaffId"></param>
        /// <param name="PlanType"></param>
        /// <param name="PlanDataType"></param>
        /// <param name="PlanTitle"></param>
        /// <param name="BeginPlanStartTime"></param>
        /// <param name="EndPlanStartTime"></param>
        /// <param name="BeginPlanRunTime"></param>
        /// <param name="EndPlanRunTime"></param>
        /// <param name="PlanPeople"></param>
        /// <param name="PlanStatus"></param>
        /// <param name="EvaluationStatus"></param>
        /// <param name="RewardType"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_WorkPlan> SelectModelPage(Infrastructure.Query.Pagination pagination, Guid MemberId, Guid SentStaffId, Guid ReciveStaffId, int? PlanType, int? PlanDataType, string PlanTitle, DateTime? BeginPlanStartTime, DateTime? EndPlanStartTime, DateTime? BeginPlanRunTime, DateTime? EndPlanRunTime, string PlanPeople, int? PlanStatus, int? ApprovalStatus, int? EvaluationStatus, int? RewardType, string RewardContent = null, string PlanContent = null, string BaocunStatus = null)
        {
            try
            {
                string condition = " 1=1 ";
                if (MemberId != Guid.Empty)
                    condition += " and a.MemberId = '" + MemberId + "'";

                if (SentStaffId != Guid.Empty)
                    condition += " and a.SentStaffId = '" + SentStaffId + "'";

                if (ReciveStaffId != Guid.Empty)
                    condition += " and a.ReciveStaffId = '" + ReciveStaffId + "'";

                if (PlanType > 0)
                {
                    if (PlanType == 5)
                        condition += " and ( a.PlanType = '1' or a.PlanType = '2' )";
                    else
                        condition += " and a.PlanType = '" + PlanType + "'";
                }

                if (PlanDataType > 0)
                {
                    condition += " and a.PlanDataType = '" + PlanDataType + "'";
                }
                else
                {
                    condition += " and a.PlanDataType != '17'  ";
                }
                   

                if (!string.IsNullOrEmpty(PlanTitle))
                    condition += " and a.PlanTitle like '%" + PlanTitle + "%'";

                if (!string.IsNullOrEmpty(PlanPeople))
                    condition += " and a.PlanPeople like '%" + PlanPeople + "%'";

                if (PlanStatus >= 0)
                    condition += " and a.PlanStatus = '" + PlanStatus + "'";

                if (ApprovalStatus >= 0)
                {
                    if (ApprovalStatus == 1)
                        condition += " and (( a.PlanDataType = 17  and a.ApprovalStatus = '1'  )  or (  a.PlanDataType != 17 and (a.ApprovalStatus = '3' or  a.ApprovalStatus = '1')  ))";
                    else
                        condition += " and a.ApprovalStatus = '" + ApprovalStatus + "'";
                }
                if (!string.IsNullOrEmpty(BaocunStatus))
                {
                        condition += " and a.ApprovalStatus != '" + BaocunStatus + "'";
                }
                if (EvaluationStatus >= 0)
                {
                    if (EvaluationStatus == 5)
                        condition += " and ( a.EvaluationStatus = '1' or a.EvaluationStatus = '2' )";
                    else
                        condition += " and a.EvaluationStatus = '" + EvaluationStatus + "'";
                }
                if (!string.IsNullOrWhiteSpace(RewardContent))
                {
                    condition += " and a.RewardContent like '%" + RewardContent + "%'";
                }
                if (!string.IsNullOrWhiteSpace(PlanContent))
                {
                    condition += " and a.PlanContent like '%" + PlanContent + "%'";
                }

                if (RewardType > 0)
                    condition += " and a.RewardType = '" + RewardType + "'";

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

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

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

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

                return _dataBase.SelectModelPage<Model.OA_WorkPlan>(pagination, " a.*,b.Name as SentStaffName,c.Name as ReciveStaffName,d.Name as ApprovalStaffName,e.Name as EvaluationStaff,f.Name as PlanDataTypeName ", " OA_WorkPlan AS a LEFT JOIN dbo.EC_MemberBasic AS b ON a.SentStaffId  = b.MemberId LEFT JOIN dbo.EC_MemberBasic AS c ON a.ReciveStaffId = c.MemberId LEFT JOIN dbo.EC_MemberBasic AS d ON a.ApprovalStaffId  = d.MemberId LEFT JOIN dbo.EC_MemberBasic AS e ON a.EvaluationStaffId = e.MemberId LEFT JOIN dbo.Sys_Dictionary AS f ON a.PlanDataType = f.MeanValue AND f.DicType='工作计划性质'  ", " a.PlanStartTime desc", " PlanStartTime desc ", condition);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 分页查询-工资统计
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="MemberId"></param>
        /// <param name="PlanPeople"></param>
        /// <param name="StatisticsTime"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_WorkPlan> SelectModelPageByWage(Infrastructure.Query.Pagination pagination, Guid MemberId, string PlanPeople,string StatisticsTime)
        {
            try
            {
                string condition = " where 1=1 ";
                if (MemberId != Guid.Empty)
                    condition += " and c.FirmId = '" + MemberId + "'";

                if (!string.IsNullOrEmpty(PlanPeople))
                    condition += " and c.Name like '%" + PlanPeople + "%'";


                string condition1 = "";
                if (!string.IsNullOrEmpty(StatisticsTime))
                {
                    StatisticsTime = StatisticsTime.Replace("年","-").Replace("月","-")+"1";
                    DateTime Time1 = DateTime.Parse(StatisticsTime);
                    DateTime Time2 = DateTime.Parse(StatisticsTime).AddMonths(1);
                    condition1 += " and ((a.EvaluationTime >= '" + Time1 + "' and a.EvaluationTime < '" + Time2 + "') or a.EvaluationTime is null)";
                }

                //condition += " GROUP BY c.MemberId,c.Name,c.Sex,g.Name,c.DepartmentId,RewardType  ";

                return _dataBase.SelectModelPage<Model.OA_WorkPlan>(pagination, " * ", " ( SELECT sum(AllReward) as AllReward ,sum(AllFine) as AllFine,ReciveStaffName,ReciveStaffSex,DepartName,ReciveStaffId  FROM ( select case when RewardType = 1 then sum(RewardMoney) else 0 end as AllReward , case when RewardType = 2 then sum(RewardMoney) else 0 end as AllFine,c.Name as ReciveStaffName,c.Sex as ReciveStaffSex,g.Name as DepartName,c.MemberId as ReciveStaffId from  dbo.OA_Staff AS c LEFT JOIN OA_WorkPlan AS a  ON a.ReciveStaffId = c.MemberId and a.PlanType <> '3' and a.EvaluationStatus <> '0' and a.RewardType <> '0' " + condition1 + " LEFT JOIN dbo.OA_Department AS g ON c.DepartmentId = g.Keyid " + condition + " GROUP BY c.MemberId,c.Name,c.Sex,g.Name,c.DepartmentId,RewardType,c.MemberId ) AS x  GROUP BY ReciveStaffName,ReciveStaffSex,DepartName,ReciveStaffId ) as NEETABLE ", " NEETABLE.DepartName desc", " DepartName desc ");
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}