using Edu.Common.Enum;
using Edu.Common.Enum.Customer;
using Edu.Model.Entity.Customer;
using Edu.Model.ViewModel.Customer;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VT.FW.DB.Dapper;

namespace Edu.Repository.Customer
{
    /// <summary>
    /// 同业客户联系人管理仓储层
    /// </summary>
    public class RB_CustomerRepository : BaseRepository<RB_Customer>
    {
        /// <summary>
        /// 获取客户分页列表..
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Customer_Extend> GetCustomerPageRepository(int pageIndex, int pageSize, out long rowsCount, RB_Customer_Extend query)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@" 
SELECT A.*,IFNULL(B.CategoryName,'') AS CategoryName
FROM RB_Customer AS A LEFT JOIN rb_customer_category AS B ON A.CategoryId=B.CategoryId
WHERE 1=1 
");
            builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Customer_Extend.Status), (int)DateStateEnum.Normal);
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Customer_Extend.Group_Id),query.Group_Id);
                }
                if (query.CustomerId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Customer_Extend.CustomerId), query.CustomerId);
                }
                if (query.CreateBy > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Customer_Extend.CreateBy), query.CreateBy);
                }
                if (!string.IsNullOrEmpty(query.CustomerName))
                {
                    builder.AppendFormat(" AND A.{0} LIKE @CustomerName ", nameof(RB_Customer_Extend.CustomerName));
                    parameters.Add("CustomerName", "%" + query.CustomerName.Trim() + "%");
                }
                if (!string.IsNullOrEmpty(query.ContactNumber))
                {
                    builder.AppendFormat(" AND A.{0} LIKE @ContactNumber ", nameof(RB_Customer_Extend.ContactNumber));
                    parameters.Add("ContactNumber", "%" + query.ContactNumber.Trim() + "%");
                }
                if (query.ApproveState > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Customer_Extend.ApproveState), query.ApproveState);
                }
                if (query.QApproveState > 0)
                {
                    if (query.QApproveState == 3)
                    {
                        builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Customer_Extend.ApproveState), 0);
                    }
                    else
                    {
                        builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Customer_Extend.ApproveState), query.QApproveState);
                    }
                }
                if (query.QCustomerState > 0)
                {
                    switch (query.QCustomerState)
                    {
                        //正式客户
                        case 1: builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Customer_Extend.CustomerState),(int)CustomerStateEnum.Normal); break;
                        //正式客户
                        case 2: builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Customer_Extend.CustomerState), (int)CustomerStateEnum.NotActivation); break;
                        //未通过客户
                        case 3: builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Customer_Extend.ApproveState), 2); break;
                        //黑名单
                        case 4: builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Customer_Extend.CustomerState), (int)CustomerStateEnum.Disable);break;
                    }
                }
                if (!string.IsNullOrEmpty(query.CategoryName))
                {
                    builder.AppendFormat(" AND B.{0} LIKE @CategoryName ", nameof(RB_Customer_Extend.CategoryName));
                    parameters.Add("CategoryName", "%" + query.CategoryName.Trim() + "%");
                }
                if (query.CategoryId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Customer_Extend.CategoryId), query.CategoryId);
                }
            }
            builder.AppendFormat(" ORDER BY A.{0} DESC ", nameof(RB_Customer_Extend.CustomerId));
            return GetPage<RB_Customer_Extend>(pageIndex, pageSize, out rowsCount, builder.ToString(), parameters).ToList();
        }


        /// <summary>
        /// 获取客户列表..
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Customer_Extend> GetCustomerListRepository(RB_Customer_Extend query)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@" 
SELECT A.*,IFNULL(B.CategoryName,'') AS CategoryName,IFNULL(B.IsContract,0) AS IsContract
FROM RB_Customer AS A LEFT  JOIN rb_customer_category AS B ON A.CategoryId=B.CategoryId
WHERE 1=1 
");
            builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Customer_Extend.Status), (int)DateStateEnum.Normal);
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Customer_Extend.Group_Id), query.Group_Id);
                }
                if (!string.IsNullOrEmpty(query.CustomerName))
                {
                    builder.AppendFormat(" AND A.{0} LIKE @CustomerName ", nameof(RB_Customer_Extend.CustomerName));
                    parameters.Add("CustomerName", "%" + query.CustomerName.Trim() + "%");
                }
                if (!string.IsNullOrEmpty(query.ContactNumber))
                {
                    builder.AppendFormat(" AND A.{0} LIKE @ContactNumber ", nameof(RB_Customer_Extend.ContactNumber));
                    parameters.Add("ContactNumber", "%" + query.ContactNumber.Trim() + "%");
                }
                if (query.ApproveState > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Customer_Extend.ApproveState), query.ApproveState);
                }
                if (query.QCustomerState > 0)
                {
                    switch (query.QCustomerState)
                    {
                        //正式客户
                        case 1: builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Customer_Extend.CustomerState), (int)CustomerStateEnum.Normal); break;
                        //正式客户
                        case 2: builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Customer_Extend.CustomerState), (int)CustomerStateEnum.NotActivation); break;
                        //未通过客户
                        case 3: builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Customer_Extend.ApproveState), 2); break;
                        //黑名单
                        case 4: builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Customer_Extend.CustomerState), (int)CustomerStateEnum.Disable); break;
                    }
                }
                if (query.CreateBy > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Customer_Extend.CreateBy), query.CreateBy);
                }
                if (!string.IsNullOrEmpty(query.CustomerIds))
                {
                    builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Customer_Extend.CustomerId), query.CustomerIds);
                }
                if (query.CategoryId > 0)
                {
                    builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Customer_Extend.CategoryId), query.CategoryId);
                }
                if (!string.IsNullOrEmpty(query.QCategoryIds))
                {
                    builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Customer_Extend.CategoryId), query.QCategoryIds);
                }
            }
            return Get<RB_Customer_Extend>(builder.ToString(), parameters).ToList();
        }        


        /// <summary>
        /// 获取同业下学生数量
        /// </summary>
        /// <param name="customerIds"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public List<RB_Customer_Extend> GetCustomerStuNum(string customerIds, int group_Id)
        {
            string sql = $@"
SELECT c.CustomerId,C.CategoryId,COUNT(0) as StuNum 
FROM rb_student s INNER JOIN rb_customer AS c ON  (s.StuSourceId=C.CustomerId AND s.CreateType=2)
WHERE s.`Status`=0 and s.Group_Id ={group_Id} 
and c.CustomerId in({customerIds}) 
GROUP BY c.CustomerId,C.CategoryId
";
            return Get<RB_Customer_Extend>(sql).ToList();
        }

        /// <summary>
        /// 获取同业下订单数量
        /// </summary>
        /// <param name="customerIds"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public List<RB_Customer_Extend> GetCustomerOrderNum(string customerIds, int group_Id)
        {
            string sql = $@"
SELECT o.CustomerId,C.CategoryId,COUNT(0) as OrderNum, SUM(o.PreferPrice) as OrderSales 
FROM rb_order o INNER JOIN rb_customer AS c ON  (o.CustomerId=C.CustomerId)
WHERE o.OrderState in(1,2) and o.Group_Id ={group_Id} and o.CustomerId in({customerIds})  
GROUP BY o.CustomerId,C.CategoryId
";
            return Get<RB_Customer_Extend>(sql).ToList();
        }

        /// <summary>
        /// 同行统计
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Customer_Extend> GetCustomerStaticRepository(RB_Customer_Extend query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.CreateBy,DATE_FORMAT(A.CreateTime,'%Y-%m-%d') AS CreateTime,COUNT(1) AS CustomerCount
FROM RB_Customer AS A 
WHERE 1=1
");
            builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Customer_Extend.Status), (int)DateStateEnum.Normal);
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Customer_Extend.Group_Id), query.Group_Id);
                }
                if (query.CreateBy > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Customer_Extend.CreateBy), query.CreateBy);
                }
                if (!string.IsNullOrEmpty(query.QDate))
                {
                    //指定日期新增同行数量
                    builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%y/%m/%d')= DATE_FORMAT('{1}','%y/%m/%d') ", nameof(RB_Customer_Extend.CreateTime), query.QDate);
                }
                if (!string.IsNullOrEmpty(query.StartDate))
                {
                    builder.AppendFormat(" AND A.{0}>='{1}' ", nameof(RB_Customer_Extend.CreateTime), query.StartDate);
                }
                if (!string.IsNullOrEmpty(query.EndDate))
                {
                    builder.AppendFormat(" AND A.{0}<='{1} 23:59:59' ", nameof(RB_Customer_Extend.CreateTime), query.EndDate);
                }
            }
            builder.AppendFormat(@" GROUP BY A.CreateBy,DATE_FORMAT(A.CreateTime,'%Y/%m/%d') ");
            builder.AppendFormat(@" ORDER BY A.CreateBy,DATE_FORMAT(A.CreateTime,'%Y/%m/%d') ");
            return Get<RB_Customer_Extend>(builder.ToString()).ToList();
        }
    }
}