using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CY.IDAL;
using System.Data.SqlClient;
using System.Data;
using CY.Infrastructure.Query;

namespace CY.SQLDAL
{
    public class OA_CustomerAccessRecordDAL : IOA_CustomerAccessRecordDAL
    {
        private Database _dataBase = null;

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

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.OA_CustomerAccessRecord trueModel = model as Model.OA_CustomerAccessRecord;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                	new SqlParameter("@CutomerType",trueModel.CutomerType),
					new SqlParameter("@CustomerId",trueModel.CustomerId),
					new SqlParameter("@AccessTypeId",trueModel.AccessTypeId),
					new SqlParameter("@StuffId",trueModel.StuffId),
					new SqlParameter("@AccesserId",trueModel.AccesserId),
					new SqlParameter("@StartTime",trueModel.StartTime),
					new SqlParameter("@Receiver",trueModel.Receiver),
					new SqlParameter("@AccessContent",trueModel.AccessContent),
					new SqlParameter("@TurnoverIntention",trueModel.TurnoverIntention),
					new SqlParameter("@EndTime",trueModel.EndTime),
					new SqlParameter("@CreateTime",trueModel.CreateTime),
					new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
					new SqlParameter("@Operator",trueModel.Operator),
					new SqlParameter("@Remark",trueModel.Remark)
            };
            try
            {
                _dataBase.Query("sp_OA_CustomerAccessRecord_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_CustomerAccessRecord trueModel = model as Model.OA_CustomerAccessRecord;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                    new SqlParameter("@Keyid",trueModel.Keyid),
                	new SqlParameter("@CutomerType",trueModel.CutomerType),
					new SqlParameter("@CustomerId",trueModel.CustomerId),
					new SqlParameter("@AccessTypeId",trueModel.AccessTypeId),
					new SqlParameter("@StuffId",trueModel.StuffId),
					new SqlParameter("@AccesserId",trueModel.AccesserId),
					new SqlParameter("@StartTime",trueModel.StartTime),
					new SqlParameter("@Receiver",trueModel.Receiver),
					new SqlParameter("@AccessContent",trueModel.AccessContent),
					new SqlParameter("@TurnoverIntention",trueModel.TurnoverIntention),
					new SqlParameter("@EndTime",trueModel.EndTime),
					new SqlParameter("@CreateTime",trueModel.CreateTime),
					new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
					new SqlParameter("@Operator",trueModel.Operator),
					new SqlParameter("@Remark",trueModel.Remark)
            };
            try
            {
                _dataBase.Query("sp_OA_CustomerAccessRecord_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)
        {
            Model.OA_CustomerAccessRecord trueModel = model as Model.OA_CustomerAccessRecord;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                new SqlParameter("@Keyid",trueModel.Keyid)
            };
            try
            {
                _dataBase.Query("sp_OA_CustomerAccessRecord_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_CustomerAccessRecord> SelectModelPage(Infrastructure.Query.Query query, Infrastructure.Query.Pagination pagination)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 访问记录
        /// </summary>
        /// <param name="pa"></param>
        /// <param name="CustomerID"></param>
        /// <param name="Type"></param>
        /// <param name="MemberId"></param>
        /// <param name="RecordType"></param>
        /// <param name="RecordStartTime"></param>
        /// <param name="RecordEndTime"></param>
        /// <param name="RecordVisiter"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_CustomerAccessRecord> SelectModelPage(Infrastructure.Query.Pagination pa, string CustomerID, string Type, Guid MemberId, string RecordType, DateTime? RecordStartTime, DateTime? RecordEndTime, string RecordVisiter)
        {
            string condition = " And 1=1 ";
            if (!string.IsNullOrEmpty(RecordType))
                condition += " And AccessTypeId ='" + RecordType + "' ";
            if (RecordStartTime != null)
                condition += " And CreateTime >='" + RecordStartTime + "' ";
            if (RecordEndTime != null)
                condition += " And CreateTime <='" + RecordEndTime.Value.AddDays(1) + "' ";
            if (!string.IsNullOrEmpty(RecordVisiter))
                condition += " And AccesserId ='" + RecordVisiter + "' ";

            string selTarget = " a.*,c.Name AS AccessType,d.Name AS StuffName  ";
            string fromSource = "  (SELECT * FROM dbo.OA_CustomerAccessRecord WHERE CustomerId='" + CustomerID + "' and CutomerType ='" + Type + "' and Remark='" + MemberId + "' " + condition + ") AS a LEFT JOIN dbo.Sys_Dictionary AS c ON c.DicType='客户访问类型' AND a.AccessTypeId=c.MeanValue LEFT JOIN dbo.OA_Staff AS d ON  a.StuffId=d.Keyid ";
            return _dataBase.SelectModelPage<Model.OA_CustomerAccessRecord>(pa, selTarget, fromSource, " a.LastUpdateTime DESC");
        }

        /// <summary>
        /// 打印访问记录
        /// </summary>
        /// <param name="pa"></param>
        /// <param name="Keyids"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_CustomerAccessRecord> SelectModelPage(Infrastructure.Query.Pagination pa, string Keyids)
        {
            try
            {
                string selTarget = " a.*,c.Name AS AccessType,d.Name AS StuffName  ";
                string fromSource = "  (SELECT * FROM dbo.OA_CustomerAccessRecord WHERE Keyid in (" + Keyids + ") ) AS a LEFT JOIN dbo.Sys_Dictionary AS c ON c.DicType='客户访问类型' AND a.AccessTypeId=c.MeanValue LEFT JOIN dbo.OA_Staff AS d ON  a.StuffId=d.Keyid ";
                return _dataBase.SelectModelPage<Model.OA_CustomerAccessRecord>(pa, selTarget, fromSource, " a.LastUpdateTime DESC");
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

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

        /// <summary>
        /// 员工绩效列表
        /// </summary>
        /// <param name="pa"></param>
        /// <param name="FirmId"></param>
        /// <param name="StuffId"></param>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_CustomerAccessRecord> getAllIntentionAccessList(Pagination pa, Guid FirmId, string StaffName, string StaffStatus, DateTime? StartTime, DateTime? EndTime)
        {
            string Condition = " where 1=1 ";
            if (!string.IsNullOrEmpty(StaffName))
            {
                Condition += " and a.Name = '" + StaffName + "' ";
            }
            if (!string.IsNullOrEmpty(StaffStatus))
            {
                Condition += " and a.Status = '" + StaffStatus + "' ";
            }
            string conditon1 = " where 1=1 and FirmId='" + FirmId + "' ";
            string conditon2 = " where 1=1 and FirmId='" + FirmId + "' ";
            if (StartTime != null)
            {
                conditon1 += " and CreateTime >= '" + StartTime.ToString() + "' ";
                conditon2 += " and CreateTime < '" + StartTime.ToString() + "' ";
            }
            if (EndTime != null)
            {
                conditon1 += " and CreateTime <= '" + EndTime.Value.AddDays(1).ToString() + "' ";
            }

            string selectTarget = @"   b.*,a.Keyid  AS StuffKeyId,a.Name  AS StuffName ,c.NowMoney  AS NowMoney,d.OldMoney  AS OldMoney,e.AllMoney  AS AllMoney,c.AllCustom  AS AllCustom,g.AllIntention  AS AllIntention";
            string fromSouce = "( SELECT * FROM  dbo.OA_Staff WHERE FirmId='" + FirmId + "') AS a  LEFT JOIN  " +
                 "  (SELECT StuffId , SUM(CASE when AccessTypeId='1' THEN 1 ELSE 0 END) AS Nupdoor , " +
                                  "  SUM(CASE when AccessTypeId='2' THEN 1 ELSE 0 END) AS Nphone, " +
                                     "   SUM(CASE when AccessTypeId='3' THEN 1 ELSE 0 END) AS Nqq, " +
                                     "   SUM(CASE when AccessTypeId='4' THEN 1 ELSE 0 END) AS Nmessage, " +
                                      "  SUM(CASE when AccessTypeId='5' THEN 1 ELSE 0 END) AS NDirectMail, " +
                                      "  SUM(CASE when AccessTypeId='6' THEN 1 ELSE 0 END) AS Nemial,  " +
   "  COUNT(*) AS Nall FROM dbo.OA_CustomerAccessRecord GROUP BY StuffId   ) AS b ON a.Keyid = b.stuffid " +
   " LEFT JOIN ( SELECT BusinessManagerId, SUM(OrderMoney) AS NowMoney ,SUM(CASE when OrderMoney >= 0 THEN 1 ELSE 0 END) AS AllCustom FROM  dbo.OA_CorporateClients " + conditon1 + " GROUP BY BusinessManagerId ) AS c ON a.Keyid = c.BusinessManagerId  LEFT JOIN ( SELECT BusinessManagerId, SUM(OrderMoney) AS OldMoney FROM  dbo.OA_CorporateClients " + conditon2 + " GROUP BY BusinessManagerId ) AS d ON a.Keyid = d.BusinessManagerId  LEFT JOIN ( SELECT BusinessManagerId, SUM(OrderMoney) AS AllMoney FROM  dbo.OA_CorporateClients where  FirmId='" + FirmId + "' GROUP BY BusinessManagerId ) AS e ON a.Keyid = e.BusinessManagerId LEFT JOIN ( SELECT BusinessManagerId, SUM(CASE when Keyid >0 THEN 1 ELSE 0 END) AS AllIntention FROM  dbo.OA_IntentionCustomer  " + conditon1 + "  GROUP BY BusinessManagerId ) AS g ON a.Keyid = g.BusinessManagerId " + Condition;

            return _dataBase.SelectModelPage<Model.OA_CustomerAccessRecord>(pa, selectTarget, fromSouce, "b.StuffId   ");

        }

        /// <summary>
        /// 员工绩效统计
        /// </summary>
        /// <param name="pa"></param>
        /// <param name="FirmId"></param>
        /// <param name="StaffName"></param>
        /// <param name="StaffStatus"></param>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <returns></returns>
        public Model.OA_CustomerAccessRecord SumAllIntentionAccessList(Pagination pa, Guid FirmId, string StaffName, string StaffStatus, DateTime? StartTime, DateTime? EndTime)
        {
            string Condition = " where 1=1 ";
            if (!string.IsNullOrEmpty(StaffName))
            {
                Condition += " and a.Name = '" + StaffName + "' ";
            }
            if (!string.IsNullOrEmpty(StaffStatus))
            {
                Condition += " and a.Status = '" + StaffStatus + "' ";
            }
            string conditon1 = " where 1=1 and FirmId='" + FirmId + "' ";
            string conditon2 = " where 1=1 and FirmId='" + FirmId + "' ";
            if (StartTime != null)
            {
                conditon1 += " and CreateTime >= '" + StartTime.ToString() + "' ";
                conditon2 += " and CreateTime < '" + StartTime.ToString() + "' ";
            }
            if (EndTime != null)
            {
                conditon1 += " and CreateTime <= '" + EndTime.Value.AddDays(1).ToString() + "' ";
            }

            string selectTarget = @" sum(b.Nupdoor) as Nupdoor,sum(b.Nphone) as Nphone,sum(b.Nqq) as Nqq,sum(b.Nmessage) as Nmessage,sum(b.NDirectMail) as NDirectMail,sum(b.Nemial) as Nemial, sum(c.NowMoney)  AS NowMoney,sum(d.OldMoney)  AS OldMoney,sum(e.AllMoney)  AS AllMoney,sum(c.AllCustom)  AS AllCustom,sum(g.AllIntention)  AS AllIntention,sum(b.Nall)  AS Nall";
            string fromSouce = "( SELECT * FROM  dbo.OA_Staff WHERE FirmId='" + FirmId + "') AS a  LEFT JOIN  " +
                 "  (SELECT StuffId , SUM(CASE when AccessTypeId='1' THEN 1 ELSE 0 END) AS Nupdoor , " +
                                  "  SUM(CASE when AccessTypeId='2' THEN 1 ELSE 0 END) AS Nphone, " +
                                     "   SUM(CASE when AccessTypeId='3' THEN 1 ELSE 0 END) AS Nqq, " +
                                     "   SUM(CASE when AccessTypeId='4' THEN 1 ELSE 0 END) AS Nmessage, " +
                                      "  SUM(CASE when AccessTypeId='5' THEN 1 ELSE 0 END) AS NDirectMail, " +
                                      "  SUM(CASE when AccessTypeId='6' THEN 1 ELSE 0 END) AS Nemial,  " +
   "  COUNT(*) AS Nall FROM dbo.OA_CustomerAccessRecord GROUP BY StuffId   ) AS b ON a.Keyid = b.stuffid " +
   " LEFT JOIN ( SELECT BusinessManagerId, SUM(OrderMoney) AS NowMoney ,SUM(CASE when OrderMoney >= 0 THEN 1 ELSE 0 END) AS AllCustom FROM  dbo.OA_CorporateClients " + conditon1 + " GROUP BY BusinessManagerId ) AS c ON a.Keyid = c.BusinessManagerId  LEFT JOIN ( SELECT BusinessManagerId, SUM(OrderMoney) AS OldMoney FROM  dbo.OA_CorporateClients " + conditon2 + " GROUP BY BusinessManagerId ) AS d ON a.Keyid = d.BusinessManagerId  LEFT JOIN ( SELECT BusinessManagerId, SUM(OrderMoney) AS AllMoney FROM  dbo.OA_CorporateClients where  FirmId='" + FirmId + "' GROUP BY BusinessManagerId ) AS e ON a.Keyid = e.BusinessManagerId LEFT JOIN ( SELECT BusinessManagerId, SUM(CASE when Keyid >0 THEN 1 ELSE 0 END) AS AllIntention FROM  dbo.OA_IntentionCustomer  " + conditon1 + "  GROUP BY BusinessManagerId ) AS g ON a.Keyid = g.BusinessManagerId " + Condition;

            IList<Model.OA_CustomerAccessRecord> m_OA_CustomerAccessRecordList = _dataBase.SelectModel<Model.OA_CustomerAccessRecord>(selectTarget, fromSouce);
            if (m_OA_CustomerAccessRecordList != null && m_OA_CustomerAccessRecordList.Count > 0)
            {
                return m_OA_CustomerAccessRecordList[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 员工绩效统计-核算工资
        /// </summary>
        /// <param name="pa"></param>
        /// <param name="FirmId"></param>
        /// <param name="StaffName"></param>
        /// <param name="StaffStatus"></param>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <returns></returns>
        public Model.OA_CustomerAccessRecord SumAccoutingWages(Pagination pa, Guid FirmId, string StaffName, string StaffStatus, DateTime? StartTime, DateTime? EndTime)
        {
            string Condition = " 1=1 ";
            if (!string.IsNullOrEmpty(StaffName))
            {
                Condition += " and c.Name = '" + StaffName + "' ";
            }
            Condition += " and c.FirmId='" + FirmId + "' ";
            if (StartTime != null)
            {
                Condition += " and a.CreateTime >= '" + StartTime.ToString() + "' ";
            }
            if (EndTime != null)
            {
                Condition += " and a.CreateTime <= '" + EndTime.Value.AddDays(1).ToString() + "' ";
            }

            string selectTarget = @" SUM(a.SumPrice) as NowMoney ";

            string fromSouce = " dbo.EC_OrderBasic AS a LEFT JOIN dbo.EC_OrderExtend  AS b ON a.Keyid = b.Keyid LEFT JOIN dbo.OA_Staff AS c ON b.BusinessManagerId = c.Keyid ";

            IList<Model.OA_CustomerAccessRecord> m_OA_CustomerAccessRecordList = _dataBase.SelectModel<Model.OA_CustomerAccessRecord>(selectTarget, fromSouce, Condition);
            if (m_OA_CustomerAccessRecordList != null && m_OA_CustomerAccessRecordList.Count > 0)
            {
                return m_OA_CustomerAccessRecordList[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 员工绩效列表-打印页面
        /// </summary>
        /// <param name="FirmId"></param>
        /// <param name="StaffIds"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_CustomerAccessRecord> getAllIntentionAccessListByIds(Guid FirmId, string StaffIds)
        {
            string Condition = "";
            if (!string.IsNullOrEmpty(StaffIds))
            {
                Condition += " and Keyid in (" + StaffIds + ") ";
            }
            string conditon1 = " where 1=1 and FirmId='" + FirmId + "' ";
            string conditon2 = " where 1=1 and FirmId='" + FirmId + "' ";

            string selectTarget = @"   b.*,a.Name  AS StuffName ,c.NowMoney  AS NowMoney,d.OldMoney  AS OldMoney,e.AllMoney  AS AllMoney,c.AllCustom  AS AllCustom,g.AllIntention  AS AllIntention";
            string fromSouce = "( SELECT * FROM  dbo.OA_Staff WHERE FirmId='" + FirmId + "' " + Condition + ") AS a  LEFT JOIN  " +
                 "  (SELECT StuffId , SUM(CASE when AccessTypeId='1' THEN 1 ELSE 0 END) AS Nupdoor , " +
                                  "  SUM(CASE when AccessTypeId='2' THEN 1 ELSE 0 END) AS Nphone, " +
                                     "   SUM(CASE when AccessTypeId='3' THEN 1 ELSE 0 END) AS Nqq, " +
                                     "   SUM(CASE when AccessTypeId='4' THEN 1 ELSE 0 END) AS Nmessage, " +
                                      "  SUM(CASE when AccessTypeId='5' THEN 1 ELSE 0 END) AS NDirectMail, " +
                                      "  SUM(CASE when AccessTypeId='6' THEN 1 ELSE 0 END) AS Nemial,  " +
   "  COUNT(*) AS Nall FROM dbo.OA_CustomerAccessRecord GROUP BY StuffId   ) AS b ON a.Keyid = b.stuffid " +
   " LEFT JOIN ( SELECT BusinessManagerId, SUM(OrderMoney) AS NowMoney ,SUM(CASE when OrderMoney >= 0 THEN 1 ELSE 0 END) AS AllCustom FROM  dbo.OA_CorporateClients " + conditon1 + " GROUP BY BusinessManagerId ) AS c ON a.Keyid = c.BusinessManagerId  LEFT JOIN ( SELECT BusinessManagerId, SUM(OrderMoney) AS OldMoney FROM  dbo.OA_CorporateClients " + conditon2 + " GROUP BY BusinessManagerId ) AS d ON a.Keyid = d.BusinessManagerId  LEFT JOIN ( SELECT BusinessManagerId, SUM(OrderMoney) AS AllMoney FROM  dbo.OA_CorporateClients where  FirmId='" + FirmId + "' GROUP BY BusinessManagerId ) AS e ON a.Keyid = e.BusinessManagerId LEFT JOIN ( SELECT BusinessManagerId, SUM(CASE when Keyid >0 THEN 1 ELSE 0 END) AS AllIntention FROM  dbo.OA_IntentionCustomer  " + conditon1 + "  GROUP BY BusinessManagerId ) AS g ON a.Keyid = g.BusinessManagerId ";

            return _dataBase.SelectModel<Model.OA_CustomerAccessRecord>(selectTarget, fromSouce, "");

        }
    }
}