JW/ServiceLogic/EMIS.CommonLogic/ChargeManage/ChargeSituation/StudentChargePaymenServices.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.ViewModel;
using EMIS.Entities;
using Bowin.Common.Linq;
using Bowin.Common.Linq.Entity;
using Bowin.Common.Utility;
using System.Linq.Expressions;
using EMIS.ViewModel.CacheManage;
using EMIS.DataLogic.ChargeManage.ChargeSituation;
using System.Text.RegularExpressions;
using EMIS.ViewModel.ChargeManage.ChargeSituation;
using System.Data;
using EMIS.CommonLogic.StudentManage.StudentStatistics;

namespace EMIS.CommonLogic.ChargeManage.ChargeSituation
{
    public class StudentChargePaymenServices : BaseServices, IStudentChargePaymenServices
    {
        public StudentChargePaymenDAL StudentChargePaymenDAL { get; set; }
        public ChargeProjectDAL ChargeProjectDAL { get; set; }
        public IInSchoolSettingServices InSchoolSettingServices { get; set; }
        public IChargeDelayServices IChargeDelayServices { get; set; }

        /// <summary>
        /// 查询学生缴费信息列表
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="collegeID"></param>
        /// <param name="yearID"></param>
        /// <param name="standardID"></param>
        /// <param name="educationID"></param>
        /// <param name="learningformID"></param>
        /// <param name="learnSystem"></param>
        /// <param name="chargeYearID"></param>
        /// <param name="chargeProjectID"></param>
        /// <param name="inSchoolStatus"></param>
        /// <param name="isDream"></param>
        /// <param name="remark"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public IGridResultSet<StudentChargePaymenView> GetStudentChargePamenViewGrid(ConfiguretView configuretView, Guid? collegeID, int? yearID,
            int? standardID, int? educationID, int? learningformID, string learnSystem,
            int? chargeYearID, Guid? chargeProjectID, int? inSchoolStatus, int? isDream, int? chargeTagID,
            string remark, int pageIndex, int pageSize)
        {
            //学生缴费信息
            Expression<Func<EC_StudentChargePayment, bool>> expStudentChargePayment = (x => true);
            if (!string.IsNullOrEmpty(remark) && remark != "-1")
            {
                expStudentChargePayment = expStudentChargePayment.And(x => x.Remark == remark);
            }

            //应收名单
            Expression<Func<EC_StudentCharge, bool>> expStudentCharge = (x => true);
            if (chargeYearID.HasValue)
            {
                expStudentCharge = expStudentCharge.And(x => x.ChargeYear == chargeYearID);
            }
            if (chargeProjectID.HasValue)
            {
                expStudentCharge = expStudentCharge.And(x => x.ChargeProjectID == chargeProjectID);
            }
            if (chargeTagID.HasValue)
            {
                expStudentCharge = expStudentCharge.And(x => x.ChargeTag == chargeTagID);
            }

            Expression<Func<CF_Student, bool>> expStudent = (x => true);
            if (isDream.HasValue)
            {
                //expStudent = expStudent.And(x => x.IsDreamProject == (isDream == 1 ? true : false));
                if (isDream.Value == (int)CF_GeneralPurpose.IsYes)
                {
                    expStudent = expStudent.And(x => x.CF_StudentProfile.IsDreamProject == true);
                }
                if (isDream.Value == (int)CF_GeneralPurpose.IsNo)
                {
                    expStudent = expStudent.And(x => x.CF_StudentProfile.IsDreamProject != true);
                }
            }

            if (inSchoolStatus != null && inSchoolStatus > -1)
            {
                var inschoolStatusList = InSchoolSettingServices.GetInschoolStatusList(true);
                if (inSchoolStatus == 1)
                {
                    //表示在校
                    expStudent = expStudent.And(x => inschoolStatusList.Contains(x.InSchoolStatusID));
                }
                if (inSchoolStatus == 0)
                {
                    //不在校
                    expStudent = expStudent.And(x => !inschoolStatusList.Contains(x.InSchoolStatusID));
                }
            }

            var query = StudentChargePaymenDAL.GetStudentChargePaymentViewTable(expStudentChargePayment, expStudentCharge, expStudent);

            if (collegeID.HasValue)
            {
                query = query.Where(x => x.CollegeID == collegeID);
            }
            if (yearID.HasValue)
            {
                query = query.Where(x => x.GradeStr == yearID);
            }
            if (standardID.HasValue)
            {
                query = query.Where(x => x.StandardID == standardID);
            }
            if (educationID.HasValue)
            {
                query = query.Where(x => x.EducationID == educationID);
            }
            if (learningformID.HasValue)
            {
                query = query.Where(x => x.LearningformID == learningformID);
            }
            if (!string.IsNullOrEmpty(learnSystem) && learnSystem != "-1")
            {
                var LearnSystems = Convert.ToDecimal(learnSystem);
                query = query.Where(x => x.LearnSystem == LearnSystems);
            }

            //查询条件()
            if (!string.IsNullOrEmpty(configuretView.ConditionValue))
            {
                query = query.DynamicWhere(configuretView.Attribute, configuretView.Condition, configuretView.ConditionValue.Trim());
            }

            return this.GetQueryByDataRangeByCollege(query).OrderBy(x => x.StudentNo.Length)
                    .ThenBy(x => x.StudentNo).ThenBy(x => x.ChargeProjectStr)
                    .ThenByDescending(x => x.ChargeYear).ThenBy(x => x.ChargeDate)
                    .ToGridResultSet<StudentChargePaymenView>(pageIndex, pageSize);
        }

        /// <summary>
        /// 查询学生缴费信息列表
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="collegeID"></param>
        /// <param name="yearID"></param>
        /// <param name="standardID"></param>
        /// <param name="educationID"></param>
        /// <param name="learningformID"></param>
        /// <param name="learnSystem"></param>
        /// <param name="chargeYearID"></param>
        /// <param name="chargeProjectID"></param>
        /// <param name="inSchoolStatus"></param>
        /// <param name="isDream"></param>
        /// <param name="chargeTagID"></param>
        /// <param name="remark"></param>
        /// <returns></returns>
        public List<StudentChargePaymenView> GetStudentChargePaymenList(ConfiguretView configuretView, Guid? collegeID, int? yearID,
            int? standardID, int? educationID, int? learningformID, string learnSystem,
            int? chargeYearID, Guid? chargeProjectID, int? inSchoolStatus, int? isDream, int? chargeTagID,
            string remark)
        {
            //学生缴费信息
            Expression<Func<EC_StudentChargePayment, bool>> expStudentChargePayment = (x => true);
            if (!string.IsNullOrEmpty(remark) && remark != "-1")
            {
                expStudentChargePayment = expStudentChargePayment.And(x => x.Remark == remark);
            }

            //应收名单
            Expression<Func<EC_StudentCharge, bool>> expStudentCharge = (x => true);
            if (chargeYearID.HasValue)
            {
                expStudentCharge = expStudentCharge.And(x => x.ChargeYear == chargeYearID);
            }
            if (chargeProjectID.HasValue)
            {
                expStudentCharge = expStudentCharge.And(x => x.ChargeProjectID == chargeProjectID);
            }
            if (chargeTagID.HasValue)
            {
                expStudentCharge = expStudentCharge.And(x => x.ChargeTag == chargeTagID);
            }

            Expression<Func<CF_Student, bool>> expStudent = (x => true);
            if (isDream.HasValue)
            {
                //expStudent = expStudent.And(x => x.IsDreamProject == (isDream == 1 ? true : false));
                if (isDream.Value == (int)CF_GeneralPurpose.IsYes)
                {
                    expStudent = expStudent.And(x => x.CF_StudentProfile.IsDreamProject == true);
                }
                if (isDream.Value == (int)CF_GeneralPurpose.IsNo)
                {
                    expStudent = expStudent.And(x => x.CF_StudentProfile.IsDreamProject != true);
                }
            }

            if (inSchoolStatus != null && inSchoolStatus > -1)
            {
                var inschoolStatusList = InSchoolSettingServices.GetInschoolStatusList(true);
                if (inSchoolStatus == 1)
                {
                    //表示在校
                    expStudent = expStudent.And(x => inschoolStatusList.Contains(x.InSchoolStatusID));
                }
                if (inSchoolStatus == 0)
                {
                    //不在校
                    expStudent = expStudent.And(x => !inschoolStatusList.Contains(x.InSchoolStatusID));
                }
            }

            var query = StudentChargePaymenDAL.GetStudentChargePaymentViewTable(expStudentChargePayment, expStudentCharge, expStudent);

            if (collegeID.HasValue)
            {
                query = query.Where(x => x.CollegeID == collegeID);
            }
            if (yearID.HasValue)
            {
                query = query.Where(x => x.GradeStr == yearID);
            }
            if (standardID.HasValue)
            {
                query = query.Where(x => x.StandardID == standardID);
            }
            if (educationID.HasValue)
            {
                query = query.Where(x => x.EducationID == educationID);
            }
            if (learningformID.HasValue)
            {
                query = query.Where(x => x.LearningformID == learningformID);
            }
            if (!string.IsNullOrEmpty(learnSystem) && learnSystem != "-1")
            {
                var LearnSystems = Convert.ToDecimal(learnSystem);
                query = query.Where(x => x.LearnSystem == LearnSystems);
            }

            //查询条件()
            if (!string.IsNullOrEmpty(configuretView.ConditionValue))
            {
                query = query.DynamicWhere(configuretView.Attribute, configuretView.Condition, configuretView.ConditionValue.Trim());
            }

            return this.GetQueryByDataRangeByCollege(query).OrderBy(x => x.StudentNo.Length)
                    .ThenBy(x => x.StudentNo).ThenBy(x => x.ChargeProjectStr)
                    .ThenByDescending(x => x.ChargeYear).ThenBy(x => x.ChargeDate)
                    .ToList();
        }

        /// <summary>
        /// 学生缴费页面缴费金额统计
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="collegeID"></param>
        /// <param name="yearID"></param>
        /// <param name="standardID"></param>
        /// <param name="educationID"></param>
        /// <param name="learningformID"></param>
        /// <param name="learnSystem"></param>
        /// <param name="chargeYearID"></param>
        /// <param name="chargeProjectID"></param>
        /// <param name="inSchoolStatus"></param>
        /// <param name="isDream"></param>
        /// <param name="chargeTagID"></param>
        /// <param name="remark"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public decimal? GetAmountCount(ConfiguretView configuretView, Guid? collegeID, int? yearID,
            int? standardID, int? educationID, int? learningformID, string learnSystem,
            int? chargeYearID, Guid? chargeProjectID, int? inSchoolStatus, int? isDream, int? chargeTagID,
            string remark, int pageIndex, int pageSize)
        {
            //学生缴费信息
            Expression<Func<EC_StudentChargePayment, bool>> expStudentChargePayment = (x => true);
            if (!string.IsNullOrEmpty(remark) && remark != "-1")
            {
                expStudentChargePayment = expStudentChargePayment.And(x => x.Remark == remark);
            }

            //应收名单
            Expression<Func<EC_StudentCharge, bool>> expStudentCharge = (x => true);
            if (chargeYearID.HasValue)
            {
                expStudentCharge = expStudentCharge.And(x => x.ChargeYear == chargeYearID);
            }
            if (chargeProjectID.HasValue)
            {
                expStudentCharge = expStudentCharge.And(x => x.ChargeProjectID == chargeProjectID);
            }
            if (chargeTagID.HasValue)
            {
                expStudentCharge = expStudentCharge.And(x => x.ChargeTag == chargeTagID);
            }

            Expression<Func<CF_Student, bool>> expStudent = (x => true);
            if (isDream.HasValue)
            {
                //expStudent = expStudent.And(x => x.IsDreamProject == (isDream == 1 ? true : false));
                if (isDream.Value == (int)CF_GeneralPurpose.IsYes)
                {
                    expStudent = expStudent.And(x => x.CF_StudentProfile.IsDreamProject == true);
                }
                if (isDream.Value == (int)CF_GeneralPurpose.IsNo)
                {
                    expStudent = expStudent.And(x => x.CF_StudentProfile.IsDreamProject != true);
                }
            }

            if (inSchoolStatus != null && inSchoolStatus > -1)
            {
                var inschoolStatusList = InSchoolSettingServices.GetInschoolStatusList(true);
                if (inSchoolStatus == 1)
                {
                    //表示在校
                    expStudent = expStudent.And(x => inschoolStatusList.Contains(x.InSchoolStatusID));
                }
                if (inSchoolStatus == 0)
                {
                    //不在校
                    expStudent = expStudent.And(x => !inschoolStatusList.Contains(x.InSchoolStatusID));
                }
            }

            var query = StudentChargePaymenDAL.GetStudentChargePaymentViewTable(expStudentChargePayment, expStudentCharge, expStudent);

            if (collegeID.HasValue)
            {
                query = query.Where(x => x.CollegeID == collegeID);
            }
            if (yearID.HasValue)
            {
                query = query.Where(x => x.GradeStr == yearID);
            }
            if (standardID.HasValue)
            {
                query = query.Where(x => x.StandardID == standardID);
            }
            if (educationID.HasValue)
            {
                query = query.Where(x => x.EducationID == educationID);
            }
            if (learningformID.HasValue)
            {
                query = query.Where(x => x.LearningformID == learningformID);
            }
            if (!string.IsNullOrEmpty(learnSystem) && learnSystem != "-1")
            {
                var LearnSystems = Convert.ToDecimal(learnSystem);
                query = query.Where(x => x.LearnSystem == LearnSystems);
            }

            //查询条件()
            if (!string.IsNullOrEmpty(configuretView.ConditionValue))
            {
                query = query.DynamicWhere(configuretView.Attribute, configuretView.Condition, configuretView.ConditionValue.Trim());
            }

            var studentChargePaymenViewList = GetQueryByDataRangeByCollege(query)
                    .ToList<StudentChargePaymenView>();

            decimal? amountCount = 0;

            foreach (var studentChargePaymenView in studentChargePaymenViewList)
            {
                amountCount += studentChargePaymenView.Amount;
            }

            return amountCount;
        }

        /// <summary>
        /// 查询对应的学生缴费信息实体
        /// </summary>
        /// <param name="studentChargePaymentID"></param>
        /// <returns></returns>
        public EC_StudentChargePayment GetStudentChargePaymen(Guid? studentChargePaymentID)
        {
            //查询条件
            Expression<Func<EC_StudentChargePayment, bool>> expression = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            expression = (x => x.StudentChargePaymentID == studentChargePaymentID);
            return StudentChargePaymenDAL.StudentChargePaymentRepository.GetSingle(expression);
        }

        /// <summary>
        /// 查询对应的学生缴费信息View
        /// </summary>
        /// <param name="studentChargePaymentID"></param>
        /// <returns></returns>
        public StudentChargePaymenView GetStudentChargePaymenView(Guid? studentChargePaymentID)
        {
            //查询条件
            Expression<Func<EC_StudentChargePayment, bool>> expStudentChargePayment = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            expStudentChargePayment = (x => x.StudentChargePaymentID == studentChargePaymentID);

            var query = StudentChargePaymenDAL.GetStudentChargePaymentViewTable(expStudentChargePayment, x => true, x => true);
            
            return query.SingleOrDefault();
        }

        /// <summary>
        /// 新增、修改
        /// </summary>
        /// <param name="studentChargePaymenView"></param>
        public void StudentChargePaymenEdit(StudentChargePaymenView studentChargePaymenView)
        {
            try
            {
                //查询对应的应收名单信息View（统计已缴金额）
                var studentChargeView = StudentChargePaymenDAL.GetStudentChargeViewQueryAble(x => x.UserID == studentChargePaymenView.UserID
                        && x.ChargeYear == studentChargePaymenView.ChargeYear
                        && x.ChargeProjectID == studentChargePaymenView.ChargeProjectID)
                        .SingleOrDefault();

                //验证缴费金额在实收金额范围内
                decimal? amountVerify = 0;

                //验证对应的应收名单信息
                if (studentChargeView != null)
                {
                    //查询数据库进行验证(需排除自己)
                    var studentChargePaymentVerification = StudentChargePaymenDAL.StudentChargePaymentRepository
                            .GetList(x => x.StudentChargePaymentID != studentChargePaymenView.StudentChargePaymentID
                                && x.StudentChargeID == studentChargeView.StudentChargeID
                                && x.ChargeDate == studentChargePaymenView.ChargeDate)
                            .SingleOrDefault();

                    if (studentChargePaymentVerification == null)
                    {
                        //数据有误验证
                        if (studentChargePaymenView.StudentChargePaymentID != Guid.Empty)
                        {
                            //表示修改
                            var studentChargePayment = StudentChargePaymenDAL.StudentChargePaymentRepository
                                    .GetList(x => x.StudentChargePaymentID == studentChargePaymenView.StudentChargePaymentID)
                                    .SingleOrDefault();

                            if (studentChargePayment == null)
                            {
                                throw new Exception("数据有误，请核查");
                            }
                            else
                            {
                                //缴费金额在实收金额范围内
                                amountVerify = studentChargeView.PaidAmount - studentChargePayment.Amount + studentChargePaymenView.Amount;
                                if (studentChargeView.ActualAmount >= amountVerify)
                                {
                                    studentChargePayment.Amount = studentChargePaymenView.Amount;
                                    studentChargePayment.ChargeDate = studentChargePaymenView.ChargeDate;
                                    SetModifyStatus(studentChargePayment);
                                }
                                else
                                {
                                    throw new Exception("缴费金额大于实收金额，请重新输入");
                                }
                            }
                        }
                        else
                        {
                            //表示新增
                            //缴费金额在实收金额范围内
                            amountVerify = studentChargeView.PaidAmount + studentChargePaymenView.Amount;
                            if (studentChargeView.ActualAmount >= amountVerify)
                            {
                                EC_StudentChargePayment studentChargePayment = new EC_StudentChargePayment();
                                studentChargePayment.StudentChargePaymentID = Guid.NewGuid();
                                studentChargePayment.StudentChargeID = studentChargeView.StudentChargeID;
                                studentChargePayment.Amount = studentChargePaymenView.Amount;
                                studentChargePayment.ChargeDate = studentChargePaymenView.ChargeDate;
                                studentChargePayment.Remark = studentChargePaymenView.Remark;
                                SetNewStatus(studentChargePayment, (int)SYS_STATUS.USABLE);
                                UnitOfWork.Add(studentChargePayment);
                            }
                            else
                            {
                                throw new Exception("缴费金额大于实收金额，请重新输入");
                            }
                        }
                    }
                    else
                    {
                        throw new Exception("已存在相同的学生缴费信息");
                    }
                }
                else
                {
                    throw new Exception("对应的应收名单不存在");
                }

                //事务提交
                UnitOfWork.Commit();

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 查询应收名单对应的缴费信息（根据应收名单业务主键）
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="chargeYearID"></param>
        /// <param name="chargeProjectID"></param>
        /// <returns></returns>
        public StudentChargeView QueryStudentChargePaymenView(Guid? userID, int? chargeYearID, Guid? chargeProjectID)
        {
            try
            {
                //应收名单
                Expression<Func<EC_StudentCharge, bool>> expStudentCharge = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
                if (userID.HasValue)
                {
                    expStudentCharge = expStudentCharge.And(x => x.UserID == userID);
                }
                if (chargeYearID.HasValue)
                {
                    expStudentCharge = expStudentCharge.And(x => x.ChargeYear == chargeYearID);
                }
                if (chargeProjectID.HasValue)
                {
                    expStudentCharge = expStudentCharge.And(x => x.ChargeProjectID == chargeProjectID);
                }

                var studentChargeView = StudentChargePaymenDAL.GetStudentChargeViewQueryAble(expStudentCharge)
                        .SingleOrDefault();

                return studentChargeView;
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 批量修改（重写）
        /// </summary>
        /// <param name="studentChargePaymentIDs"></param>
        /// <param name="amount"></param>
        /// <returns></returns>
        public string BatchModify(string studentChargePaymentIDs, decimal? amount)
        {
            try
            {
                List<Guid?> list = studentChargePaymentIDs.Split(',').Where(x => !string.IsNullOrEmpty(x))
                    .Select(x => (Guid?)new Guid(x)).ToList();

                //对应的学生缴费信息List
                var studentChargePaymentList = StudentChargePaymenDAL.StudentChargePaymentRepository
                        .GetList(x => list.Contains(x.StudentChargePaymentID)).ToList();

                //对应的应收名单IDList
                var studentChargeIDList = studentChargePaymentList.Select(x => x.StudentChargeID).ToList();

                //对应的应收名单信息List（需统计对应的已缴金额）
                var studentChargeViewList = StudentChargePaymenDAL.GetStudentChargeViewQueryAble(x => studentChargeIDList.Contains(x.StudentChargeID)).ToList();

                int success = 0; //成功
                int fail = 0; //失败
                string tipMessage = null; //提示消息

                List<EC_StudentChargePayment> studentChargePaymentUpList = new List<EC_StudentChargePayment>();

                foreach (var studentChargePayment in studentChargePaymentList)
                {
                    //查询对应的应收名单信息（需统计对应的已缴金额）
                    var studentCharge = studentChargeViewList.Where(x => x.StudentChargeID == studentChargePayment.StudentChargeID)
                            .SingleOrDefault();

                    //注：由于存在可多次缴费的情况，那么实收金额应大于等于所有的缴费金额之和
                    //计算公式应为：实收金额 >= 所有的缴费金额之和 - 更新的缴费金额 + 修改的金额
                    if (studentCharge.ActualAmount >= studentCharge.PaidAmount - studentChargePayment.Amount + amount)
                    {
                        //表示可修改
                        studentChargePayment.Amount = amount;
                        SetModifyStatus(studentChargePayment);
                        studentChargePaymentUpList.Add(studentChargePayment);

                        success++;
                    }
                    else
                    {
                        //表示不可修改
                        fail++;
                    }
                }

                //批量统一提交更新
                if (studentChargePaymentUpList != null && studentChargePaymentUpList.Count() > 0)
                {
                    UnitOfWork.Commit();
                }

                if (success > 0 && fail <= 0)
                {
                    tipMessage = success + "条";
                }
                else
                {
                    tipMessage = success + "条，" + fail + "条失败，原因：缴费金额大于实收金额，请检查";
                }

                return tipMessage;
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="studentChargePaymenIDs"></param>
        /// <returns></returns>
        public bool StudentChargePaymenDelete(List<Guid> studentChargePaymenIDs)
        {
            try
            {
                UnitOfWork.Delete<EC_StudentChargePayment>(x => studentChargePaymenIDs.Contains(x.StudentChargePaymentID));
                UnitOfWork.Commit();
                return true;
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 获取学生缴费备注信息
        /// </summary>
        /// <param name="configuretView"></param>
        /// <param name="Remark"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public IGridResultSet<StudentChargePaymenView> GetRemarkViewGrid(ConfiguretView configuretView, string Remark,
                int pageIndex, int pageSize)
        {
            Expression<Func<EC_StudentChargePayment, bool>> exp = (x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE);
            if (!string.IsNullOrEmpty(Remark) && Remark != "-1")
            {
                exp = exp.And(x => x.Remark == Remark);
            }
            if (!string.IsNullOrEmpty(configuretView.ConditionValue))
            {
                return StudentChargePaymenDAL.GetRemarkViewTable(exp).DynamicWhere(configuretView.Attribute, configuretView.Condition,
                        configuretView.ConditionValue.Trim()).OrderBy(x => x.Remark)
                        .ToGridResultSet<StudentChargePaymenView>(pageIndex, pageSize);
            }

            return StudentChargePaymenDAL.GetRemarkViewTable(exp).OrderBy(x => x.Remark)
                        .ToGridResultSet<StudentChargePaymenView>(pageIndex, pageSize);
        }

        /// <summary>
        /// Excel导入
        /// 同一应收名单，同一缴费日期，只有一条记录且判断缴费金额在实收金额范围内
        /// 由于存在可多次缴费的情况，那么需考虑实收金额应大于等于所有的缴费金额之和的情况
        /// </summary>
        /// <param name="cellheader"></param>
        /// <param name="inCount"></param>
        /// <param name="upCount"></param>
        /// <param name="errdataList"></param>
        /// <param name="errCount"></param>
        /// <param name="sourcePhysicalPath"></param>
        public void StudentChargePaymenImport(Dictionary<string, string> cellheader, out int? inCount, out int? upCount,
                out List<StudentChargePaymenView> errdataList, out int? errCount, string sourcePhysicalPath)
        {
            try
            {
                StringBuilder errorMsg = new StringBuilder(); // 错误信息
                List<StudentChargePaymenView> errList = new List<StudentChargePaymenView>();

                // 1.1解析文件，存放到一个List集合里
                cellheader.Remove("ErrorMessage");//移除“未导入原因”列(ErrorMessage)

                List<StudentChargePaymenView> enlist = NpoiExcelHelper
                        .ExcelToEntityList<StudentChargePaymenView>(cellheader, sourcePhysicalPath, out errorMsg, out errList);

                cellheader.Add("ErrorMessage", "未导入原因");

                //对List集合进行有效性校验
                if (enlist.Count() <= 0)
                {
                    throw new Exception("Excel文件数据为空，请检查。");
                }

                //Regex reg = null; //正则表达式

                DateTime result; //用于返回判断日期字段格式

                decimal isDecimal; //用于返回判断decimal列格式

                inCount = 0; //导入个数
                upCount = 0; //更新个数
                errCount = 0; //失败个数

                string errorMsgStr = ""; //错误信息

                //学生缴费insert实体List
                List<EC_StudentChargePayment> studentChargePaymentInList = new List<EC_StudentChargePayment>();
                //学生缴费update实体List
                List<EC_StudentChargePayment> studentChargePaymentUpList = new List<EC_StudentChargePayment>();

                //移除InList
                List<EC_StudentChargePayment> removeInList = new List<EC_StudentChargePayment>();
                //移除UpList
                List<EC_StudentChargePayment> removeUpList = new List<EC_StudentChargePayment>();

                //将循环中相关数据库查询统一查询出来进行匹配(尽量避免在循环中进行数据库查询)

                //学号
                var studentNoList = enlist.Where(x => !string.IsNullOrEmpty(x.StudentNo))
                        .Select(x => x.StudentNo).ToList();

                //查询对应的学生信息List
                var studentList = StudentChargePaymenDAL.StudentRepository
                        .GetList(x => studentNoList.Contains(x.Sys_User.LoginID), (x => x.Sys_User)).ToList();

                //对应的学生信息userIDList
                var userIDList = studentList.Select(x => x.UserID).ToList();

                //收费项目
                var chargeProjectNameList = enlist.Where(x => !string.IsNullOrEmpty(x.ChargeProjectStr))
                        .Select(x => x.ChargeProjectStr).ToList();

                //查询对应的收费项目List
                var chargeProjectList = StudentChargePaymenDAL.ChargeProjectRepository
                        .GetList(x => chargeProjectNameList.Contains(x.Name)).ToList();

                //缴费学年
                var chargeYearList = enlist.Where(x => !string.IsNullOrEmpty(x.ChargeYearStr))
                        .Select(x => x.ChargeYearStr).ToList();

                //学年
                var schoolYearList = IdNameExt.GetDictionaryItem(DictionaryItem.CF_Year).ToList();

                //查询应收名单信息ViewList(考虑到数据量的问题，暂时先查询所有)
                var studentChargeViewList = StudentChargePaymenDAL
                        .GetStudentChargeViewQueryAble(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE).ToList();
                //查询相应的匹配信息studentChargeViewList
                studentChargeViewList = studentChargeViewList.Where(x => userIDList.Contains(x.UserID.Value)).ToList();

                //对应的应收名单studentChargeIDList
                var studentChargeIDList = studentChargeViewList.Select(x => x.StudentChargeID).ToList();

                //学生缴费信息List(根据应收名单信息)
                var studentChargePaymentList = StudentChargePaymenDAL.StudentChargePaymentRepository
                        .GetList(x => x.RecordStatus > (int)SYS_STATUS.UNUSABLE).ToList();
                //查询相应的匹配信息studentChargePaymentList
                studentChargePaymentList = studentChargePaymentList
                        .Where(x => studentChargeIDList.Contains(x.StudentChargeID.Value)).ToList();


                //循环检测数据列,对各数据列进行验证（必填、字典项验证、数据格式等）
                for (int i = 0; i < enlist.Count; i++)
                {
                    StudentChargePaymenView en = enlist[i]; //Excel表数据视图

                    //学生缴费实体
                    EC_StudentChargePayment studentChargePayment = new EC_StudentChargePayment();

                    //学号
                    if (string.IsNullOrEmpty(en.StudentNo))
                    {
                        errCount++;
                        errorMsgStr = "学号不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        var student = studentList.Where(x => x.Sys_User.LoginID == en.StudentNo.Trim()).SingleOrDefault();

                        if (student == null)
                        {
                            errCount++;
                            errorMsgStr = "学号不存在，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            //userID
                            //studentChargePayment.UserID = student.UserID;
                            en.UserID = student.UserID;
                        }
                    }
                    //收费项目
                    if (string.IsNullOrEmpty(en.ChargeProjectStr))
                    {
                        errCount++;
                        errorMsgStr = "收费项目不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        var chargeProject = chargeProjectList.Where(x => x.Name == en.ChargeProjectStr.Trim()).SingleOrDefault();

                        if (chargeProject == null)
                        {
                            errCount++;
                            errorMsgStr = "收费项目不存在，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            //收费项目ID
                            //studentChargePayment.ChargeProjectID = chargeProject.ChargeProjectID;
                            en.ChargeProjectID = chargeProject.ChargeProjectID;
                        }
                    }
                    //缴费学年
                    if (string.IsNullOrEmpty(en.ChargeYearStr))
                    {
                        errCount++;
                        errorMsgStr = "缴费学年不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        var year = schoolYearList.Where(x => x.Name == en.ChargeYearStr.Trim()).SingleOrDefault();

                        if (year == null)
                        {
                            errCount++;
                            errorMsgStr = "缴费学年不存在，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            //缴费学年
                            //studentChargePayment.ChargeYear = year.Value;
                            en.ChargeYear = year.Value;
                        }
                    }
                    //缴费日期
                    if (string.IsNullOrEmpty(en.ChargeDateStr))
                    {
                        errCount++;
                        errorMsgStr = "缴费日期不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        if (!DateTime.TryParse(en.ChargeDateStr.Trim(), out result))
                        {
                            errCount++;
                            errorMsgStr = "缴费日期格式不正确，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            //缴费日期
                            studentChargePayment.ChargeDate = Convert.ToDateTime(en.ChargeDateStr.Trim());
                        }
                    }
                    //缴费金额
                    if (string.IsNullOrEmpty(en.AmountStr))
                    {
                        errCount++;
                        errorMsgStr = "缴费金额不能为空";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        if (!Decimal.TryParse(en.AmountStr.Trim(), out isDecimal))
                        {
                            errCount++;
                            errorMsgStr = "缴费金额格式不正确，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                        else
                        {
                            //缴费金额
                            if (isDecimal <= 0)
                            {
                                errCount++;
                                errorMsgStr = "缴费金额不能小于等于0，请检查";
                                en.ErrorMessage = errorMsgStr;
                                errList.Add(en);
                                errorMsg.AppendLine(errorMsgStr);
                                continue;
                            }
                            else
                            {
                                studentChargePayment.Amount = Convert.ToDecimal(en.AmountStr.Trim());
                            }
                        }
                    }

                    //查询对应的应收名单View（统计对应的已缴金额）
                    var studentChargeView = studentChargeViewList.Where(x => x.UserID == en.UserID 
                            && x.ChargeProjectID == en.ChargeProjectID 
                            && x.ChargeYear == en.ChargeYear
                            ).SingleOrDefault();

                    if (studentChargeView == null)
                    {
                        errCount++;
                        errorMsgStr = "对应的应收名单不存在，请检查";
                        en.ErrorMessage = errorMsgStr;
                        errList.Add(en);
                        errorMsg.AppendLine(errorMsgStr);
                        continue;
                    }
                    else
                    {
                        //应收名单ID
                        en.StudentChargeID = studentChargeView.StudentChargeID;
                        //实收金额("减免后的实收金额)
                        en.ActualAmount = studentChargeView.ActualAmount;
                        //已缴金额
                        en.PaidAmount = studentChargeView.PaidAmount;
                    }

                    //备注
                    studentChargePayment.Remark = en.Remark;

                    ////Excel表重复性验证（注：当数据表中没有此记录，但是Excel中有重复数据时的去掉）
                    //for (int j = i + 1; j < enlist.Count; j++)
                    //{
                    //    StudentChargePaymenView enA = enlist[j];

                    //    //根据Excel表中的业务主键进行去重（应收名单、缴费日期唯一）
                    //    if (en.StudentNo == enA.StudentNo
                    //            && en.ChargeProjectStr == enA.ChargeProjectStr
                    //            && en.ChargeYearStr == enA.ChargeYearStr
                    //            && en.ChargeDateStr == enA.ChargeDateStr)
                    //    {
                    //        //用于标识Excel表中的重复记录（由于是批量进行插入数据表）

                    //    }
                    //}


                    //注：由于存在可多次缴费的情况，那么实收金额应大于等于所有的缴费金额之和
                    //计算公式应分为两种情况：
                    //1、新增时，实收金额 >= 所有的缴费金额之和 + 缴费金额
                    //2、更新时：实收金额 >= 所有的缴费金额之和 - 更新的缴费金额 + 缴费金额

                    //数据表重复性验证(应收名单、缴费日期唯一)
                    var payVerification = studentChargePaymentList
                            .Where(x => x.StudentChargeID == en.StudentChargeID 
                                && x.ChargeDate == studentChargePayment.ChargeDate).FirstOrDefault();
                    if (payVerification == null)
                    {
                        if (!removeInList.Any(x => x.StudentChargeID == en.StudentChargeID
                                && x.ChargeDate == studentChargePayment.ChargeDate))
                        {
                            //新增
                            if (!studentChargePaymentInList.Any(x => x.StudentChargeID == en.StudentChargeID
                                    && x.ChargeDate == studentChargePayment.ChargeDate))
                            {
                                if (!removeInList.Any(x => x.StudentChargeID == en.StudentChargeID))
                                {
                                    //多次缴费情况处理
                                    if (!studentChargePaymentInList.Any(x => x.StudentChargeID == en.StudentChargeID))
                                    {
                                        //新增时，实收金额 >= 所有的缴费金额之和 + 缴费金额（一次性缴费）
                                        if (studentChargeView.ActualAmount >= studentChargeView.PaidAmount + studentChargePayment.Amount)
                                        {
                                            studentChargePayment.StudentChargePaymentID = Guid.NewGuid();
                                            studentChargePayment.StudentChargeID = en.StudentChargeID;

                                            SetNewStatus(studentChargePayment);
                                            studentChargePaymentInList.Add(studentChargePayment);
                                            inCount++;
                                        }
                                        else
                                        {
                                            errCount++;
                                            errorMsgStr = "导入失败，缴费金额大于实收金额";
                                            en.ErrorMessage = errorMsgStr;
                                            errList.Add(en);
                                            errorMsg.AppendLine(errorMsgStr);
                                            continue;
                                        }
                                    }
                                    else
                                    {
                                        //分组统计查询对应的应收名单缴费金额
                                        var scpInGroup = studentChargePaymentInList.GroupBy(x => x.StudentChargeID)
                                                .Select(x => new
                                                {
                                                    key = x.Key,
                                                    PaidInAmount = x.Sum(s => s.Amount).Value
                                                }).Where(w => w.key == en.StudentChargeID).SingleOrDefault();

                                        //新增时，实收金额 >= 所有的缴费金额之和 + 缴费金额之和（多次重复缴费）
                                        if (studentChargeView.ActualAmount >= studentChargeView.PaidAmount + scpInGroup.PaidInAmount
                                                + studentChargePayment.Amount)
                                        {
                                            studentChargePayment.StudentChargePaymentID = Guid.NewGuid();
                                            studentChargePayment.StudentChargeID = en.StudentChargeID;

                                            SetNewStatus(studentChargePayment);
                                            studentChargePaymentInList.Add(studentChargePayment);
                                            inCount++;
                                        }
                                        else
                                        {
                                            //新增(Excel中有重复且缴费金额之和超出实收金额范围
                                            //抛出到失败数据文件中进行处理，不进行新增)
                                            var scpInList = studentChargePaymentInList
                                            .Where(x => x.StudentChargeID == en.StudentChargeID).ToList();

                                            foreach (var scpIn in scpInList)
                                            {
                                                errCount++;
                                                errorMsgStr = "多次缴费金额之和大于实收金额，请检查";
                                                en.ErrorMessage = errorMsgStr;
                                                errList.Add(new StudentChargePaymenView()
                                                {
                                                    StudentNo = en.StudentNo,
                                                    ChargeProjectStr = en.ChargeProjectStr,
                                                    ChargeYearStr = en.ChargeYearStr,
                                                    ChargeDateStr = scpIn.ChargeDate.ToString(),
                                                    AmountStr = scpIn.Amount.ToString(),
                                                    Remark = scpIn.Remark,
                                                    ErrorMessage = en.ErrorMessage
                                                });
                                                errorMsg.AppendLine(errorMsgStr);

                                                studentChargePaymentInList.Remove(scpIn);
                                                removeInList.Add(scpIn);
                                                inCount--;
                                            }

                                            errCount++;
                                            errorMsgStr = "多次缴费金额之和大于实收金额，请检查";
                                            en.ErrorMessage = errorMsgStr;
                                            errList.Add(en);
                                            errorMsg.AppendLine(errorMsgStr);
                                            continue;
                                        }
                                    }
                                }
                                else
                                {
                                    errCount++;
                                    errorMsgStr = "多次缴费金额之和大于实收金额，请检查";
                                    en.ErrorMessage = errorMsgStr;
                                    errList.Add(en);
                                    errorMsg.AppendLine(errorMsgStr);
                                    continue;
                                }
                            }
                            else
                            {
                                //Excel表重复性验证
                                //（注：当数据表中没有此记录，但是Excel中有重复数据，抛出到失败数据文件中进行处理）
                                var scpInList = studentChargePaymentInList
                                        .Where(x => x.StudentChargeID == en.StudentChargeID
                                                && x.ChargeDate == studentChargePayment.ChargeDate).ToList();

                                foreach (var scpIn in scpInList)
                                {
                                    errCount++;
                                    errorMsgStr = "数据重复，请检查";
                                    en.ErrorMessage = errorMsgStr;
                                    errList.Add(new StudentChargePaymenView()
                                    {
                                        StudentNo = en.StudentNo,
                                        ChargeProjectStr = en.ChargeProjectStr,
                                        ChargeYearStr = en.ChargeYearStr,
                                        ChargeDateStr = en.ChargeDateStr,
                                        AmountStr = scpIn.Amount.ToString(),
                                        Remark = scpIn.Remark,
                                        ErrorMessage = en.ErrorMessage
                                    });
                                    errorMsg.AppendLine(errorMsgStr);

                                    studentChargePaymentInList.Remove(scpIn);
                                    removeInList.Add(scpIn);
                                    inCount--;
                                }

                                errCount++;
                                errorMsgStr = "数据重复，请检查";
                                en.ErrorMessage = errorMsgStr;
                                errList.Add(en);
                                errorMsg.AppendLine(errorMsgStr);
                                continue;
                            }
                        }
                        else
                        {
                            errCount++;
                            errorMsgStr = "数据重复，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                    }
                    else
                    {
                        //更新(Excel中有重复时，以数据重复抛出到失败数据文件中进行处理，不进行更新)
                        if (!removeUpList.Any(x => x.StudentChargeID == en.StudentChargeID
                                && x.ChargeDate == studentChargePayment.ChargeDate))
                        {
                            if (!studentChargePaymentUpList.Any(x => x.StudentChargeID == en.StudentChargeID
                                && x.ChargeDate == studentChargePayment.ChargeDate))
                            {
                                //更新时：实收金额 >= 所有的缴费金额之和 - 更新的缴费金额 + 缴费金额（缴费金额在实收金额范围内）
                                if (studentChargeView.ActualAmount >=
                                        studentChargeView.PaidAmount - payVerification.Amount + studentChargePayment.Amount)
                                {
                                    //更新(Excel中无重复时，以最后一条记录的更新为准)
                                    payVerification.Amount = studentChargePayment.Amount;
                                    payVerification.Remark = studentChargePayment.Remark;

                                    SetModifyStatus(payVerification);
                                    studentChargePaymentUpList.Add(payVerification);

                                    upCount++;
                                }
                                else
                                {
                                    errCount++;
                                    errorMsgStr = "导入更新失败，缴费金额大于实收金额";
                                    en.ErrorMessage = errorMsgStr;
                                    errList.Add(en);
                                    errorMsg.AppendLine(errorMsgStr);
                                    continue;
                                }
                            }
                            else
                            {
                                var scpUpList = studentChargePaymentUpList
                                        .Where(x => x.StudentChargeID == en.StudentChargeID
                                                && x.ChargeDate == studentChargePayment.ChargeDate).ToList();

                                foreach (var scpUp in scpUpList)
                                {
                                    errCount++;
                                    errorMsgStr = "数据重复，请检查";
                                    en.ErrorMessage = errorMsgStr;
                                    errList.Add(new StudentChargePaymenView()
                                    {
                                        StudentNo = en.StudentNo,
                                        ChargeProjectStr = en.ChargeProjectStr,
                                        ChargeYearStr = en.ChargeYearStr,
                                        ChargeDateStr = en.ChargeDateStr,
                                        AmountStr = scpUp.Amount.ToString(),
                                        Remark = scpUp.Remark,
                                        ErrorMessage = en.ErrorMessage
                                    });
                                    errorMsg.AppendLine(errorMsgStr);

                                    studentChargePaymentUpList.Remove(scpUp);
                                    removeUpList.Add(scpUp);
                                    upCount--;
                                }

                                errCount++;
                                errorMsgStr = "数据重复，请检查";
                                en.ErrorMessage = errorMsgStr;
                                errList.Add(en);
                                errorMsg.AppendLine(errorMsgStr);
                                continue;
                            }
                        }
                        else
                        {
                            errCount++;
                            errorMsgStr = "数据重复，请检查";
                            en.ErrorMessage = errorMsgStr;
                            errList.Add(en);
                            errorMsg.AppendLine(errorMsgStr);
                            continue;
                        }
                    }
                    
                }

                //移除In
                if (removeInList != null && removeInList.Count() > 0)
                {
                    foreach (var removeIn in removeInList)
                    {
                        studentChargePaymentInList.Remove(removeIn);
                    }
                }

                //移除Up
                if (removeUpList != null && removeUpList.Count() > 0)
                {
                    foreach (var removeUp in removeUpList)
                    {
                        studentChargePaymentUpList.Remove(removeUp);
                    }
                }

                UnitOfWork.BulkInsert(studentChargePaymentInList); //批量插入

                //批量统一提交更新
                if (studentChargePaymentUpList != null && studentChargePaymentUpList.Count() > 0)
                {
                    UnitOfWork.BatchUpdate(studentChargePaymentUpList); //批量更新
                }

                errdataList = errList.ToList(); //错误列表List

            }
            catch (Exception ex)
            {
                //目前会出现，由于错误信息字符太长，无法抛出弹出框的问题
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 验证学生缴费信息(验证逻辑：同一应收名单，同一缴费日期，只有一条记录且判断缴费金额在实收金额范围内)
        /// </summary>
        /// <param name="studentChargePaymentID"></param>
        /// <param name="userID"></param>
        /// <param name="chargeYearID"></param>
        /// <param name="chargeProjectID"></param>
        /// <param name="amount"></param>
        /// <param name="chargeDate"></param>
        /// <returns></returns>
        public string GetVerification(Guid? studentChargePaymentID, Guid? userID, int? chargeYearID, 
                Guid? chargeProjectID, decimal? amount, DateTime? chargeDate)
        {
            try
            {
                //查询对应的应收名单信息View（统计已缴金额）
                var studentChargeView = StudentChargePaymenDAL.GetStudentChargeViewQueryAble(x => x.UserID == userID
                        && x.ChargeYear == chargeYearID
                        && x.ChargeProjectID == chargeProjectID)
                        .SingleOrDefault();

                //验证缴费金额在实收金额范围内
                decimal? amountVerify = 0;

                //验证对应的应收名单信息
                if (studentChargeView != null)
                {
                    //查询数据库进行验证(需排除自己)
                    var studentChargePayment = StudentChargePaymenDAL.StudentChargePaymentRepository
                            .GetList(x => x.StudentChargePaymentID != studentChargePaymentID
                                && x.StudentChargeID == studentChargeView.StudentChargeID
                                && x.ChargeDate == chargeDate)
                            .SingleOrDefault();

                    if (studentChargePayment == null)
                    {
                        //数据有误验证
                        if (studentChargePaymentID.HasValue && studentChargePaymentID != Guid.Empty)
                        {
                            //表示修改
                            studentChargePayment = StudentChargePaymenDAL.StudentChargePaymentRepository
                                    .GetList(x => x.StudentChargePaymentID == studentChargePaymentID)
                                    .SingleOrDefault();

                            if (studentChargePayment == null)
                            {
                                return "数据有误，请核查";
                            }
                            else
                            {
                                amountVerify = studentChargeView.PaidAmount - studentChargePayment.Amount + amount;
                                if (studentChargeView.ActualAmount >= amountVerify)
                                {
                                    return "成功";
                                }
                                else
                                {
                                    return "缴费金额大于实收金额，请重新输入";
                                }
                            }
                        }
                        else
                        {
                            amountVerify = studentChargeView.PaidAmount + amount;
                            if (studentChargeView.ActualAmount >= amountVerify)
                            {
                                return "成功";
                            }
                            else
                            {
                                return "缴费金额大于实收金额，请重新输入";
                            }
                        }
                    }
                    else
                    {
                        return "已存在相同的学生缴费信息";
                    }
                }
                else
                {
                    throw new Exception("对应的应收名单不存在");
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        
    }
}