using System.Collections.Generic;
using System.Linq;
using System.Text;
using Edu.Common.Enum.Finance;
using Edu.Model.Entity.Finance;
using Edu.Model.ViewModel.Finance;

namespace Edu.Repository.Finance
{
    /// <summary>
    /// 财务单据仓储层
    /// </summary>
    public class RB_FinanceRepository : BaseRepository<RB_Finance>
    {
        /// <summary>
        /// 表名称
        /// </summary>
        public string TableName { get { return nameof(RB_Finance); } }

        /// <summary>
        /// 记录表
        /// </summary>
        public string ReCordTableName { get { return nameof(Rb_Workflow_Auditrecord); } }

        /// <summary>
        /// 财务单据 主表查询
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<RB_Finance_Extend> GetListSingle(RB_Finance_Extend model)
        {
            string where = "1=1 AND A.Is_Auto=0 ";
            //单据ID
            if (model.FrID > 0)
            {
                where += string.Format(" AND A." + nameof(RB_Finance.FrID) + " ={0}", model.FrID);
            }
            //集团
            if (model.RB_Group_Id > 0)
            {
                where += string.Format(" AND A." + nameof(RB_Finance.RB_Group_Id) + " ={0}", model.RB_Group_Id);
            }
            if (model.RB_Branch_Id >= 0)
            {
                where += string.Format(" AND A." + nameof(RB_Finance.RB_Branch_Id) + " ={0}", model.RB_Branch_Id);
            }
            if (model.TCID > 0)
            {
                where += string.Format(" AND A.FrID in (select b.FinanceId from rb_travel_finance_relation as b where b.FinanceId=FrID and b.TCID=" + model.TCID + ")");
            }
            if (!string.IsNullOrEmpty(model.TCIDStr))
            {
                where += string.Format(" AND A.FrID in (select b.FinanceId from rb_travel_finance_relation as b where b.FinanceId=FrID and b.TCID in(" + model.TCIDStr + "))");
            }
            if (model.BankList != null && model.BankList.Count > 0)
            {
                where += string.Format(" AND A." + nameof(RB_Finance.FrID) + " in (SELECT b.FinanceId from rb_tradeway as b where b.FinanceId=A.FrID and Type=" + (int)model.BankList?.FirstOrDefault()?.Type + " and AccountId=" + model.BankList?.FirstOrDefault().AccountId + " Group By b.FinanceId) ");
            }
            //查询收款 并且账户类形一致的单据
            if (!string.IsNullOrWhiteSpace(model.AccountIdStr))
            {
                where += string.Format(" AND A." + nameof(RB_Finance.FrID) + " in (SELECT b.FinanceId from rb_tradeway as b where b.FinanceId=A.FrID and Type=1 and AccountId in(" + model.AccountIdStr + ") Group By b.FinanceId) ");
            }
            if (model.ClientID > 0)
            {
                where += string.Format(" AND A." + nameof(RB_Finance.ClientID) + " ={0}", model.ClientID);
            }
            if (!string.IsNullOrEmpty(model.sTradeDate))
            {
                where += string.Format(" AND A." + nameof(RB_Finance_Extend.TradeDate) + ">='{0}'", model.sTradeDate);
            }
            if (!string.IsNullOrEmpty(model.eTradeDate))
            {
                where += string.Format(" AND A." + nameof(RB_Finance_Extend.TradeDate) + "<='{0} 23:59:59'", model.eTradeDate);
            }
            if (model.Is_Cashier >= 0)
            {
                where += string.Format(" AND A." + nameof(RB_Finance_Extend.Is_Cashier) + "={0}", model.Is_Cashier);
            }
            if (model.OrderID > 0)
            {
                where += string.Format(" AND A." + nameof(RB_Finance.OrderID) + " ={0}", model.OrderID);
            }
            if (!string.IsNullOrWhiteSpace(model.OrderIdStr))
            {
                where += string.Format(" AND A." + nameof(RB_Finance.OrderID) + " in({0})", model.OrderIdStr);
            }
            if (model.GuestId > 0)
            {
                where += string.Format(" AND A." + nameof(RB_Finance.GuestId) + " ={0}", model.GuestId);
            }
            if (!string.IsNullOrWhiteSpace(model.GuestIdStr))
            {
                where += string.Format(" AND A." + nameof(RB_Finance.GuestId) + " in({0})", model.GuestIdStr);
            }
            if (model.Type > 0)
            {
                where += string.Format(" AND A." + nameof(RB_Finance.Type) + " ={0}", (int)model.Type);
            }
            if (model.FinanceType.HasValue && model.FinanceType > 0)
            {
                where += string.Format(" AND A." + nameof(RB_Finance.FinanceType) + " ={0}", model.FinanceType);
            }
            if (model.OtherType > 0)
            {
                where += string.Format(" AND A." + nameof(RB_Finance.OtherType) + " ={0}", model.OtherType);
            }
            if (model.ReFinanceId > 0)
            {
                where += string.Format(" AND A." + nameof(RB_Finance.ReFinanceId) + " ={0}", model.ReFinanceId);
            }
            if (!string.IsNullOrEmpty(model.ReFinanceIds))
            {
                where += string.Format(" AND A." + nameof(RB_Finance.ReFinanceId) + " in({0})", model.ReFinanceIds);
            }
            if (model.Status > 0)
            {
                where += string.Format(" AND A." + nameof(RB_Finance.Status) + " ={0}", (int)model.Status);
            }
            if (model.OrderSource > 0)
            {
                where += string.Format(" AND A." + nameof(RB_Finance.OrderSource) + " ={0}", (int)model.OrderSource);
            }
            if (model.SourceID > 0)
            {
                where += string.Format(" AND A." + nameof(RB_Finance.SourceID) + " ={0}", model.SourceID);
            }
            if (!string.IsNullOrEmpty(model.AccountNumber))
            {
                where += string.Format(" AND A." + nameof(RB_Finance.AccountNumber) + " ='{0}'", model.AccountNumber);
            }
            if (!string.IsNullOrEmpty(model.FrIDs))
            {
                where += string.Format(" AND A." + nameof(RB_Finance.FrID) + " in ({0})", model.FrIDs);
            }
            if (!string.IsNullOrWhiteSpace(model.DijieOnlyCode))
            {
                where += string.Format(" AND A." + nameof(RB_Finance.DijieOnlyCode) + " ='{0}'", model.DijieOnlyCode);
            }
            if (model.IsSelectNormal == 1)
            {
                where += string.Format(" AND A." + nameof(RB_Finance.Status) + " in(1,2,5)");
            }
            string sql = string.Format(@"SELECT A.*  FROM {0} AS A WHERE {2} {3}", TableName, ReCordTableName, where, " GROUP BY A.FrID DESC");
            return Get<RB_Finance_Extend>(sql).ToList();
        }


        /// <summary>
        /// 获取营收报表财务单据列表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<RB_Finance_Extend> GetDataStatisticsListThree(RB_Finance_Extend model)
        {
            string where = "1=1 and A.Is_Auto=0";
            where += string.Format(" AND A." + nameof(RB_Finance.Status) + " in({0})", ((int)FinanceAuditStatus.CTemporary + "," + (int)FinanceAuditStatus.InReview + "," + (int)FinanceAuditStatus.Pass));
            //营收报表
            if (!string.IsNullOrEmpty(model.TCIDStr))
            {
                where += string.Format(" AND tfr.TCID in(" + model.TCIDStr + ")");
            }
            if (model.TCID.HasValue && model.TCID > 0)
            {
                where += string.Format(" AND tfr.TCID =" + model.TCID);
            }
            //简易报表
            if (model.sAduitDate.HasValue)
            {
                where += string.Format(" AND A." + nameof(RB_Finance.TradeDate) + ">='{0}'", model.sAduitDate);
            }
            if (model.eAduitDate.HasValue)
            {
                where += string.Format(" AND A." + nameof(RB_Finance.TradeDate) + "<='{0} 23:59:59'", model.eAduitDate);
            }
            if (model.RB_Branch_Id >= 0)
            {
                where += string.Format(" AND A." + nameof(RB_Finance.RB_Branch_Id) + "={0}", model.RB_Branch_Id);
            }
            if (model.RB_Group_Id > 0)
            {
                where += string.Format(" AND A." + nameof(RB_Finance.RB_Group_Id) + "={0}", model.RB_Group_Id);
            }
            if (model.OrderSource.HasValue)
            {
                where += string.Format(" AND A." + nameof(RB_Finance.OrderSource) + "={0}", (int)model.OrderSource);
            }

            string sql = string.Format(@"SELECT A.FrID,tfr.TCID,A.Status,A.TradeDate,IFNULL(A.Is_Cashier,0)Is_Cashier,A.OrderID,
A.OrderSource,A.SourceID,IFNULL(C.Money,0)as Money,IFNULL(A.Fee,0) as Fee,IFNULL(A.PayMoney,0)PayMoney,C.CostTypeId as CostTypeID,D.Name,A.Type
FROM rb_finance AS A 
INNER JOIN rb_financedetail AS C ON A.FrID = C.FinanceId 
INNER JOIN rb_costtype AS D ON D.ID=C.CostTypeId 
left join rb_travel_finance_relation as tfr on tfr.FinanceId=A.FrID
WHERE {1} {2} ", TableName, where, " ORDER BY A.FrID DESC");
            return Get<RB_Finance_Extend>(sql).ToList();
        }

        /// <summary>
        /// 营收报表获取团号下所有的单据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<RB_Finance_Extend> GetListForReport(RB_Finance_Extend model)
        {
            StringBuilder where = new StringBuilder();
            where.Append(" and b.Status in(1,2,5) and b.Is_Auto=0 ");
            StringBuilder builder = new StringBuilder();
            //老徐喊修改的  2019-05-11  收款用交易日期   付款用制单日期
            if (model.sAduitDate.HasValue && model.eAduitDate.HasValue)
            {
                where.Append(" AND case when b.Type=2 then b.CreateDate>='" + model.sAduitDate.Value.ToString("yyyy-MM-dd") + "' and b.CreateDate<='" + model.eAduitDate.Value.ToString("yyyy-MM-dd") + " 23:59:59' else b.TradeDate>='" + model.sAduitDate.Value.ToString("yyyy-MM-dd") + "' and b." + nameof(RB_Finance.TradeDate) + "<='" + model.eAduitDate.Value.ToString("yyyy-MM-dd") + " 23:59:59' end");
            }
            else if (model.sAduitDate.HasValue)
            {
                where.Append(" AND case when b.Type=2 then b." + nameof(RB_Finance.CreateDate) + ">='" + model.sAduitDate.Value.ToString("yyyy-MM-dd") + "' else b." + nameof(RB_Finance.TradeDate) + ">='" + model.sAduitDate.Value.ToString("yyyy-MM-dd") + "' end");
            }
            else if (model.eAduitDate.HasValue)
            {
                where.Append(" AND case when b.Type=2 then b." + nameof(RB_Finance.CreateDate) + "<='" + model.eAduitDate.Value.ToString("yyyy-MM-dd") + " 23:59:59' else b." + nameof(RB_Finance.TradeDate) + "<='" + model.eAduitDate.Value.ToString("yyyy-MM-dd") + " 23:59:59' end");
            }
            if (model.RB_Branch_Id >= 0)
            {
                where.Append(" AND b." + nameof(RB_Finance.RB_Branch_Id) + "=" + model.RB_Branch_Id + "");
            }
            if (model.RB_Group_Id > 0)
            {
                where.Append(" AND b." + nameof(RB_Finance.RB_Group_Id) + "=" + model.RB_Group_Id + "");
            }
            if (!string.IsNullOrEmpty(model.TCIDStr))
            {
                where.Append(" and E.TCID in(" + model.TCIDStr + ")");
            }
            if (model.TCID == -1)
            {
                where.Append(" and IFNULL(E.TCID,0)=0");
            }
            if (model.TCID == -2)
            {
                where.Append(" and IFNULL(E.TCID,0) >0");//班级上的单据
                where.Append(" and IFNULL(b.OrderID,0) =0");
            }
            if (model.IsSelectOrder == 1)
            {
                where.Append(" and IFNULL(b.OrderID,0) >0");//只查询订单相关单据
            }
            if (model.OrderSource.HasValue)
            {
                where.Append(" and IFNULL(b.OrderSource,0) =" + (int)model.OrderSource);
            }
            if (model.FinanceType.HasValue && model.FinanceType > 0)
            {
                where.Append(" AND b." + nameof(RB_Finance.FinanceType) + "=" + model.FinanceType + "");
            }

            builder.AppendFormat(@"select distinct b.FrID,IFNULL(b.MatchMoney,0) as MatchMoney,IFNULL(E.TCID,0)TCID,D.Name,b.Status,b.TradeDate,b.CreateBy,b.CreateDate,b.RB_Branch_Id,a.ID,a.CostTypeId,IFNULL(b.Is_Cashier,0)Is_Cashier,a.Money,IFNULL(b.OrderID,0) OrderID,b.OrderSource,b.SourceID,IFNULL(b.PayMoney,0)PayMoney,IFNULL(b.Fee,0)Fee,b.Type 
from rb_financedetail a INNER JOIN rb_finance as b on a.FinanceId=b.FrID and a.`Status`=0 
INNER JOIN rb_costtype AS D ON D.ID=a.CostTypeId
LEFT JOIN rb_tradeway as td ON b.FrID=td.FinanceId
LEFT JOIN rb_travel_finance_relation as E ON A.FinanceId=E.FinanceId where 1=1 " + where);
            return Get<RB_Finance_Extend>(builder.ToString() + " ORDER BY b.FrID DESC ").ToList();
        }

        /// <summary>
        /// 班级专用查询
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<RB_Finance_Extend> GetListForReportClass(RB_Finance_Extend model, string datebaseStr)
        {
            StringBuilder where = new StringBuilder();
            where.Append(" and b.Status in(1,2,5) and b.Is_Auto=0 ");
            StringBuilder builder = new StringBuilder();
            //老徐喊修改的  2019-05-11  收款用交易日期   付款用制单日期
            if (model.sAduitDate.HasValue && model.eAduitDate.HasValue)
            {
                where.Append(" AND case when b.Type=2 then b.CreateDate>='" + model.sAduitDate.Value.ToString("yyyy-MM-dd") + "' and b.CreateDate<='" + model.eAduitDate.Value.ToString("yyyy-MM-dd") + " 23:59:59' else b.TradeDate>='" + model.sAduitDate.Value.ToString("yyyy-MM-dd") + "' and b." + nameof(RB_Finance.TradeDate) + "<='" + model.eAduitDate.Value.ToString("yyyy-MM-dd") + " 23:59:59' end");
            }
            else if (model.sAduitDate.HasValue)
            {
                where.Append(" AND case when b.Type=2 then b." + nameof(RB_Finance.CreateDate) + ">='" + model.sAduitDate.Value.ToString("yyyy-MM-dd") + "' else b." + nameof(RB_Finance.TradeDate) + ">='" + model.sAduitDate.Value.ToString("yyyy-MM-dd") + "' end");
            }
            else if (model.eAduitDate.HasValue)
            {
                where.Append(" AND case when b.Type=2 then b." + nameof(RB_Finance.CreateDate) + "<='" + model.eAduitDate.Value.ToString("yyyy-MM-dd") + " 23:59:59' else b." + nameof(RB_Finance.TradeDate) + "<='" + model.eAduitDate.Value.ToString("yyyy-MM-dd") + " 23:59:59' end");
            }
            if (model.RB_Group_Id > 0)
            {
                where.Append(" AND b." + nameof(RB_Finance.RB_Group_Id) + "=" + model.RB_Group_Id + "");
            }
            if (!string.IsNullOrEmpty(model.TCIDStr))
            {
                where.Append(" and E.TCID in(" + model.TCIDStr + ")");
            }
            if (model.TCID == -1)
            {
                where.Append(" and IFNULL(E.TCID,0)=0");
            }
            else if (model.TCID == -2)
            {
                where.Append(" and IFNULL(E.TCID,0) >0");//班级上的单据
                where.Append(" and IFNULL(b.OrderID,0) =0");
            }
            else if (model.TCID == -3)
            {
                where.Append(" and IFNULL(E.TCID,0) >0");//班级上的单据
                where.Append(" and (IFNULL(b.OrderID,0) =0 or (IFNULL(b.OrderID,0) >0 and b.OtherType =45))");
            }
            if (model.RB_Branch_Id >= 0)
            {
                where.Append(" AND c.School_Id =" + model.RB_Branch_Id);
            }
            if (model.OrderSource.HasValue)
            {
                where.Append(" and IFNULL(b.OrderSource,0) =" + (int)model.OrderSource);
            }
            if (model.FinanceType.HasValue && model.FinanceType > 0)
            {
                where.Append(" AND b." + nameof(RB_Finance.FinanceType) + "=" + model.FinanceType + "");
            }

            builder.AppendFormat($@"select distinct b.FrID,IFNULL(b.MatchMoney,0) as MatchMoney,IFNULL(E.TCID,0)TCID,D.Name,b.Status,b.TradeDate,b.CreateDate,b.RB_Branch_Id,a.ID,a.CostTypeId,IFNULL(b.Is_Cashier,0)Is_Cashier,a.Money,IFNULL(b.OrderID,0) OrderID,b.OrderSource,b.SourceID,IFNULL(b.PayMoney,0)PayMoney,IFNULL(b.Fee,0)Fee,b.Type 
from rb_financedetail a INNER JOIN rb_finance as b on a.FinanceId=b.FrID and a.`Status`=0 
INNER JOIN rb_costtype AS D ON D.ID=a.CostTypeId
LEFT JOIN rb_tradeway as td ON b.FrID=td.FinanceId
LEFT JOIN rb_travel_finance_relation as E ON A.FinanceId=E.FinanceId
left join {datebaseStr}.rb_class c on c.ClassId = E.TCID
where 1=1 " + where);
            return Get<RB_Finance_Extend>(builder.ToString() + " ORDER BY b.FrID DESC ").ToList();
        }

        /// <summary>
        /// 获取资金调拨 汇兑损益
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<RB_TradeWay_Extend> GetTradeWayForCapitalAllocation(RB_Finance_Extend model)
        {
            StringBuilder where = new StringBuilder();
            where.Append(" and b.Status in(1,2,5) and b.Is_Auto=0 and td.IsExChange=1 and td.Status=0 and b.Type=4 ");
            StringBuilder builder = new StringBuilder();

            if (model.sAduitDate.HasValue)
            {
                where.Append(" AND  td." + nameof(RB_TradeWay_Extend.AllotDate) + ">='" + model.sAduitDate.Value.ToString("yyyy-MM-dd") + "'");
            }
            if (model.eAduitDate.HasValue)
            {
                where.Append(" AND  td." + nameof(RB_TradeWay_Extend.AllotDate) + "<='" + model.eAduitDate.Value.ToString("yyyy-MM-dd") + " 23:59:59' ");
            }
            if (model.RB_Branch_Id >= 0)
            {
                where.Append(" AND b." + nameof(RB_Finance.RB_Branch_Id) + "=" + model.RB_Branch_Id + "");
            }
            if (model.RB_Group_Id > 0)
            {
                where.Append(" AND b." + nameof(RB_Finance.RB_Group_Id) + "=" + model.RB_Group_Id + "");
            }
            builder.AppendFormat(@"select td.*,b.RB_Branch_Id as BranchId from rb_tradeway td
left join rb_finance b on td.FinanceId=b.FrID where 1=1 " + where);
            return Get<RB_TradeWay_Extend>(builder.ToString()).ToList();
        }
    }
}