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

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

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

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.OA_StaffResume trueModel = model as Model.OA_StaffResume;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {            	new SqlParameter("@Memberid", trueModel.Memberid) ,            
                        	new SqlParameter("@R_G_Avatar", trueModel.R_G_Avatar) ,            
                        	new SqlParameter("@R_G_Name", trueModel.R_G_Name) ,            
                        	new SqlParameter("@R_G_Sex", trueModel.R_G_Sex) ,            
                        	new SqlParameter("@R_G_WorkYear", trueModel.R_G_WorkYear) ,            
                        	new SqlParameter("@R_G_Degree", trueModel.R_G_Degree) ,            
                        	new SqlParameter("@R_G_DocumentsType", trueModel.R_G_DocumentsType) ,            
                        	new SqlParameter("@R_G_DocumentsNumber", trueModel.R_G_DocumentsNumber) ,            
                        	new SqlParameter("@R_G_Email", trueModel.R_G_Email) ,            
                        	new SqlParameter("@R_G_Moblie", trueModel.R_G_Moblie) ,            
                        	new SqlParameter("@R_G_WorkPhone", trueModel.R_G_WorkPhone) ,            
                        	new SqlParameter("@R_G_HomePhone", trueModel.R_G_HomePhone) ,            
                        	new SqlParameter("@R_G_NowProvince", trueModel.R_G_NowProvince) ,            
                        	new SqlParameter("@R_G_NowCity", trueModel.R_G_NowCity) ,            
                        	new SqlParameter("@R_G_NowArea", trueModel.R_G_NowArea) ,            
                        	new SqlParameter("@R_G_NowAddress", trueModel.R_G_NowAddress) ,            
                        	new SqlParameter("@R_G_OldProvence", trueModel.R_G_OldProvence) ,            
                        	new SqlParameter("@R_G_OldCity", trueModel.R_G_OldCity) ,            
                        	new SqlParameter("@R_G_OldArea", trueModel.R_G_OldArea) ,            
                        	new SqlParameter("@R_G_OldAdress", trueModel.R_G_OldAdress) ,            
                        	new SqlParameter("@R_G_CreatTime", trueModel.R_G_CreatTime) ,            
                        	new SqlParameter("@R_G_Language", trueModel.R_G_Language) ,            
                        	new SqlParameter("@R_G_YearsMoney", trueModel.R_G_YearsMoney) ,            
                        	new SqlParameter("@R_G_WorkStatus", trueModel.R_G_WorkStatus) ,            
                        	new SqlParameter("@R_G_QQ", trueModel.R_G_QQ) ,            
                        	new SqlParameter("@R_G_YouHome", trueModel.R_G_YouHome) ,            
                        	new SqlParameter("@R_G_YouHeight", trueModel.R_G_YouHeight) ,            
                        	new SqlParameter("@R_G_YouWeight", trueModel.R_G_YouWeight) ,            
                        	new SqlParameter("@R_G_MarryStatus", trueModel.R_G_MarryStatus) ,            
                        	new SqlParameter("@R_G_YouPolitical", trueModel.R_G_YouPolitical) ,            
                        	new SqlParameter("@R_G_Status", trueModel.R_G_Status) ,            
                        	new SqlParameter("@OrderNum", trueModel.OrderNum) ,            
                        	new SqlParameter("@LastUpdateTime", trueModel.LastUpdateTime) ,            
                        	new SqlParameter("@Operator", trueModel.Operator)             
            		 };
            try
            {
                _dataBase.Query("sp_OA_StaffResume_Insert", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        /// <summary>
        /// 保存简历
        /// </summary>
        /// <param name="m_OA_StaffResume"></param>
        /// <param name="m_OA_StaffResumeIntention"></param>
        /// <param name="m_OA_StaffResumeEducationList"></param>
        /// <param name="m_OA_StaffResumeWorkList"></param>
        /// <returns></returns>
        public bool SaveResume(OA_StaffResume m_OA_StaffResume, OA_StaffResumeIntention m_OA_StaffResumeIntention, List<OA_StaffResumeEducation> m_OA_StaffResumeEducationList, List<OA_StaffResumeWork> m_OA_StaffResumeWorkList)
        {
            try
            {
                OA_StaffResumeIntentionDAL dal_OA_StaffResumeIntentionDAL = new OA_StaffResumeIntentionDAL(_dataBase);
                OA_StaffResumeEducationDAL dal_OA_StaffResumeEducationDAL = new OA_StaffResumeEducationDAL(_dataBase);
                OA_StaffResumeWorkDAL dal_OA_StaffResumeWorkDAL = new OA_StaffResumeWorkDAL(_dataBase);
                bool IsSuccess = true;
                using (TransactionScope t_TransactionScope = new TransactionScope())
                {
                    if (IsSuccess)
                    {
                        if (m_OA_StaffResume.Keyid > 0)
                            IsSuccess = this.UpdateModel(m_OA_StaffResume);
                        else
                            IsSuccess = this.InserModel(m_OA_StaffResume);

                        if (IsSuccess)
                        {
                            if (m_OA_StaffResumeIntention.Keyid > 0)
                                IsSuccess = dal_OA_StaffResumeIntentionDAL.UpdateModel(m_OA_StaffResumeIntention);
                            else
                                IsSuccess = dal_OA_StaffResumeIntentionDAL.InserModel(m_OA_StaffResumeIntention);
                        }

                        if (IsSuccess)
                        {
                            IList<SqlParameter> sqlParms = new List<SqlParameter>() { new SqlParameter("@Memberid", m_OA_StaffResume.Memberid) };
                            _dataBase.Query("sp_OA_StaffResumeEducation_DeleteRows", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
                            IList<SqlParameter> sqlParms1 = new List<SqlParameter>() { new SqlParameter("@Memberid", m_OA_StaffResume.Memberid) };
                            _dataBase.Query("sp_OA_StaffResumeWork_DeleteRows", CommandType.StoredProcedure, sqlParms1.ToArray<SqlParameter>());
                        }

                        if (IsSuccess)
                        {
                            foreach (var item in m_OA_StaffResumeEducationList)
                            {
                                if (IsSuccess)
                                    IsSuccess = dal_OA_StaffResumeEducationDAL.InserModel(item);
                                else
                                    return false;
                            }
                        }

                        if (IsSuccess)
                        {
                            foreach (var item in m_OA_StaffResumeWorkList)
                            {
                                if (IsSuccess)
                                    IsSuccess = dal_OA_StaffResumeWorkDAL.InserModel(item);
                                else
                                    return false;
                            }
                        }
                        if (IsSuccess)
                        {
                            t_TransactionScope.Complete();
                        }
                    }
                }
                return IsSuccess;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.OA_StaffResume trueModel = model as Model.OA_StaffResume;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {                new SqlParameter("@Keyid", trueModel.Keyid) ,            
                            new SqlParameter("@Memberid", trueModel.Memberid) ,            
                            new SqlParameter("@R_G_Avatar", trueModel.R_G_Avatar) ,            
                            new SqlParameter("@R_G_Name", trueModel.R_G_Name) ,            
                            new SqlParameter("@R_G_Sex", trueModel.R_G_Sex) ,            
                            new SqlParameter("@R_G_WorkYear", trueModel.R_G_WorkYear) ,            
                            new SqlParameter("@R_G_Degree", trueModel.R_G_Degree) ,            
                            new SqlParameter("@R_G_DocumentsType", trueModel.R_G_DocumentsType) ,            
                            new SqlParameter("@R_G_DocumentsNumber", trueModel.R_G_DocumentsNumber) ,            
                            new SqlParameter("@R_G_Email", trueModel.R_G_Email) ,            
                            new SqlParameter("@R_G_Moblie", trueModel.R_G_Moblie) ,            
                            new SqlParameter("@R_G_WorkPhone", trueModel.R_G_WorkPhone) ,            
                            new SqlParameter("@R_G_HomePhone", trueModel.R_G_HomePhone) ,            
                            new SqlParameter("@R_G_NowProvince", trueModel.R_G_NowProvince) ,            
                            new SqlParameter("@R_G_NowCity", trueModel.R_G_NowCity) ,            
                            new SqlParameter("@R_G_NowArea", trueModel.R_G_NowArea) ,            
                            new SqlParameter("@R_G_NowAddress", trueModel.R_G_NowAddress) ,            
                            new SqlParameter("@R_G_OldProvence", trueModel.R_G_OldProvence) ,            
                            new SqlParameter("@R_G_OldCity", trueModel.R_G_OldCity) ,            
                            new SqlParameter("@R_G_OldArea", trueModel.R_G_OldArea) ,            
                            new SqlParameter("@R_G_OldAdress", trueModel.R_G_OldAdress) ,            
                            new SqlParameter("@R_G_CreatTime", trueModel.R_G_CreatTime) ,            
                            new SqlParameter("@R_G_Language", trueModel.R_G_Language) ,            
                            new SqlParameter("@R_G_YearsMoney", trueModel.R_G_YearsMoney) ,            
                            new SqlParameter("@R_G_WorkStatus", trueModel.R_G_WorkStatus) ,            
                            new SqlParameter("@R_G_QQ", trueModel.R_G_QQ) ,            
                            new SqlParameter("@R_G_YouHome", trueModel.R_G_YouHome) ,            
                            new SqlParameter("@R_G_YouHeight", trueModel.R_G_YouHeight) ,            
                            new SqlParameter("@R_G_YouWeight", trueModel.R_G_YouWeight) ,            
                            new SqlParameter("@R_G_MarryStatus", trueModel.R_G_MarryStatus) ,            
                            new SqlParameter("@R_G_YouPolitical", trueModel.R_G_YouPolitical) ,            
                            new SqlParameter("@R_G_Status", trueModel.R_G_Status) ,            
                            new SqlParameter("@OrderNum", trueModel.OrderNum) ,            
                            new SqlParameter("@LastUpdateTime", trueModel.LastUpdateTime) ,            
                            new SqlParameter("@Operator", trueModel.Operator)             
            		 };
            try
            {
                _dataBase.Query("sp_OA_StaffResume_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_StaffResume trueModel = model as Model.OA_StaffResume;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
                {
                	new SqlParameter("@Keyid",trueModel.Keyid)
                };
            try
            {
                _dataBase.Query("sp_OA_StaffResume_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_StaffResume> SelectModelPage(Infrastructure.Query.Query query, Infrastructure.Query.Pagination pagination)
        {
            throw new NotImplementedException();
        }

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

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

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

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

        /// <summary>
        /// 根据厂商编号获得信息
        /// </summary>
        /// <param name="Memberid">厂商编号</param>
        /// <returns></returns>
        public OA_StaffResume GetModelByMemberid(Guid Memberid)
        {
            try
            {
                if (Memberid == null || Memberid == Guid.Empty)
                    return null;//错误数据返会空 

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

                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_StaffResume> SelectModelPage(Infrastructure.Query.Pagination pagination, Guid _Memberid, string _R_G_Avatar, string _R_G_Name, string _R_G_Sex, string _R_G_WorkYear, string _R_G_Degree, string _R_G_DocumentsType, string _R_G_DocumentsNumber, string _R_G_Email, string _R_G_Moblie, string _R_G_WorkPhone, string _R_G_HomePhone, string _R_G_NowProvince, string _R_G_NowCity, string _R_G_NowArea, string _R_G_NowAddress, string _R_G_OldProvence, string _R_G_OldCity, string _R_G_OldArea, string _R_G_OldAdress, DateTime? _BeginR_G_CreatTime, DateTime? _EndR_G_CreatTime, int? _R_G_Language, string _R_G_YearsMoney, string _R_G_WorkStatus, string _R_G_QQ, string _R_G_YouHome, int? _R_G_YouHeight, int? _R_G_YouWeight, int? _R_G_MarryStatus, string _R_G_YouPolitical, int? _R_G_Status, int? _OrderNum, DateTime? _BeginLastUpdateTime, DateTime? _EndLastUpdateTime, string _Operator)
        {
            try
            {
                string condition = " 1=1 ";

                if (_Memberid != Guid.Empty)
                    condition += " and Memberid = '" + _Memberid + "' ";

                if (!string.IsNullOrEmpty(_R_G_Avatar))
                    condition += " and R_G_Avatar = '" + _R_G_Avatar + "' ";

                if (!string.IsNullOrEmpty(_R_G_Name))
                    condition += " and R_G_Name = '" + _R_G_Name + "' ";

                if (!string.IsNullOrEmpty(_R_G_Sex))
                    condition += " and R_G_Sex = '" + _R_G_Sex + "' ";

                if (!string.IsNullOrEmpty(_R_G_WorkYear))
                    condition += " and R_G_WorkYear = '" + _R_G_WorkYear + "' ";

                if (!string.IsNullOrEmpty(_R_G_Degree))
                    condition += " and R_G_Degree = '" + _R_G_Degree + "' ";

                if (!string.IsNullOrEmpty(_R_G_DocumentsType))
                    condition += " and R_G_DocumentsType = '" + _R_G_DocumentsType + "' ";

                if (!string.IsNullOrEmpty(_R_G_DocumentsNumber))
                    condition += " and R_G_DocumentsNumber = '" + _R_G_DocumentsNumber + "' ";

                if (!string.IsNullOrEmpty(_R_G_Email))
                    condition += " and R_G_Email = '" + _R_G_Email + "' ";

                if (!string.IsNullOrEmpty(_R_G_Moblie))
                    condition += " and R_G_Moblie = '" + _R_G_Moblie + "' ";

                if (!string.IsNullOrEmpty(_R_G_WorkPhone))
                    condition += " and R_G_WorkPhone = '" + _R_G_WorkPhone + "' ";

                if (!string.IsNullOrEmpty(_R_G_HomePhone))
                    condition += " and R_G_HomePhone = '" + _R_G_HomePhone + "' ";

                if (!string.IsNullOrEmpty(_R_G_NowProvince))
                    condition += " and R_G_NowProvince = '" + _R_G_NowProvince + "' ";

                if (!string.IsNullOrEmpty(_R_G_NowCity))
                    condition += " and R_G_NowCity = '" + _R_G_NowCity + "' ";

                if (!string.IsNullOrEmpty(_R_G_NowArea))
                    condition += " and R_G_NowArea = '" + _R_G_NowArea + "' ";

                if (!string.IsNullOrEmpty(_R_G_NowAddress))
                    condition += " and R_G_NowAddress = '" + _R_G_NowAddress + "' ";

                if (!string.IsNullOrEmpty(_R_G_OldProvence))
                    condition += " and R_G_OldProvence = '" + _R_G_OldProvence + "' ";

                if (!string.IsNullOrEmpty(_R_G_OldCity))
                    condition += " and R_G_OldCity = '" + _R_G_OldCity + "' ";

                if (!string.IsNullOrEmpty(_R_G_OldArea))
                    condition += " and R_G_OldArea = '" + _R_G_OldArea + "' ";

                if (!string.IsNullOrEmpty(_R_G_OldAdress))
                    condition += " and R_G_OldAdress = '" + _R_G_OldAdress + "' ";

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

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

                if (_R_G_Language.HasValue)
                    condition += " and R_G_Language = '" + _R_G_Language + "' ";

                if (!string.IsNullOrEmpty(_R_G_YearsMoney))
                    condition += " and R_G_YearsMoney = '" + _R_G_YearsMoney + "' ";

                if (!string.IsNullOrEmpty(_R_G_WorkStatus))
                    condition += " and R_G_WorkStatus = '" + _R_G_WorkStatus + "' ";

                if (!string.IsNullOrEmpty(_R_G_QQ))
                    condition += " and R_G_QQ = '" + _R_G_QQ + "' ";

                if (!string.IsNullOrEmpty(_R_G_YouHome))
                    condition += " and R_G_YouHome = '" + _R_G_YouHome + "' ";

                if (_R_G_YouHeight.HasValue)
                    condition += " and R_G_YouHeight = '" + _R_G_YouHeight + "' ";

                if (_R_G_YouWeight.HasValue)
                    condition += " and R_G_YouWeight = '" + _R_G_YouWeight + "' ";

                if (_R_G_MarryStatus.HasValue)
                    condition += " and R_G_MarryStatus = '" + _R_G_MarryStatus + "' ";

                if (!string.IsNullOrEmpty(_R_G_YouPolitical))
                    condition += " and R_G_YouPolitical = '" + _R_G_YouPolitical + "' ";

                if (_R_G_Status.HasValue)
                    condition += " and R_G_Status = '" + _R_G_Status + "' ";

                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_StaffResume>(pagination, " * ", " OA_StaffResume ", " Keyid desc", " Keyid desc ", condition);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="pagination"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_StaffResume> SelectModelPage(Infrastructure.Query.Pagination pagination, DateTime? _BeginR_G_CreatTime, string _R_G_Sex, string _R_G_WorkYear, int? _R_I_Consultants_id, string _R_I_PostName)
        {
            try
            {
                string condition = " 1=1 ";

                if (!string.IsNullOrEmpty(_R_G_Sex))
                    condition += " and a.R_G_Sex = '" + _R_G_Sex + "' ";

                if (!string.IsNullOrEmpty(_R_G_WorkYear))
                    condition += " and a.R_G_WorkYear = '" + _R_G_WorkYear + "' ";

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


                if (!string.IsNullOrEmpty(_R_I_PostName))
                    condition += " and b.R_I_PostName like '%" + _R_I_PostName + "%' ";

                if (_R_I_Consultants_id.HasValue)
                    condition += " and b.R_I_Consultants_id = '" + _R_I_Consultants_id + "' ";

                condition += " and a.R_G_Status = '1' ";

                return _dataBase.SelectModelPage<Model.OA_StaffResume>(pagination, " a.*,b.R_I_PostName as R_I_PostName,b.R_I_HopeMoney as R_I_HopeMoney ", " OA_StaffResume as a left join OA_StaffResumeIntention as b on a.Memberid = b.Memberid ", " a.Keyid desc", " Keyid desc ", condition);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}