using System;
using System.Collections.Generic;
using System.Linq;
using WX.CRM.BLL.Util;
using WX.CRM.Common;
using WX.CRM.IBLL.QH;
using WX.CRM.Model.Entity;

namespace WX.CRM.BLL.QH
{
    public class QH_CUSTOMERCAPITALCOUNT_BL : IQH_CUSTOMERCAPITALCOUNT_Q
    {

        public List<CACHE_QH_CUSTOMERCAPITALCOUNT> GetList_Cache_QH_CustomerCapitalCount(string tradecode)
        {
            using (crmContext db = new crmContext())
            {
                return db.CACHE_QH_CUSTOMERCAPITALCOUNT.Where(m => m.TRADECODE == tradecode).OrderByDescending(m => m.TRADEDATE).ToList();
            }
        }

        public List<QH_CUSTOMERCAPITALCOUNT> GetList_QH_RecentCustomerCapitalcount(ref Common.Pager pager, string tradecode, string Stime, string Etime)
        {
            using (crmContext db = new crmContext())
            {
                var data = db.QH_CUSTOMERCAPITALCOUNT.AsQueryable();

                DateTime _stime;
                DateTime _etime;
                if (!string.IsNullOrWhiteSpace(Stime))
                {
                    _stime = Convert.ToDateTime(Stime);
                    data = data.Where(m => m.TRADEDATE >= _stime);
                }
                if (!string.IsNullOrWhiteSpace(Etime))
                {
                    _etime = Convert.ToDateTime(Etime).AddDays(1);
                    data = data.Where(m => m.TRADEDATE < _etime);
                }
                if (!string.IsNullOrWhiteSpace(tradecode))
                    data = data.Where(m => m.TRADECODE == tradecode);
                #region 排序
                if (pager.order == "desc")
                {
                    switch (pager.sort)
                    {
                        case "INOUTAMT":
                            data = data.OrderByDescending(m => m.INOUTAMT);
                            break;
                        case "TRANSFERGAL":
                            data = data.OrderByDescending(m => m.TRANSFERGAL);
                            break;
                        case "FLOATAMTCHANGED":
                            data = data.OrderByDescending(m => m.FLOATAMTCHANGED);
                            break;
                        case "TOTALGAL":
                            data = data.OrderByDescending(m => m.TOTALGAL);
                            break;
                        case "ORGSAVEDAMT":
                            data = data.OrderByDescending(m => m.ORGSAVEDAMT);
                            break;
                        case "MBSAVEDAMT":
                            data = data.OrderByDescending(m => m.MBSAVEDAMT);
                            break;
                        case "TRADEAMT":
                            data = data.OrderByDescending(m => m.TRADEAMT);
                            break;
                        case "EXTENDELIVERYAMT":
                            data = data.OrderByDescending(m => m.EXTENDELIVERYAMT);
                            break;
                        case "TAKESUREAMT":
                            data = data.OrderByDescending(m => m.TAKESUREAMT);
                            break;
                        case "ENDAMT":
                            data = data.OrderByDescending(m => m.ENDAMT);
                            break;
                        case "RISKRATE":
                            data = data.OrderByDescending(m => m.RISKRATE);
                            break;
                        case "TODAYGALHASFLOAT":
                            data = data.OrderByDescending(m => m.TODAYGALHASFLOAT);
                            break;
                        default:
                            data = data.OrderByDescending(m => m.TRADEDATE);
                            break;
                    }
                }
                else
                {
                    switch (pager.sort)
                    {
                        case "INOUTAMT":
                            data = data.OrderBy(m => m.INOUTAMT);
                            break;
                        case "TRANSFERGAL":
                            data = data.OrderBy(m => m.TRANSFERGAL);
                            break;
                        case "FLOATAMTCHANGED":
                            data = data.OrderBy(m => m.FLOATAMTCHANGED);
                            break;
                        case "TOTALGAL":
                            data = data.OrderBy(m => m.TOTALGAL);
                            break;
                        case "ORGSAVEDAMT":
                            data = data.OrderBy(m => m.ORGSAVEDAMT);
                            break;
                        case "MBSAVEDAMT":
                            data = data.OrderBy(m => m.MBSAVEDAMT);
                            break;
                        case "TRADEAMT":
                            data = data.OrderBy(m => m.TRADEAMT);
                            break;
                        case "EXTENDELIVERYAMT":
                            data = data.OrderBy(m => m.EXTENDELIVERYAMT);
                            break;
                        case "TAKESUREAMT":
                            data = data.OrderBy(m => m.TAKESUREAMT);
                            break;
                        case "ENDAMT":
                            data = data.OrderBy(m => m.ENDAMT);
                            break;
                        case "RISKRATE":
                            data = data.OrderBy(m => m.RISKRATE);
                            break;
                        case "TODAYGALHASFLOAT":
                            data = data.OrderBy(m => m.TODAYGALHASFLOAT);
                            break;
                        default:
                            data = data.OrderBy(m => m.TRADEDATE);
                            break;
                    }
                }
                #endregion
                //data = data.OrderByDescending(m => m.TRADEDATE);
                PagerUtil.SetPager<QH_CUSTOMERCAPITALCOUNT>(ref data, ref pager);
                return data.ToList<QH_CUSTOMERCAPITALCOUNT>();
            }
        }


        //获取资金汇总表列表
        public List<QH_CUSTOMERCAPITALCOUNT> GetList_QH_CustomerCapitalcount(ref Common.Pager pager, string tradecode, string Stime, string Etime, string group, decimal user, decimal his, string businessType)
        {
            using (crmContext db = new crmContext())
            {
                var list = db.QH_CUSTOMERCAPITALCOUNT.AsQueryable();
                var his_list = db.HIS_QH_CUSTOMERCAPITALCOUNT.AsQueryable();
                var customer_list = db.QH_CUSTOMER.AsQueryable();
                if (!string.IsNullOrEmpty(businessType))
                {
                    if (businessType == "-1")
                    {
                        list = list.Where(p => string.IsNullOrEmpty(p.BUSINESSTYPE));
                        his_list = his_list.Where(p => string.IsNullOrEmpty(p.BUSINESSTYPE));
                    }
                    else
                    {
                        list = list.Where(p => p.BUSINESSTYPE == businessType);
                        his_list = his_list.Where(p => p.BUSINESSTYPE == businessType);
                    }
                }
                if (!string.IsNullOrEmpty(tradecode))
                {
                    list = list.Where(m => m.TRADECODE == tradecode);
                    his_list = his_list.Where(m => m.TRADECODE == tradecode);
                }
                if (user > 0)
                {
                    //customer_list = customer_list.Where(m => m.SALEUSERID == user);
                }
                else if (!string.IsNullOrEmpty(group))
                {
                    decimal[] _groupids = OperationUtil.ConvertToDecimal(group.Split(','));
                    var userIds = db.BAS_INNERUSERGROUP.Where(m => _groupids.Contains(m.GID.Value)).Select(m => m.INNERUSERID).ToArray<decimal>();
                    //customer_list = customer_list.Where(m => userIds.Contains(m.SALEUSERID.Value));
                }

                DateTime _stime;
                DateTime _etime;
                if (!string.IsNullOrWhiteSpace(Stime))
                {
                    _stime = Convert.ToDateTime(Stime);
                    list = list.Where(m => m.TRADEDATE >= _stime);
                    his_list = his_list.Where(m => m.TRADEDATE >= _stime);
                }
                if (!string.IsNullOrWhiteSpace(Etime))
                {
                    _etime = Convert.ToDateTime(Etime).AddDays(1);
                    list = list.Where(m => m.TRADEDATE < _etime);
                    his_list = his_list.Where(m => m.TRADEDATE < _etime);
                }

                //list = list.Where(m => customer_list.Select(n => n.TRADECODE).Contains(m.TRADECODE));
                //his_list = his_list.Where(m => customer_list.Select(n=>n.TRADECODE).Contains(m.TRADECODE));

                if (his > 0)
                {
                    list = list.Union(his_list.Select(m => new QH_CUSTOMERCAPITALCOUNT()
                    {
                        PKID = m.PKID,
                        IMPORTID = m.IMPORTID,
                        TRADECODE = m.TRADECODE,
                        CUSTOMERNAME = m.CUSTOMERNAME,
                        INOUTAMT = m.INOUTAMT,
                        INITAMT = m.INITAMT,
                        FLOATAMTCHANGED = m.FLOATAMTCHANGED,
                        TRANSFERGAL = m.TRANSFERGAL,
                        TRADEAMT = m.TRADEAMT,
                        EXTENDELIVERYAMT = m.EXTENDELIVERYAMT,
                        ENDAMT = m.ENDAMT,
                        //TODAYINAMT = m.TODAYINAMT,
                        TODAYGALHASFLOAT = m.TODAYGALHASFLOAT,
                        TOTALGAL = m.TOTALGAL,
                        ORGSAVEDAMT = m.ORGSAVEDAMT,
                        MBSAVEDAMT = m.MBSAVEDAMT,
                        RISKRATE = m.RISKRATE,
                        TAKESUREAMT = m.TAKESUREAMT,
                        TRADEDATE = m.TRADEDATE,

                        CUSTOMERLEVELMODEL = m.CUSTOMERLEVELMODEL,
                        //TODAYMAXAMT = m.TODAYMAXAMT,
                        CTIME = m.CTIME,
                        BUSINESSTYPE = m.BUSINESSTYPE
                    }));
                }
                #region 排序
                if (pager.order == "desc")
                {
                    switch (pager.sort)
                    {
                        case "map_INOUTAMT":
                            list = list.OrderByDescending(m => m.INOUTAMT);
                            break;
                        case "map_TRANSFERGAL":
                            list = list.OrderByDescending(m => m.TRANSFERGAL);
                            break;
                        case "map_FLOATAMTCHANGED":
                            list = list.OrderByDescending(m => m.FLOATAMTCHANGED);
                            break;
                        case "map_TOTALGAL":
                            list = list.OrderByDescending(m => m.TOTALGAL);
                            break;
                        case "map_ORGSAVEDAMT":
                            list = list.OrderByDescending(m => m.ORGSAVEDAMT);
                            break;
                        case "map_MBSAVEDAMT":
                            list = list.OrderByDescending(m => m.MBSAVEDAMT);
                            break;
                        case "map_TRADEAMT":
                            list = list.OrderByDescending(m => m.TRADEAMT);
                            break;
                        case "map_EXTENDELIVERYAMT":
                            list = list.OrderByDescending(m => m.EXTENDELIVERYAMT);
                            break;
                        case "map_TAKESUREAMT":
                            list = list.OrderByDescending(m => m.TAKESUREAMT);
                            break;
                        case "map_ENDAMT":
                            list = list.OrderByDescending(m => m.ENDAMT);
                            break;
                        case "map_RISKRATE":
                            list = list.OrderByDescending(m => m.RISKRATE);
                            break;
                        case "map_TODAYGALHASFLOAT":
                            list = list.OrderByDescending(m => m.TODAYGALHASFLOAT);
                            break;
                        default:
                            list = list.OrderByDescending(m => m.TRADEDATE);
                            break;
                    }
                }
                else
                {
                    switch (pager.sort)
                    {
                        case "map_INOUTAMT":
                            list = list.OrderBy(m => m.INOUTAMT);
                            break;
                        case "map_TRANSFERGAL":
                            list = list.OrderBy(m => m.TRANSFERGAL);
                            break;
                        case "map_FLOATAMTCHANGED":
                            list = list.OrderBy(m => m.FLOATAMTCHANGED);
                            break;
                        case "map_TOTALGAL":
                            list = list.OrderBy(m => m.TOTALGAL);
                            break;
                        case "map_ORGSAVEDAMT":
                            list = list.OrderBy(m => m.ORGSAVEDAMT);
                            break;
                        case "map_MBSAVEDAMT":
                            list = list.OrderBy(m => m.MBSAVEDAMT);
                            break;
                        case "map_TRADEAMT":
                            list = list.OrderBy(m => m.TRADEAMT);
                            break;
                        case "map_EXTENDELIVERYAMT":
                            list = list.OrderBy(m => m.EXTENDELIVERYAMT);
                            break;
                        case "map_TAKESUREAMT":
                            list = list.OrderBy(m => m.TAKESUREAMT);
                            break;
                        case "map_ENDAMT":
                            list = list.OrderBy(m => m.ENDAMT);
                            break;
                        case "map_RISKRATE":
                            list = list.OrderBy(m => m.RISKRATE);
                            break;
                        case "map_TODAYGALHASFLOAT":
                            list = list.OrderBy(m => m.TODAYGALHASFLOAT);
                            break;
                        default:
                            list = list.OrderBy(m => m.TRADEDATE);
                            break;
                    }
                }
                #endregion
                var customerCapitalcoutExtend = (from a in list
                                                 group a by 1 into q
                                                 select new
                                                 {
                                                     PKID = -1,
                                                     INITAMT = q.Sum(p => p.INITAMT),
                                                     INOUTAMT = q.Sum(p => p.INOUTAMT),
                                                     FLOATAMTCHANGED = q.Sum(p => p.FLOATAMTCHANGED),
                                                     TRANSFERGAL = q.Sum(p => p.TRANSFERGAL),
                                                     TRADEAMT = q.Sum(p => p.TRADEAMT),
                                                     EXTENDELIVERYAMT = q.Sum(p => p.EXTENDELIVERYAMT),
                                                     ENDAMT = q.Sum(p => p.ENDAMT),
                                                     //TODAYINAMT = q.Sum(p => p.TODAYINAMT),
                                                     TODAYGALHASFLOAT = q.Sum(p => p.TODAYGALHASFLOAT),
                                                     TOTALGAL = q.Sum(p => p.TOTALGAL),
                                                     ORGSAVEDAMT = q.Sum(p => p.ORGSAVEDAMT),
                                                     MBSAVEDAMT = q.Sum(p => p.MBSAVEDAMT),
                                                     ENDBALANCE = q.Sum(p => p.ENDBALANCE),
                                                     TAKESUREAMT = q.Sum(p => p.TAKESUREAMT)
                                                 }).FirstOrDefault();
                PagerUtil.SetPager<QH_CUSTOMERCAPITALCOUNT>(ref list, ref pager);
                var return_List = list.ToList();
                if (customerCapitalcoutExtend != null)
                {
                    QH_CUSTOMERCAPITALCOUNT customerCapitalcout = new QH_CUSTOMERCAPITALCOUNT()
                    {
                        PKID = customerCapitalcoutExtend.PKID,
                        IMPORTID = 0,
                        TRADECODE = "-",
                        CUSTOMERNAME = "-",
                        INOUTAMT = customerCapitalcoutExtend.INOUTAMT,
                        ENDBALANCE = customerCapitalcoutExtend.ENDBALANCE,
                        INITAMT = customerCapitalcoutExtend.INITAMT,
                        FLOATAMTCHANGED = customerCapitalcoutExtend.FLOATAMTCHANGED,
                        TRANSFERGAL = customerCapitalcoutExtend.TRANSFERGAL,
                        TRADEAMT = customerCapitalcoutExtend.TRADEAMT,
                        EXTENDELIVERYAMT = customerCapitalcoutExtend.EXTENDELIVERYAMT,
                        ENDAMT = customerCapitalcoutExtend.ENDAMT,
                        //TODAYINAMT = customerCapitalcoutExtend.TODAYINAMT,
                        TODAYGALHASFLOAT = customerCapitalcoutExtend.TODAYGALHASFLOAT,
                        TOTALGAL = customerCapitalcoutExtend.TOTALGAL,
                        ORGSAVEDAMT = customerCapitalcoutExtend.ORGSAVEDAMT,
                        MBSAVEDAMT = customerCapitalcoutExtend.MBSAVEDAMT,
                        RISKRATE = "-",
                        TAKESUREAMT = customerCapitalcoutExtend.TAKESUREAMT,
                        TRADEDATE = null,
                        CUSTOMERLEVELMODEL = "-",
                        //TODAYMAXAMT=null,
                        CTIME = null,
                        BUSINESSTYPE = ""
                    };
                    return_List.Add(customerCapitalcout);
                }
                return return_List;
            }
        }

        //public List<CustomerCapitalCount> GetList_CustomerCapitalCount(DateTime t1,DateTime t2)
        //{
        //    using (var db=new  crmContext())
        //    {
        //        var q = (from a in db.QH_CUSTOMERCAPITALCOUNT
        //                 join b in (from c in db.QH_TRANSFERTAMTCOUNT where c.TRADEDATE >= t1 && c.TRADEDATE < t2 select c) on a.TRADECODE equals b.TRADECODE into temp1
        //                 from ab in temp1.DefaultIfEmpty()
        //                 where  a.TRADEDATE>=t1 && a.TRADEDATE<t2
        //                 select new CustomerCapitalCount
        //                 { 
        //                     tradeCode=a.TRADECODE,
        //                     deptCode="",//部门编码
        //                     beginDate=a.TRADEDATE.Value,
        //                     endDate=a.TRADEDATE.Value,
        //                     //beginDate = DateTime.SpecifyKind((DateTime)a.TRADEDATE.Value,DateTimeKind.Utc),
        //                     //endDate = DateTime.SpecifyKind((DateTime)a.TRADEDATE.Value,DateTimeKind.Utc),
        //                     initAmt=a.INITAMT,//期初权益
        //                     deposit=ab.INAMT,//入金
        //                     outAmt=ab.OUTAMT,//出金
        //                     securityDepositChanged = 0,//保证金变动
        //                     floatAmtChanged = a.FLOATAMTCHANGED,//浮亏金变动
        //                     deliveryAmtChanged = 0,//交割保证金变动
        //                     transferGAL = a.TRANSFERGAL,//转让盈亏
        //                     deliveryGAL = 0,//交割盈亏
        //                     salesAmt = 0,//销售收入
        //                     buyAmt = 0,//购货支出
        //                     tradeAmt = a.TRADEAMT,//交易手续费
        //                     deliveryAmt = 0,//交割手续费
        //                     extenDeliveryAmt = a.EXTENDELIVERYAMT,//延期交割补偿费
        //                     otherItem = 0,//当日其它项
        //                     endAmt = a.ENDAMT,//期末余额
        //                     curRights = a.ENDAMT,//当日权益
        //                     creationDate = a.CTIME,//创建时间
        //                     creationBy = 10000,//创建人
        //                     todayMaxAMT=ab.TODAYMAXAMT//出入金净值峰值
        //                 })
        //                 .ToList().Select(p => new CustomerCapitalCount()
        //                 {
        //                     tradeCode = p.tradeCode,
        //                     deptCode = p.deptCode,
        //                     beginDate = DateTime.SpecifyKind((DateTime)p.beginDate, DateTimeKind.Utc),
        //                     endDate = DateTime.SpecifyKind((DateTime)p.endDate, DateTimeKind.Utc),
        //                     initAmt = p.initAmt,
        //                     deposit = p.deposit,
        //                     outAmt = p.outAmt,
        //                     securityDepositChanged = p.securityDepositChanged,
        //                     floatAmtChanged = p.floatAmtChanged,
        //                     deliveryAmtChanged = p.deliveryAmtChanged,
        //                     transferGAL = p.transferGAL,
        //                     salesAmt = p.salesAmt,
        //                     buyAmt = p.buyAmt,
        //                     tradeAmt = p.tradeAmt,
        //                     deliveryAmt = p.deliveryAmt,
        //                     extenDeliveryAmt = p.extenDeliveryAmt,
        //                     otherItem = p.otherItem,
        //                     endAmt = p.endAmt,
        //                     curRights = p.curRights,
        //                     creationDate = p.creationDate,
        //                     creationBy = p.creationBy,
        //                     todayMaxAMT = p.todayMaxAMT
        //                 })
        //                 ;
        //        return q.ToList();
        //    }            
        //}
    }
}