using System;
using System.Collections.Generic;
using System.Linq;
using Edu.Common;
using Edu.Common.Enum.Finance;
using Edu.Common.Plugin;
using Edu.Model.CacheModel;
using Edu.Model.Entity.Finance;
using Edu.Model.ViewModel.Course;
using Edu.Model.ViewModel.Finance;
using Edu.Model.ViewModel.Grade;
using Edu.Model.ViewModel.Sell;
using Edu.Repository.Course;
using Edu.Repository.Finance;
using Edu.Repository.Grade;
using Edu.Repository.Sell;
using Edu.Repository.StudyAbroad;
using Edu.Repository.User;
using VT.FW.DB;
namespace Edu.Module.Finance
{
/// <summary>
/// 财务处理类
/// </summary>
public class FinanceModule
{
/// <summary>
/// 财务单据
/// </summary>
private readonly RB_FinanceRepository RB_FinanceRepository = new RB_FinanceRepository();
/// <summary>
/// 财务单据详情
/// </summary>
private readonly RB_FinanceDetailRepository RB_FinanceDetailRepository = new RB_FinanceDetailRepository();
/// <summary>
/// 财务单据模板
/// </summary>
private readonly Repository.Finance.Rb_Workflow_TemplateRepository Finance_TemplateRepository = new Repository.Finance.Rb_Workflow_TemplateRepository();
/// <summary>
/// 交易方式
/// </summary>
private readonly RB_TradeWayRepository tradeWayRepository = new RB_TradeWayRepository();
/// <summary>
/// 银行账户
/// </summary>
private readonly RB_BackAccountRepository RB_BackAccountRepository = new RB_BackAccountRepository();
/// <summary>
/// 币种
/// </summary>
private readonly RB_CurrencyRepository RB_CurrencyRepository = new RB_CurrencyRepository();
/// <summary>
/// 现金账户
/// </summary>
private readonly RB_CashAccountRepository cashAccountRepository = new RB_CashAccountRepository();
/// <summary>
/// 资金池账户仓储层对象
/// </summary>
private readonly RB_CashPoolAccountRepository cashPoolAccountRepository = new RB_CashPoolAccountRepository();
/// <summary>
/// 平台账户仓储层对象
/// </summary>
private readonly RB_PlatformAccountRepository platformAccountRepository = new RB_PlatformAccountRepository();
/// <summary>
/// 费用类型仓储层对象
/// </summary>
private readonly RB_CosttypeRepository costtypeRepository = new RB_CosttypeRepository();
/// <summary>
/// 账户类型管理
/// </summary>
private readonly RB_AccountTypeRepository RB_AccountTypeRepository = new RB_AccountTypeRepository();
/// <summary>
/// 财务单据凭证仓储层对象
/// </summary>
private readonly RB_VoucherRepository voucherRepository = new RB_VoucherRepository();
/// <summary>
/// 营收报表
/// </summary>
private readonly RB_Edu_RevenueReportRepository edu_RevenueReportRepository = new RB_Edu_RevenueReportRepository();
/// <summary>
/// 简易报表
/// </summary>
private readonly RB_SimpleReportRepository edu_simpleReportRepository = new RB_SimpleReportRepository();
/// <summary>
/// 报表更新记录
/// </summary>
private readonly RB_Report_UpdateRecordRepository edu_UpdateRecordRepository = new RB_Report_UpdateRecordRepository();
/// <summary>
/// 班级
/// </summary>
private readonly RB_ClassRepository classRepository = new RB_ClassRepository();
/// <summary>
/// 学生签到
/// </summary>
private readonly RB_Class_CheckRepository class_CheckRepository = new RB_Class_CheckRepository();
/// <summary>
/// 基础配置
/// </summary>
private readonly RB_Class_ConfigRepository class_ConfigRepository = new RB_Class_ConfigRepository();
/// <summary>
/// 班级类型
/// </summary>
private readonly RB_Class_TypeRepository class_TypeRepository = new RB_Class_TypeRepository();
/// <summary>
/// 订单列表
/// </summary>
private readonly RB_OrderRepository orderRepository = new RB_OrderRepository();
/// <summary>
/// 订单学生
/// </summary>
private readonly RB_Order_GuestRepository order_GuestRepository = new RB_Order_GuestRepository();
/// <summary>
/// 教师奖励明细
/// </summary>
private readonly RB_Teaching_BonusDetailRepository teaching_BonusDetailRepository = new RB_Teaching_BonusDetailRepository();
/// <summary>
/// 教师绩效
/// </summary>
private readonly RB_Teaching_PerfRepository teaching_PerfRepository = new RB_Teaching_PerfRepository();
/// <summary>
/// 销售提成明细
/// </summary>
private readonly RB_Sell_Commission_DetailsRepository sell_Commission_DetailsRepository = new RB_Sell_Commission_DetailsRepository();
/// <summary>
/// 业绩提成
/// </summary>
private readonly RB_Sell_Achievements_EmpRepository sell_Achievements_EmpRepository = new RB_Sell_Achievements_EmpRepository();
/// <summary>
/// 订单返佣
/// </summary>
private readonly RB_Order_ReturnComissionRepository order_ReturnComissionRepository = new RB_Order_ReturnComissionRepository();
/// <summary>
/// 学校仓储层对象
/// </summary>
private readonly RB_SchoolRepository schoolRepository = new RB_SchoolRepository();
/// <summary>
/// 留学就业
/// </summary>
private readonly RB_StudyAbroadRepository studyAbroadRepository = new RB_StudyAbroadRepository();
/// <summary>
/// 月结设置
/// </summary>
private readonly RB_RollingAccountRepository rollingAccountRepository = new RB_RollingAccountRepository();
/// <summary>
/// 账户
/// </summary>
private readonly RB_AccountRepository accountRepository = new RB_AccountRepository();
/// <summary>
/// 收据配置
/// </summary>
private readonly RB_Receipt_ConfigRepository receipt_ConfigRepository = new RB_Receipt_ConfigRepository();
/// <summary>
/// 收据
/// </summary>
private readonly RB_Receipt_InfoRepository receipt_InfoRepository = new RB_Receipt_InfoRepository();
/// <summary>
/// 试听班级
/// </summary>
private readonly Repository.Reserve.RB_Reserve_ClassRepository reserve_ClassRepository = new Repository.Reserve.RB_Reserve_ClassRepository();
public List<RB_Finance_Extend> GetFinanceInfoList(RB_Finance_Extend model)
{
var list = RB_FinanceRepository.GetListSingle(model)?.Where(x => x.Status != FinanceAuditStatus.Delete).ToList();
List<RB_TradeWay_Extend> tdlist = new List<RB_TradeWay_Extend>();
List<RB_FinanceDetail_Extend> fdlist = new List<RB_FinanceDetail_Extend>();
List<Rb_Workflow_Auditrecord> walist = new List<Rb_Workflow_Auditrecord>();
List<RB_Costtype_Extend> costlist = new List<RB_Costtype_Extend>();
List<RB_Finance_Extend> tclist = new List<RB_Finance_Extend>();
List<RB_Voucher_Extend> volist = new List<RB_Voucher_Extend>();
if (list.Any())
{
string fridStr = string.Join(",", list.Select(x => x.FrID));
tdlist = GetBankListForFrIdStr(fridStr, model.RB_Group_Id ?? 2);
fdlist = RB_FinanceDetailRepository.GetList(new RB_FinanceDetail_Extend() { FrIds = fridStr });
if (fdlist.Any())
{
costlist = costtypeRepository.GetList(new RB_Costtype_Extend() { CostIds = string.Join(",", fdlist.Select(x => x.CostTypeId ?? 0)) });
}
walist = Finance_TemplateRepository.GetAgreeOrWaitList(new Rb_Workflow_Auditrecord() { AuditStatus = WFRrocessStatus.NotAudit }, fridStr);
volist = voucherRepository.GetList(new RB_Voucher_Extend() { FrIdStr = fridStr });
}
foreach (var item in list)
{
item.BankList = new List<RB_TradeWay_Extend>();
item.BankList = tdlist.Where(x => x.FinanceId == item.FrID).ToList();
item.CostTypeList = new List<string>();
var detailList = fdlist.Where(x => x.FinanceId == item.FrID).ToList();
item.detailList = detailList;
foreach (var detailitem in detailList)
{
var cmodel = costlist.Where(x => x.ID == detailitem.CostTypeId).FirstOrDefault();
item.CostTypeList.Add(cmodel?.Name ?? "");
}
#region 判断是否可以操作单据
item.AuditList = new List<AuditInfo>();
if (item.Status == FinanceAuditStatus.InReview)
{
var Auditdata = walist.Where(x => x.WorkFlowId == item.FrID).ToList();
foreach (var sitem in Auditdata)
{
AuditInfo auditInfo = new AuditInfo()
{
AuditEmId = sitem.AuditEmId,
AuditDate = null,
AuditEmName = sitem.AuditEmIdName
};
item.AuditList.Add(auditInfo);
}
}
if (item.Type == WFTempLateClassEnum.IN && item.Status == FinanceAuditStatus.CTemporary)
{
//判断如果是平台的单子 需要把实付清0
var bankModel = item.BankList.FirstOrDefault();
if (bankModel != null)
{
if (bankModel.Type == BranchAccountEnum.Platform && (item.PayMoney ?? 0) > 0)
{
item.PayMoney = 0;
}
}
}
#endregion
#region 团队编号
//item.TCIDAndTCNUMList = tclist.Where(x => x.FrID == item.FrID).FirstOrDefault()?.TCIDAndTCNUMList ?? new List<TCIDInfo>();
#endregion
#region 凭证
item.vorcherInos = volist.Where(x => x.FinanceId == item.FrID).ToList();
#endregion
}
return list;
}
/// <summary>
/// 批量获取财务单据的交易方式
/// </summary>
/// <param name="ID"></param>
/// <returns></returns>
public List<RB_TradeWay_Extend> GetBankListForFrIdStr(string FrIdStr, int GroupId)
{
if (string.IsNullOrWhiteSpace(FrIdStr))
{
return null;
}
List<RB_TradeWay_Extend> list = new List<RB_TradeWay_Extend>();
var TradeList = tradeWayRepository.GetList(new RB_TradeWay_Extend() { FrIdStr = FrIdStr });
//获取币种列表
var CurrencyList = RB_CurrencyRepository.GetList(new Model.Entity.Finance.RB_Currency() { RB_Group_Id = GroupId });
//查询四大账户
var BankList = RB_BackAccountRepository.GetList(new RB_BackAccount_Extend() { AccountIdStr = string.Join(",", TradeList.Where(x => x.Type == BranchAccountEnum.Bank).Select(x => x.AccountId ?? 0).Distinct()) });
var CashList = cashAccountRepository.GetList(new RB_CashAccount_Extend() { AccountIdStr = string.Join(",", TradeList.Where(x => x.Type == BranchAccountEnum.Cash).Select(x => x.AccountId ?? 0).Distinct()) });
var CashPoolList = cashPoolAccountRepository.GetList(new RB_CashPoolAccount_Extend() { AccountIdStr = string.Join(",", TradeList.Where(x => x.Type == BranchAccountEnum.CashPool).Select(x => x.AccountId ?? 0).Distinct()) });
var PlatformList = platformAccountRepository.GetList(new RB_PlatformAccount_Extend() { AccountIdStr = string.Join(",", TradeList.Where(x => x.Type == BranchAccountEnum.Platform).Select(x => x.AccountId ?? 0).Distinct()) });
foreach (var TradeModel in TradeList)
{
RB_TradeWay_Extend accountInFo = TradeModel.RefMapperTo<RB_TradeWay_Extend>();
accountInFo.CurrencyName = CurrencyList.Where(x => x.ID == TradeModel.CurrencyId).FirstOrDefault()?.Name ?? "";
if (TradeModel.Type == Common.Enum.Finance.BranchAccountEnum.Bank)
{
var bank = BankList.Where(x => x.ID == TradeModel.AccountId).FirstOrDefault();
if (bank != null)
{
accountInFo.Alias = bank.Alias;
accountInFo.BankNo = bank.BackNo;
accountInFo.Type = Common.Enum.Finance.BranchAccountEnum.Bank;
accountInFo.AccountTypeId = bank.TypeId;
var accountType = RB_AccountTypeRepository.GetEntity(bank.TypeId.Value);
accountInFo.AccountType = accountType?.IsPublic == 0 ? "对私" : "对公";
}
}
else if (TradeModel.Type == Common.Enum.Finance.BranchAccountEnum.Cash)
{
var cash = CashList.Where(x => x.ID == TradeModel.AccountId).FirstOrDefault();
if (cash != null)
{
accountInFo.Alias = cash.Alias;
accountInFo.BankNo = "";
accountInFo.Type = Common.Enum.Finance.BranchAccountEnum.Cash;
accountInFo.AccountTypeId = cash.TypeId;
var accountType = RB_AccountTypeRepository.GetEntity(cash.TypeId.Value);
accountInFo.AccountType = accountType?.IsPublic == 0 ? "对私" : "对公";
}
}
else if (TradeModel.Type == Common.Enum.Finance.BranchAccountEnum.CashPool)
{
var pool = CashPoolList.Where(x => x.ID == TradeModel.AccountId).FirstOrDefault();
if (pool != null)
{
accountInFo.Alias = pool.Alias;
accountInFo.BankNo = "";
accountInFo.Type = Common.Enum.Finance.BranchAccountEnum.CashPool;
accountInFo.AccountTypeId = pool.TypeId;
var accountType = RB_AccountTypeRepository.GetEntity(pool.TypeId.Value);
accountInFo.AccountType = accountType?.IsPublic == 0 ? "对私" : "对公";
}
}
else if (TradeModel.Type == Common.Enum.Finance.BranchAccountEnum.Platform)
{
var platform = PlatformList.Where(x => x.ID == TradeModel.AccountId).FirstOrDefault();
if (platform != null)
{
accountInFo.Alias = platform.Alias;
accountInFo.BankNo = platform.PlatformNo;
accountInFo.Type = Common.Enum.Finance.BranchAccountEnum.Platform;
accountInFo.AccountTypeId = platform.TypeId;
var accountType = RB_AccountTypeRepository.GetEntity(platform.TypeId.Value);
accountInFo.AccountType = accountType?.IsPublic == 0 ? "对私" : "对公";
}
}
list.Add(accountInFo);
}
return list;
}
#region 营收报表+简易报表初始化
/// <summary>
/// 更新营收报表
/// </summary>
/// <param name="GroupId"></param>
/// <param name="StartTime"></param>
/// <param name="EndTime"></param>
/// <returns></returns>
public bool SetEduRevenueReport(int GroupId, string StartTime, bool IsUpdateHistory)
{
edu_UpdateRecordRepository.Insert(new RB_Report_UpdateRecord()
{
Type = 1,
StartDate = DateTime.Now
});
string STime = "";string ETime = "";
if (IsUpdateHistory == true)
{
//历史 往后倒退一年
STime = Convert.ToDateTime(StartTime).AddYears(-1).ToString("yyyy-MM-dd");
ETime = StartTime;
}
else {
STime = StartTime;
ETime = DateTime.Now.AddYears(1).ToString("yyyy-MM-dd");
}
//查询日期范围内所有的班级
var classList = classRepository.GetClassAndCourseListRepository(new RB_Class_ViewModel() { Group_Id = GroupId, StartTime = STime, EndTime = ETime }).ToList();
List<RB_Order_ViewModel> orderAllList = new List<RB_Order_ViewModel>();
List<RB_Finance_Extend> FAllList = new List<RB_Finance_Extend>();
List<RB_Teaching_BonusDetail_ViewModel> teacherBonusList = new List<RB_Teaching_BonusDetail_ViewModel>();
List<RB_Teaching_Perf_ViewModel> meritsProfitList = new List<RB_Teaching_Perf_ViewModel>();
List<RB_Class_Check_ViewModel> classHoursList = new List<RB_Class_Check_ViewModel>();
List<RB_Class_Check_ViewModel> classStuList = new List<RB_Class_Check_ViewModel>();
List<RB_Sell_Achievements_Emp_ViewModel> AchDetailList = new List<RB_Sell_Achievements_Emp_ViewModel>();
if (classList.Any())
{
string classIds = string.Join(",", classList.Select(x => x.ClassId));
//根据班级查询所有的订单列表
orderAllList = orderRepository.GetOrderListRepository(new RB_Order_ViewModel() { Group_Id = GroupId, ClassIds = classIds, OrderType = Common.Enum.Sale.OrderTypeEnum.CourseOrder, Q_OrderState = 1 });
//查询班级下所有的财务单据
FAllList = RB_FinanceRepository.GetDataStatisticsListThree(new RB_Finance_Extend() { RB_Group_Id = GroupId, TCIDStr = classIds, OrderSource = OrderResourceEnum.Education });
teacherBonusList = teaching_BonusDetailRepository.GetList(new RB_Teaching_BonusDetail_ViewModel { Group_Id = GroupId, ClassIds = classIds, CouseClassify = 1 });
meritsProfitList = teaching_PerfRepository.GetList(new RB_Teaching_Perf_ViewModel { Group_Id = GroupId, ClassIds = classIds });
//查询班级下已上课时数量
classHoursList = class_CheckRepository.GetClassLearnHoursList(GroupId, classIds);
//查询班级下所有学生的上课金额
classStuList = class_CheckRepository.GetClassStuLearnMoneyList(GroupId, classIds);
#region 查询订单下所有的业绩提成
if (orderAllList.Any())
{
string orderIds = string.Join(",", orderAllList.Select(x => x.OrderId));
AchDetailList = sell_Achievements_EmpRepository.GetList(new RB_Sell_Achievements_Emp_ViewModel() { Group_Id = GroupId, OrderIds = orderIds });
}
#endregion
}
//排序取消/删除班级
var oldlist = edu_RevenueReportRepository.GetList(new RB_Edu_RevenueReport_Extend() { StartTime = STime, EndTime = ETime, DateType = 1, Rb_Group_Id = GroupId });
var delList = oldlist.Where(x => !classList.Select(y => y.ClassId).Contains(x.ClassId)).ToList();
if (delList.Any())
{
edu_RevenueReportRepository.DeleteBatch(delList);
}
foreach (var item in classList) {
try
{
Console.WriteLine("班级：" + item.ClassName + "|" + item.ClassId);
bool IsNew = false;//是否新增
var orderList = orderAllList.Where(x => x.ClassId == item.ClassId).ToList();
var financeList = FAllList.Where(x => x.TCID == item.ClassId).ToList();
var demodel = oldlist.Where(x => x.ClassId == item.ClassId).FirstOrDefault();
if (demodel == null) {
IsNew = true;
//新增
demodel = new RB_Edu_RevenueReport_Extend()
{
Id = 0,
ClassId = item.ClassId,
Rb_Group_Id = GroupId,
SchoolId = item.School_Id,
SchoolName = item.SchoolName,
DateType = 1
};
}
#region 初始化数据
demodel.ClassBegins = item.OpenTime;
demodel.ClassName = item.ClassName;
demodel.ClassNo = item.ClassNo;
demodel.ClassState = (int)item.ClassStatus;
demodel.CourseId = item.CouseId;
demodel.CourseName = item.CourseName;
demodel.TeacherId = item.Teacher_Id;
demodel.TeacherName = item.TeacherName;
demodel.SchoolId = item.School_Id;
demodel.SchoolName = item.SchoolName;
demodel.HoursNum = item.ClassHours;
demodel.ConsumeNum = Convert.ToInt32(classHoursList.Where(x => x.ClassId == item.ClassId).FirstOrDefault()?.CurrentDeductionHours ?? 0);
demodel.ConsumeMoney = Math.Round(classStuList.Where(x => x.ClassId == item.ClassId).FirstOrDefault()?.UnitPrice ?? 0, 2, MidpointRounding.AwayFromZero);
#endregion
#region 统计数据
#region 订单
demodel.YingShou = orderList.Sum(x => x.PreferPrice);
demodel.ShiShou = orderList.Sum(x => x.Income);
demodel.TuiKuan = orderList.Sum(x => x.Refund);
demodel.PingTaiShuiJin = orderList.Sum(x => x.PlatformTax);
demodel.ZaiTu = 0;//暂时在途未做
demodel.YouHui = orderList.Sum(x => x.DiscountMoney);
demodel.DaiShou = (demodel.YingShou - demodel.ShiShou + demodel.TuiKuan - demodel.PingTaiShuiJin - demodel.YouHui);
demodel.YiShou = -demodel.DaiShou;
demodel.GuestNum = orderList.Sum(x => x.GuestNum);
#endregion
#region 财务单据
//排序订单单据
var cfinanceList = financeList.Where(x => x.OrderID == 0 && x.Type == WFTempLateClassEnum.IN).ToList();
demodel.QTShouRu = cfinanceList.Where(y => y.Is_Cashier == 1).Sum(x => x.Money ?? 0);
demodel.QTShouRu -= cfinanceList.Where(x => x.Is_Cashier == 1 && x.Fee > 0).GroupBy(x => new { x.FrID, x.Fee }).Sum(x => x.Key.Fee ?? 0);
//支出
demodel.TiCheng = orderList.Where(x => x.IsCommissionGive == 1).Sum(x => x.CommissionMoney + x.ExtraCommissionMoney);//销售提成
var achList = AchDetailList.Where(x => orderList.Select(x => x.OrderId).Contains(x.OrderId)).ToList();
if (achList.Any()) {
demodel.TiCheng += achList.Sum(x => x.GiveOutMoney);// 业绩提成
}
demodel.ClassFee = teacherBonusList.Where(x => x.ClassId == item.ClassId).Sum(x => x.Money);//老师提成
demodel.JiXiaoMoney = meritsProfitList.Where(x => x.ClassId == item.ClassId && x.PerfState == Common.Enum.Course.PerfStateEnum.Confirmed).OrderByDescending(x => x.CreateTime).FirstOrDefault()?.Money ?? 0;//绩效提成
var ofinanceList = financeList.Where(x => x.Type == WFTempLateClassEnum.OUT).ToList();// 支出包括退款
demodel.QTZhiChu = ofinanceList.Sum(x => x.Money ?? 0);
demodel.YingFu = demodel.QTZhiChu + demodel.TiCheng + demodel.ClassFee + demodel.JiXiaoMoney;
#endregion
demodel.MaoLi = demodel.ShiShou - demodel.YingFu;
demodel.ShiJiLiRun = demodel.MaoLi + demodel.QTShouRu;
//毛利率
if (demodel.ShiShou != 0)
{
demodel.MaoLiRate = Math.Round(demodel.MaoLi / demodel.ShiShou * 100, 2, MidpointRounding.AwayFromZero);
}
if (demodel.GuestNum > 0)
{
//平均收入=实收+自费收入+其他收入+佣金收入（属于佣金的所有）/参团+单团报入
demodel.AverageIncome = Math.Round((demodel.ShiShou + demodel.QTShouRu) / demodel.GuestNum, 2, MidpointRounding.AwayFromZero);
//平均成本=应付/参团+单团报入
demodel.AverageCost = Math.Round((demodel.YingFu / demodel.GuestNum), 2, MidpointRounding.AwayFromZero);
}
//平均利润=平均收入-平均成本
demodel.AverageProfit = demodel.AverageIncome - demodel.AverageCost;
#endregion
if (IsNew)
{
edu_RevenueReportRepository.Insert(demodel);
}
else {
edu_RevenueReportRepository.Update(demodel);
}
}
catch (Exception ex)
{
Console.WriteLine("班级：" + item.ClassName + "|" + item.ClassId + ",错误：" + ex.Message);
LogHelper.Write(ex, "SetEduRevenueReport");
}
}
#region 留学就业
//查询日期范围内所有的留学就业项目
var saList = studyAbroadRepository.GetStudyAbroadListExtRepository(new Model.ViewModel.StudyAbroad.RB_StudyAbroad_ViewModel() { Group_Id = GroupId, School_Id = -1, SaleState = Common.Enum.Sale.SaleStateEnum.Sell, StartTime = STime, EndTime = ETime });
if (saList.Any()) {
string saIds = string.Join(",", saList.Select(x => x.Id));
//根据班级查询所有的订单列表
orderAllList = orderRepository.GetOrderListRepository(new RB_Order_ViewModel() { Group_Id = GroupId, SourceIds = saIds, OrderType = Common.Enum.Sale.OrderTypeEnum.StudyabroadOrder, Q_OrderState = 1 });
//查询班级下所有的财务单据
FAllList = RB_FinanceRepository.GetDataStatisticsListThree(new RB_Finance_Extend() { RB_Group_Id = GroupId, TCIDStr = saIds, OrderSource = OrderResourceEnum.EducationStudy });
}
//排除取消/删除项目
oldlist = edu_RevenueReportRepository.GetList(new RB_Edu_RevenueReport_Extend() { StartTime = STime, EndTime = ETime, IsSelectStuAbroad = 1, Rb_Group_Id = GroupId });
delList = oldlist.Where(x => !saList.Select(y => y.Id).Contains(x.ClassId)).ToList();
if (delList.Any())
{
edu_RevenueReportRepository.DeleteBatch(delList);
}
foreach (var item in saList) {
try
{
Console.WriteLine("留学就业：" + item.Name + "|" + item.Id);
bool IsNew = false;//是否新增
var orderList = orderAllList.Where(x => x.SourceId == item.Id).ToList();
var financeList = FAllList.Where(x => x.TCID == item.Id).ToList();
var demodel = oldlist.Where(x => x.ClassId == item.Id).FirstOrDefault();
if (demodel == null)
{
IsNew = true;
//新增
demodel = new RB_Edu_RevenueReport_Extend()
{
Id = 0,
ClassId = item.Id,
Rb_Group_Id = GroupId,
SchoolId = item.School_Id,
SchoolName = item.SchoolName,
DateType = item.Type == 1 ? 2 : 3
};
}
#region 初始化数据
demodel.ClassBegins = item.CreateTime;
demodel.ClassName = item.Name;
demodel.ClassState = 0;
demodel.CourseId = 0;
demodel.CourseName = item.SupplierName;
demodel.TeacherId = 0;
demodel.TeacherName = item.Type == 1 ? "留学" : "就业";
demodel.SchoolId = item.School_Id;
demodel.SchoolName = item.SchoolName;
#endregion
#region 统计数据
#region 订单
demodel.YingShou = orderList.Sum(x => x.PreferPrice);
demodel.ShiShou = orderList.Sum(x => x.Income);
demodel.TuiKuan = orderList.Sum(x => x.Refund);
demodel.PingTaiShuiJin = orderList.Sum(x => x.PlatformTax);
demodel.ZaiTu = 0;//暂时在途未做
demodel.YouHui = orderList.Sum(x => x.DiscountMoney);
demodel.DaiShou = (demodel.YingShou - demodel.ShiShou + demodel.TuiKuan - demodel.PingTaiShuiJin - demodel.YouHui);
demodel.YiShou = -demodel.DaiShou;
demodel.GuestNum = orderList.Sum(x => x.GuestNum);
#endregion
#region 财务单据
//排序订单单据
var cfinanceList = financeList.Where(x => x.OrderID == 0 && x.Type == WFTempLateClassEnum.IN).ToList();
demodel.QTShouRu = cfinanceList.Where(y => y.Is_Cashier == 1).Sum(x => x.Money ?? 0);
demodel.QTShouRu -= cfinanceList.Where(x => x.Is_Cashier == 1 && x.Fee > 0).GroupBy(x => new { x.FrID, x.Fee }).Sum(x => x.Key.Fee ?? 0);
//支出
demodel.TiCheng = (orderList != null && orderList.Any()) ? orderList.Where(x => x.IsCommissionGive == 1).Sum(x => x.CommissionMoney + x.ExtraCommissionMoney) : 0;//销售提成 -留学提成暂定
demodel.ClassFee = 0;//老师提成 -留学暂定
demodel.JiXiaoMoney = 0;//绩效提成 -留学暂定
var ofinanceList = financeList.Where(x => x.Type == WFTempLateClassEnum.OUT).ToList();// 支出包括退款
demodel.QTZhiChu = ofinanceList.Sum(x => x.Money ?? 0);
demodel.YingFu = demodel.QTZhiChu + demodel.TiCheng + demodel.ClassFee + demodel.JiXiaoMoney;
#endregion
demodel.MaoLi = demodel.ShiShou - demodel.YingFu;
demodel.ShiJiLiRun = demodel.MaoLi + demodel.QTShouRu;
//毛利率
if (demodel.ShiShou != 0)
{
demodel.MaoLiRate = Math.Round(demodel.MaoLi / demodel.ShiShou * 100, 2, MidpointRounding.AwayFromZero);
}
if (demodel.GuestNum > 0)
{
//平均收入=实收+自费收入+其他收入+佣金收入（属于佣金的所有）/参团+单团报入
demodel.AverageIncome = Math.Round((demodel.ShiShou + demodel.QTShouRu) / demodel.GuestNum, 2, MidpointRounding.AwayFromZero);
//平均成本=应付/参团+单团报入
demodel.AverageCost = Math.Round((demodel.YingFu / demodel.GuestNum), 2, MidpointRounding.AwayFromZero);
}
//平均利润=平均收入-平均成本
demodel.AverageProfit = demodel.AverageIncome - demodel.AverageCost;
#endregion
if (IsNew)
{
edu_RevenueReportRepository.Insert(demodel);
}
else
{
edu_RevenueReportRepository.Update(demodel);
}
}
catch (Exception ex)
{
Console.WriteLine("留学就业：" + item.Name + "|" + item.Id + ",错误：" + ex.Message);
LogHelper.Write(ex, "SetEduRevenueReport");
}
}
#endregion
#region 试听课程
var rclist = reserve_ClassRepository.GetReserveClassListRepository(new Model.ViewModel.Reserve.RB_Reserve_Class_Extend() { Group_Id = GroupId, StartClassDate = STime, EndClassDate = ETime });
if (rclist.Any()) {
string rclassIds = string.Join(",", rclist.Select(x => x.ReserveClassId));
//查询班级下所有的财务单据
FAllList = RB_FinanceRepository.GetDataStatisticsListThree(new RB_Finance_Extend() { RB_Group_Id = GroupId, TCIDStr = rclassIds, OrderSource = OrderResourceEnum.ReserveClass });
}
#endregion
var fumodel = edu_UpdateRecordRepository.GetList(new RB_Report_UpdateRecord() { Type = 1 }).FirstOrDefault();
if (fumodel != null)
{
fumodel.EndDate = DateTime.Now;
edu_UpdateRecordRepository.Update(fumodel);
}
return true;
}
/// <summary>
/// 获取营收报表分页列表
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <param name="dmodel"></param>
/// <returns></returns>
public List<RB_Edu_RevenueReport_Extend> GetRevenueReportPageList(int pageIndex, int pageSize, out long count, RB_Edu_RevenueReport_Extend dmodel)
{
var list = edu_RevenueReportRepository.GetPageList(pageIndex, pageSize, out count, dmodel);
//统计
var Statistics = edu_RevenueReportRepository.GetRevenueReportStatistics(dmodel);
if (count > 0) {
Statistics.MaoLiRate = Math.Round(Statistics.MaoLiRate / count, 2, MidpointRounding.AwayFromZero);
Statistics.AverageIncome = Math.Round(Statistics.AverageIncome / count, 2, MidpointRounding.AwayFromZero);
Statistics.AverageCost = Math.Round(Statistics.AverageCost / count, 2, MidpointRounding.AwayFromZero);
Statistics.AverageProfit = Math.Round(Statistics.AverageProfit / count, 2, MidpointRounding.AwayFromZero);
}
list.Add(Statistics);
return list;
}
/// <summary>
/// 获取最新记录
/// </summary>
/// <returns></returns>
public RB_Report_UpdateRecord GetUpdateRecordNew()
{
return edu_UpdateRecordRepository.GetList(new RB_Report_UpdateRecord() { Type = 1 }).FirstOrDefault();
}
/// <summary>
/// 更新简易报表
/// </summary>
/// <param name="GroupId"></param>
/// <param name="Year"></param>
/// <returns></returns>
public bool InsertBatchSimple(int GroupId, int Year)
{
edu_UpdateRecordRepository.Insert(new RB_Report_UpdateRecord()
{
Type = 2,
StartDate = DateTime.Now
});
try
{
string sDate = Year + "-01-01";
string eDate = DateTime.Now.AddYears(1).AddDays(-1).ToString("yyyy-MM-dd");
int i = 1;
string FYLX = "营业总收入,其他收入,待收金额,退款金额,平台税金,优惠金额,营业总成本,营业总毛利,工资,员工提成,社保,员工福利及竞赛奖金,办公用品,差旅费,电话费(通訊費),快递费,物管水电费,房租,广告费,业务招待,税金,平台使用费,電腦系统费,市内交通费,团队建设费,会务费,工会经费,律师费,服务器管理费,汇款手续费,其他,管销费用,利息收入,汇兑损益,财务费用,营业外收入,其他营业支出,营业利润(亏损),所得税费用,净利润(亏损),以下项均不计入计算,股东权益(投资款),股东权益(利润分配),股东权益(员工红利),小计1,预付款(教育),预付款(保证金.押金),小计2,备用金,个人借款,其他代垫代收,陈芳英款项,小计3";
string RZFYLX = "营业总收入,其他收入,待收金额,退款金额,平台税金,优惠金额,营业总成本,营业总毛利,营业利润(亏损),净利润(亏损)";
var FYLXList = new List<string>(FYLX.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries));
var RZFYLXList = new List<string>(RZFYLX.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries));
//财务单据
var RZFinancelist = RB_FinanceRepository.GetListForReport(new RB_Finance_Extend() { sAduitDate = DateTime.Parse(sDate), eAduitDate = DateTime.Now.AddYears(1).AddDays(-1), RB_Branch_Id = -1, RB_Group_Id = GroupId, Is_TCID = 1, TCID = -1, FinanceType = 2 });
//获取资金调拨单据
var CapitalAllocationFinanceList = RB_FinanceRepository.GetTradeWayForCapitalAllocation(new RB_Finance_Extend() { sAduitDate = DateTime.Parse(sDate), eAduitDate = DateTime.Now.AddYears(1).AddDays(-1), RB_Branch_Id = -1, RB_Group_Id = GroupId, Is_TCID = 1, TCID = -1 });
//营收报表数据
var YSBBList = edu_RevenueReportRepository.GetList(new RB_Edu_RevenueReport_Extend() { DateType = 0, StartTime = sDate, EndTime = eDate, Rb_Group_Id = GroupId });
//历史简易
var oldlist = edu_simpleReportRepository.GetList(new RB_SimpleReport_Extend() { Year = Year, Rb_Group_Id = GroupId });
//查询校区
var schoolList = schoolRepository.GetSchoolListRepository(new Model.ViewModel.User.RB_School_ViewModel() { Group_Id = GroupId });
List<string> shareList = new List<string>() { "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12" };
foreach (var branchitem in schoolList)
{
var srList = oldlist.Where(x => x.BranchId == branchitem.SId).ToList();
edu_simpleReportRepository.DeleteBatch(srList);
foreach (var ditem in FYLXList)
{
RB_SimpleReport ousimpleReport = new RB_SimpleReport();
decimal January = 0, February = 0, March = 0, April = 0, May = 0, June = 0, July = 0, August = 0, September = 0, October = 0, November = 0, December = 0;
foreach (var item in shareList)
{
sDate = Convert.ToDateTime(Year + "-" + item + "-01").ToString("yyyy-MM-dd");
eDate = DateTime.Parse((Year + "-" + item + "-01")).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
#region 营业总收入，佣金收入，待收金额，退款金额，平台税金，联运成本，营业总成本，联运营业额支出
decimal YSJE = 0, YYZSR = 0, DSJE = 0, TKJE = 0, PJSJ = 0, YouHui = 0, YYZCB = 0, QTShouRu = 0;
var financelist = new List<RB_Finance_Extend>();
var NowYSBBList = YSBBList.Where(x => x.SchoolId == branchitem.SId && x.ClassBegins.ToString("yyyy-MM") == Convert.ToDateTime(sDate).ToString("yyyy-MM")).ToList();
if (RZFYLXList.Contains(ditem))
{
YSJE = NowYSBBList.Sum(x => x.YingShou);
YYZSR = NowYSBBList.Sum(x => x.ShiShou);
TKJE = NowYSBBList.Sum(x => x.TuiKuan);
PJSJ = NowYSBBList.Sum(x => x.PingTaiShuiJin);
YouHui = NowYSBBList.Sum(x => x.YouHui);
//待收金额=应收-已收-凭条税金-在途金额+退款+问题账户；
DSJE = YSJE - YYZSR - PJSJ + TKJE - YouHui;
YYZCB = NowYSBBList.Sum(x => x.YingFu);
QTShouRu = NowYSBBList.Sum(x => x.QTShouRu);
}
#endregion
#region 日常费用类
decimal GXFY = 0;//管销费用
decimal YYZML = 0;//营业总毛利=(营业总收入+退款总金额-待收金额-平台税金-在途金额(这个是营收报表的实收))-营业总成本（营收报表的应付）+佣金总收入+自费收入-联运成本-旅游意外保险
var Financelist = RZFinancelist.Where(x => ((x.Type == WFTempLateClassEnum.OUT && x.CreateDate.Value.ToString("yyyy-MM") == Convert.ToDateTime(sDate).ToString("yyyy-MM")) || (x.Type != WFTempLateClassEnum.OUT && x.TradeDate.Value.ToString("yyyy-MM") == Convert.ToDateTime(sDate).ToString("yyyy-MM"))) && x.RB_Branch_Id == branchitem.SId).ToList();
YYZML = NowYSBBList.Sum(x => x.ShiJiLiRun);
decimal GZ = 0;//工资
decimal YGTC = 0;//员工提成
decimal SB = 0;//社保
decimal YGFL = 0;//员工福利及竞赛奖金
decimal BGYP = 0;//办公用品
decimal CLF = 0;//差旅费
decimal TEL = 0;//电话费
decimal KDF = 0;//快递费
decimal WGSDF = 0;//物管水电费
decimal FZ = 0;//房租
decimal GGF = 0;//广告费
decimal YWZD = 0;//业务招待
decimal LJZK = 0;//累计折扣
decimal Sj = 0;//税金
decimal PTSYF = 0;//平台使用费
decimal DNSYF = 0;//电脑使用费
decimal SNJTF = 0;//市内交通费
decimal TJF = 0;//团建费
decimal HWF = 0;//会务费
decimal GHJF = 0;//工会经费
decimal LSF = 0;//律师费
decimal FWQGLF = 0;//服务器管理员费
decimal HKSXF = 0;//汇款手续费
decimal JYQT = 0;//其他
decimal LXSR = 0;//利息收入
decimal HDSY = 0;//汇兑损益
decimal CWFY = 0;//财务费用=利息收入-汇兑损益
decimal YYWSR1 = 0;//营业外收入1
//decimal YYWSR2 = 0;//营业外收入2（领队）
//decimal QTYYSR = 0;//其他营业收入
decimal YYWSR = 0;//营业外收入=营业外收入+营业外收入1，更正：营业外收入=营业外收入1+营业外收入2（领队）+其他营业收入;
//YYWSR = GetCostIds(44, Financelist, true) + YYWSR1;
YYWSR = YYWSR1;//更正：营业外收入=营业外收入1
decimal QTYYZC = 0;//其他营业支出
decimal YSYF = 0;//应收应付
//decimal YSYFTZ = 0;//应收应付投资
decimal YYLRKS = 0;//营业利润亏损=营业总毛利-分摊金额-营业税金及附加-管销费用+财务费用+营业外收入-其他营业支出-应收应付款
YYLRKS = YYZML - GXFY + CWFY + YYWSR - QTYYZC - YSYF;
decimal SDSFY = 0;//所得税费用
decimal JLRKS = 0;//净利润亏损=营业利润+所得税费用，更正：净利润（亏损）=营业利润（亏损）-所得税费用
#endregion
#region 资金调拨汇兑损益
decimal ZJTBHDSYMoney = 0;
if (ditem == "汇兑损益" || ditem == "财务费用" || ditem == "营业利润(亏损)" || ditem == "净利润(亏损)")
{
var calist = CapitalAllocationFinanceList.Where(x => x.AllotDate.Value.ToString("yyyy-MM") == Convert.ToDateTime(sDate).ToString("yyyy-MM") && x.BranchId == branchitem.SId).ToList();
//资金调拨的汇兑损益
decimal iMoney = calist.Where(x => x.AllotType == 1)?.Sum(x => x.Money ?? 0) ?? 0;
decimal oMoney = calist.Where(x => x.AllotType == 2)?.Sum(x => x.Money ?? 0) ?? 0;
ZJTBHDSYMoney = oMoney - iMoney;
}
#endregion
#region 赋值
decimal Money = 0;
if (ditem == "营业总收入")
{
Money = YSJE;
}
else if (ditem == "其他收入")
{
Money = QTShouRu;
}
else if (ditem == "待收金额")
{
Money = DSJE;
}
if (ditem == "平台税金")
{
Money = PJSJ;
}
else if (ditem == "退款金额")
{
Money = TKJE;
}
else if (ditem == "优惠金额")
{
Money = YouHui;
}
else if (ditem == "营业总成本")
{
Money = YYZCB;
}
else if (ditem == "营业总毛利")
{
Money = YYZML;
}
else if (ditem == "工资")
{
GZ = GetCostIds(15, Financelist, false, 1, true);
Money = GZ;
}
else if (ditem == "员工提成")
{
YGTC = GetCostIds(16, Financelist, false, 1, true);
Money = YGTC;
}
else if (ditem == "社保")
{
SB = GetCostIds(17, Financelist, false, 1, true);
Money = SB;
}
else if (ditem == "员工福利及竞赛奖金")
{
YGFL = GetCostIds(18, Financelist, false, 1, true);
Money = YGFL;
}
else if (ditem == "办公用品")
{
BGYP = GetCostIds(19, Financelist, false, 1, true);
Money = BGYP;
}
else if (ditem == "差旅费")
{
CLF = GetCostIds(20, Financelist, false, 1, true);
Money = CLF;
}
else if (ditem == "电话费(通訊費)")
{
TEL = GetCostIds(21, Financelist, false, 1, true);
Money = TEL;
}
else if (ditem == "快递费")
{
KDF = GetCostIds(52, Financelist, false, 1, true);
Money = KDF;
}
else if (ditem == "物管水电费")
{
WGSDF = GetCostIds(22, Financelist, false, 1, true);
Money = WGSDF;
}
else if (ditem == "房租")
{
FZ = GetCostIds(23, Financelist, false, 1, true);
Money = FZ;
}
else if (ditem == "广告费")
{
GGF = GetCostIds(24, Financelist, false, 1, true);
Money = GGF;
}
else if (ditem == "业务招待")
{
YWZD = GetCostIds(25, Financelist, false, 1, true);
Money = YWZD;
}
else if (ditem == "累计折旧")
{
LJZK = GetCostIds(26, Financelist, false, 1, true);
Money = LJZK;
}
else if (ditem == "税金")
{
Sj = GetCostIds(27, Financelist, false, 1, true);
Money = Sj;
}
else if (ditem == "平台使用费")
{
PTSYF = GetCostIds(28, Financelist, false, 1, true);
Money = PTSYF;
}
else if (ditem == "電腦系统费")
{
DNSYF = GetCostIds(29, Financelist, false, 1, true);
Money = DNSYF;
}
else if (ditem == "市内交通费")
{
SNJTF = GetCostIds(30, Financelist, false, 1, true);
Money = SNJTF;
}
else if (ditem == "团队建设费")
{
TJF = GetCostIds(31, Financelist, false, 1, true);
Money = TJF;
}
else if (ditem == "会务费")
{
HWF = GetCostIds(32, Financelist, false, 1, true);
Money = HWF;
}
else if (ditem == "工会经费")
{
GHJF = GetCostIds(34, Financelist, false, 1, true);
Money = GHJF;
}
else if (ditem == "律师费")
{
LSF = GetCostIds(35, Financelist, false, 1, true);
Money = LSF;
}
else if (ditem == "服务器管理费")
{
FWQGLF = GetCostIds(36, Financelist, false, 1, true);
Money = FWQGLF;
}
else if (ditem == "汇款手续费")
{
HKSXF = GetCostIds(41, Financelist, false, 1, true);
Money = HKSXF;
}
else if (ditem == "其他")
{
JYQT = GetCostIds(51, Financelist, false, 1, true);
Money = JYQT;
}
else if (ditem == "管销费用")
{
GXFY = GetGXFY(Financelist);
Money = GXFY;
}
else if (ditem == "利息收入")
{
LXSR = GetCostIds(42, Financelist, false, 1, true);
Money = -LXSR;
}
else if (ditem == "汇兑损益")
{
HDSY = GetCostIds(43, Financelist, false, 1, true);
Money = HDSY + ZJTBHDSYMoney;
}
else if (ditem == "财务费用")
{
//财务费用=利息收入-汇兑损益
CWFY = -GetCostIds(42, Financelist, false, 1, true) - GetCostIds(43, Financelist, false, 1, true) - ZJTBHDSYMoney;
Money = CWFY;
}
else if (ditem == "营业外收入")
{
Money = GetCostIds(44, Financelist, true, 1);
}
else if (ditem == "其他营业支出")
{
Money = GetCostIds(48, Financelist, false, 1);
}
else if (ditem == "应收应付款")
{
//应收应付款=出纳审核过的收款单之和-所有应付款
decimal YS = GetCostIds(55, Financelist, true, 1);
decimal YF = GetCostIds(56, Financelist, false, 1);
Money = YF - YS;
}
else if (ditem == "营业利润(亏损)")
{
decimal yywsrMoney = GetCostIds(44, Financelist, true, 1);
decimal yywzcMoney = GetCostIds(48, Financelist, false, 1);
//营业利润亏损 = 营业总毛利 - 分摊金额 - 管销费用 + 财务费用 + 营业外收入 - 其他营业支出 - 应收应付款(应收-应付)
Money = YYZML - GetGXFY(Financelist) + (-GetCostIds(42, Financelist, false, 1, true) - GetCostIds(43, Financelist, false, 1, true)) - ZJTBHDSYMoney + yywsrMoney - yywzcMoney - (GetCostIds(56, Financelist, true, 1) - GetCostIds(55, Financelist, false, 1));
}
else if (ditem == "所得税费用")
{
SDSFY = GetCostIds(49, Financelist, false, 1, true);
Money = SDSFY;
}
else if (ditem == "净利润(亏损)")
{
decimal yywsrMoney = GetCostIds(44, Financelist, true, 1);
decimal yywzcMoney = GetCostIds(48, Financelist, false, 1);
//净利润亏损=营业利润+所得税费用，更正：净利润（亏损）=营业利润（亏损）-所得税费用
JLRKS = YYZML - GetGXFY(Financelist) + (-GetCostIds(42, Financelist, false, 1, true) - GetCostIds(43, Financelist, false, 1, true)) - ZJTBHDSYMoney + yywsrMoney - yywzcMoney - (GetCostIds(56, Financelist, true, 1) - GetCostIds(55, Financelist, false, 1)) - GetCostIds(49, Financelist, false, 1, true);
Money = JLRKS;
}
else if (ditem == "股东权益(投资款)")
{
//应收应付款=出纳审核过的收款单之和-所有应付款
decimal YS = GetCostIds(58, Financelist, true, 1);
decimal YF = GetCostIds(59, Financelist, false, 1);
Money = YS - YF;
}
else if (ditem == "股东权益(利润分配)")
{
decimal YS = GetCostIds(61, Financelist, true, 1);
decimal YF = GetCostIds(62, Financelist, false, 1);
Money = YS - YF;
}
else if (ditem == "股东权益(员工红利)")
{
decimal YS = GetCostIds(63, Financelist, true, 1);
decimal YF = GetCostIds(64, Financelist, false, 1);
Money = YS - YF;
}
else if (ditem == "预付款(教育)")
{
decimal YS = GetCostIds(73, Financelist, true, 1);
decimal YF = GetCostIds(74, Financelist, false, 1);
Money = YS - YF;
}
else if (ditem == "预付款(保证金.押金)")
{
decimal YS = GetCostIds(75, Financelist, true, 1);
decimal YF = GetCostIds(76, Financelist, false, 1);
Money = YS - YF;
}
else if (ditem == "备用金")
{
decimal YS = GetCostIds(81, Financelist, true, 1);
decimal YF = GetCostIds(82, Financelist, false, 1);
Money = YS - YF;
}
else if (ditem == "个人借款")
{
decimal YS = GetCostIds(83, Financelist, true, 1);
decimal YF = GetCostIds(84, Financelist, false, 1);
Money = YS - YF;
}
else if (ditem == "其他代垫代收")
{
decimal YS = GetCostIds(85, Financelist, true, 1);
decimal YF = GetCostIds(86, Financelist, false, 1);
Money = YS - YF;
}
else if (ditem == "陈芳英款项")
{
decimal YS = GetCostIds(87, Financelist, true, 1);
decimal YF = GetCostIds(88, Financelist, false, 1);
Money = YS - YF;
}
else if (ditem == "小计1")
{
//投资款
decimal YS = GetCostIds(58, Financelist, true, 1);
decimal YF = GetCostIds(59, Financelist, false, 1);
Money = YS - YF;
//利润分配
YS = GetCostIds(61, Financelist, true, 1);
YF = GetCostIds(62, Financelist, false, 1);
Money += YS - YF;
//员工红利
YS = GetCostIds(63, Financelist, true, 1);
YF = GetCostIds(64, Financelist, false, 1);
Money += YS - YF;
}
else if (ditem == "小计2")
{
//预付款(教育)
decimal YS = GetCostIds(73, Financelist, true, 1);
decimal YF = GetCostIds(74, Financelist, false, 1);
Money += YS - YF;
//预付款(保证金.押金)
YS = GetCostIds(75, Financelist, true, 1);
YF = GetCostIds(76, Financelist, false, 1);
Money += YS - YF;
}
else if (ditem == "小计3")
{
//备用金
decimal YS = GetCostIds(81, Financelist, true, 1);
decimal YF = GetCostIds(82, Financelist, false, 1);
Money = YS - YF;
//个人借款
YS = GetCostIds(83, Financelist, true, 1);
YF = GetCostIds(84, Financelist, false, 1);
Money += YS - YF;
//其他代垫代收
YS = GetCostIds(85, Financelist, true, 1);
YF = GetCostIds(86, Financelist, false, 1);
Money += YS - YF;
//陈芳英款项
YS = GetCostIds(87, Financelist, true, 1);
YF = GetCostIds(88, Financelist, false, 1);
Money += YS - YF;
}
else if (ditem == "以下项均不计入计算")
{
Money = 0;
}
if (item == "01")
{
January = Money;
}
else if (item == "02")
{
February = Money;
}
else if (item == "03")
{
March = Money;
}
else if (item == "04")
{
April = Money;
}
else if (item == "05")
{
May = Money;
}
else if (item == "06")
{
June = Money;
}
else if (item == "07")
{
July = Money;
}
else if (item == "08")
{
August = Money;
}
else if (item == "09")
{
September = Money;
}
else if (item == "10")
{
October = Money;
}
else if (item == "11")
{
November = Money;
}
else if (item == "12")
{
December = Money;
}
#endregion
Console.WriteLine(branchitem.SName + "：" + Year + "年" + item + "月" + "费用名称：" + ditem + "金额：" + Money);
}
var simpleReport = new RB_SimpleReport()
{
Year = Year,
Abstract = ditem,
January = January,
February = February,
March = March,
FirstQuarter = January + February + March,
April = April,
May = May,
June = June,
SecondQuarter = April + May + June,
HalfReport = January + February + March + April + May + June,
July = July,
August = August,
September = September,
ThirdQuarter = July + August + September,
October = October,
November = November,
December = December,
FourthQuarter = October + November + December,
LastHalfReport = July + August + September + October + November + December,
Total = January + February + March + April + May + June + July + August + September + October + November + December,
Sort = i,
Rb_Group_Id = GroupId,
BranchId = branchitem.SId
};
i++;
edu_simpleReportRepository.Insert(simpleReport);
}
}
int j = 1;
var oldALLlist = edu_simpleReportRepository.GetList(new RB_SimpleReport_Extend() { Year = Year, Rb_Group_Id = GroupId, BranchId = -1 });
edu_simpleReportRepository.DeleteBatch(oldALLlist);
foreach (var FYLXitem in FYLXList)
{
//汇总
var simpleReportList = edu_simpleReportRepository.GetList(new RB_SimpleReport_Extend() { Year = Year, Abstract = FYLXitem, Rb_Group_Id = GroupId }).Where(x => x.BranchId > -1).ToList();
var simpleReport = new RB_SimpleReport
{
Year = Year,
Abstract = FYLXitem.ToString(),
January = simpleReportList.Where(x => x.Abstract == FYLXitem.ToString()).Sum(x => x.January),
February = simpleReportList.Where(x => x.Abstract == FYLXitem.ToString()).Sum(x => x.February),
March = simpleReportList.Where(x => x.Abstract == FYLXitem.ToString()).Sum(x => x.March),
FirstQuarter = simpleReportList.Where(x => x.Abstract == FYLXitem.ToString()).Sum(x => x.FirstQuarter),
April = simpleReportList.Where(x => x.Abstract == FYLXitem.ToString()).Sum(x => x.April),
May = simpleReportList.Where(x => x.Abstract == FYLXitem.ToString()).Sum(x => x.May),
June = simpleReportList.Where(x => x.Abstract == FYLXitem.ToString()).Sum(x => x.June),
SecondQuarter = simpleReportList.Where(x => x.Abstract == FYLXitem.ToString()).Sum(x => x.SecondQuarter),
HalfReport = simpleReportList.Where(x => x.Abstract == FYLXitem.ToString()).Sum(x => x.HalfReport),
July = simpleReportList.Where(x => x.Abstract == FYLXitem.ToString()).Sum(x => x.July),
August = simpleReportList.Where(x => x.Abstract == FYLXitem.ToString()).Sum(x => x.August),
September = simpleReportList.Where(x => x.Abstract == FYLXitem.ToString()).Sum(x => x.September),
ThirdQuarter = simpleReportList.Where(x => x.Abstract == FYLXitem.ToString()).Sum(x => x.ThirdQuarter),
October = simpleReportList.Where(x => x.Abstract == FYLXitem.ToString()).Sum(x => x.October),
November = simpleReportList.Where(x => x.Abstract == FYLXitem.ToString()).Sum(x => x.November),
December = simpleReportList.Where(x => x.Abstract == FYLXitem.ToString()).Sum(x => x.December),
FourthQuarter = simpleReportList.Where(x => x.Abstract == FYLXitem.ToString()).Sum(x => x.FourthQuarter),
LastHalfReport = simpleReportList.Where(x => x.Abstract == FYLXitem.ToString()).Sum(x => x.LastHalfReport),
Total = simpleReportList.Where(x => x.Abstract == FYLXitem.ToString()).Sum(x => x.Total),
Sort = j,
Rb_Group_Id = GroupId,
BranchId = -1
};
j++;
Console.WriteLine("总计：" + FYLXitem + "金额：" + simpleReportList.Where(x => x.Abstract == FYLXitem.ToString()).Sum(x => x.January));
edu_simpleReportRepository.Insert(simpleReport);
}
var fumodel = edu_UpdateRecordRepository.GetList(new RB_Report_UpdateRecord() { Type = 2 }).FirstOrDefault();
if (fumodel != null)
{
fumodel.EndDate = DateTime.Now;
edu_UpdateRecordRepository.Update(fumodel);
}
return true;
}
catch (Exception ex)
{
LogHelper.Write(ex, "InsertBatchSimple");
}
return true;
}
/// <summary>
/// 新版本更新简易报表
/// </summary>
/// <param name="GroupId"></param>
/// <param name="Year"></param>
/// <returns></returns>
public bool InsertBatchSimple_V2(int GroupId, int Year)
{
edu_UpdateRecordRepository.Insert(new RB_Report_UpdateRecord()
{
Type = 2,
StartDate = DateTime.Now
});
try
{
string sDate = Year + "-01-01";
string eDate = DateTime.Now.AddYears(1).AddDays(-1).ToString("yyyy-MM-dd");
#region 查询费用
var ctList = costtypeRepository.GetList(new RB_Costtype_Extend() { RB_Group_Id = GroupId });
//查询出分组的费用
List<int> groupList = ctList.Where(x => x.ReportType > 0).Select(x => x.ID).ToList();
//再查询出该下面 所有最下级的费用 需是进入报表统计的
List<ReportGroupModel> RGList = new List<ReportGroupModel>();
foreach (var item in groupList)
{
var nmodel = ctList.Where(x => x.ID == item).FirstOrDefault();
ReportGroupModel rgModel = new ReportGroupModel()
{
CostTypeId = item,
Name = nmodel.Name,
ReportType = nmodel.ReportType,
IncomeType = nmodel.Name.Contains("收入") ? 1 : 2,
Sort = nmodel.Sort ?? 0,
ChildList = new List<ReportChildModel>()
};
var qlist = ctList.Where(x => x.Is_Report == 1 && x.ParentId == item).ToList();
foreach (var qitem in qlist)
{
var qctList = ctList.Where(x => x.Is_Report == 1 && x.ParentId == qitem.ID).ToList();
if (qctList.Any())
{
rgModel.ChildList.AddRange(qctList.Select(x => new ReportChildModel()
{
CostTypeId = x.ID,
Name = x.Name,
Sort = x.Sort ?? 0
}));
}
else
{
rgModel.ChildList.Add(new ReportChildModel()
{
CostTypeId = qitem.ID,
Name = qitem.Name,
Sort = qitem.Sort ?? 0
});
}
}
RGList.Add(rgModel);
}
#endregion
//财务单据
var RZFinancelist = RB_FinanceRepository.GetListForReport(new RB_Finance_Extend() { sAduitDate = DateTime.Parse(sDate), eAduitDate = DateTime.Now.AddYears(1).AddDays(-1), RB_Branch_Id = -1, RB_Group_Id = GroupId, Is_TCID = 1, TCID = -1, FinanceType = 2 });
//获取资金调拨单据
var CapitalAllocationFinanceList = RB_FinanceRepository.GetTradeWayForCapitalAllocation(new RB_Finance_Extend() { sAduitDate = DateTime.Parse(sDate), eAduitDate = DateTime.Now.AddYears(1).AddDays(-1), RB_Branch_Id = -1, RB_Group_Id = GroupId, Is_TCID = 1, TCID = -1 });
//查询校区
var schoolList = schoolRepository.GetSchoolListRepository(new Model.ViewModel.User.RB_School_ViewModel() { Group_Id = GroupId });
List<string> shareList = new List<string>() { "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12" };
List<RB_SimpleReport> AllSchoolReportList = new List<RB_SimpleReport>();
foreach (var branchitem in schoolList)
{
Console.WriteLine("校区：" + branchitem.SName);
List<RB_SimpleReport> SchoolReportList = new List<RB_SimpleReport>();
#region 班级统计
RB_SimpleReport IncomeReport = new RB_SimpleReport() { Year = Year, Abstract = "营业收入", Rb_Group_Id = GroupId, Sort = 1, BranchId = branchitem.SId, CostTypeIds = "" };
RB_SimpleReport OtherIncomeReport = new RB_SimpleReport() { Year = Year, Abstract = "其他收入", Rb_Group_Id = GroupId, Sort = 2, BranchId = branchitem.SId, CostTypeIds = "" };
RB_SimpleReport BonusReport = new RB_SimpleReport() { Year = Year, Abstract = "课时费", Rb_Group_Id = GroupId, Sort = 3, BranchId = branchitem.SId, CostTypeIds = "" };
RB_SimpleReport PerfReport = new RB_SimpleReport() { Year = Year, Abstract = "老师绩效", Rb_Group_Id = GroupId, Sort = 4, BranchId = branchitem.SId, CostTypeIds = "" };
RB_SimpleReport SellReport = new RB_SimpleReport() { Year = Year, Abstract = "销售提成", Rb_Group_Id = GroupId, Sort = 5, BranchId = branchitem.SId, CostTypeIds = "" };
RB_SimpleReport OtherCostReport = new RB_SimpleReport() { Year = Year, Abstract = "其他成本", Rb_Group_Id = GroupId, Sort = 6, BranchId = branchitem.SId, CostTypeIds = "" };
RB_SimpleReport TotalReport = new RB_SimpleReport() { Year = Year, Abstract = "营业总毛利", Rb_Group_Id = GroupId, Sort = 7, BranchId = branchitem.SId, CostTypeIds = "" };
foreach (var month in shareList)
{
string startTime = Year + "-" + month + "-01";
string endTime = Convert.ToDateTime(Year + "-" + month + "-01").AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
#region 最新班级
List<int> ClassIdArr = new List<int>();
//首先根据签到表 查询出所有的班级 并分页
var list = class_CheckRepository.GetAllClassNowMonthStatistics(startTime, endTime, 0, "", branchitem.SId, GroupId);
if (list.Any())
{
ClassIdArr.AddRange(list.Select(x => x.ClassId));
}
//查询时间范围内 除订单外 班级下其他订单 的其他收入 + 其他成本支出
string datebaseStr = Config.ReadConfigKey("EduDateBase");//教育数据库
var financeList = RB_FinanceRepository.GetListForReportClass(new RB_Finance_Extend() { RB_Group_Id = GroupId, RB_Branch_Id = branchitem.SId, TCID = -2, sAduitDate = Convert.ToDateTime(startTime), eAduitDate = Convert.ToDateTime(endTime), FinanceType = 2, OrderSource = OrderResourceEnum.Education }, datebaseStr);
if (financeList.Any())
{
ClassIdArr.AddRange(financeList.Select(x => x.TCID ?? 0));
}
string STime = Convert.ToDateTime(startTime).AddMonths(-1).ToString("yyyy-MM-dd");
string ETime = Convert.ToDateTime(endTime).AddMonths(-1).ToString("yyyy-MM-dd");
var teacherBonusList = teaching_BonusDetailRepository.GetTeacherBonusForDate(GroupId, branchitem.SId, "", STime, ETime);//查询所有班级需要发放
if (teacherBonusList.Any())
{
ClassIdArr.AddRange(teacherBonusList.Select(x => x.ClassId));
}
var meritsProfitList = teaching_PerfRepository.GetTeacherPerfForDate(GroupId, branchitem.SId, "", startTime, endTime);
if (meritsProfitList.Any())
{
ClassIdArr.AddRange(meritsProfitList.Select(x => x.ClassId));
}
var saleList = sell_Commission_DetailsRepository.GetSellCommissionForDate(GroupId, branchitem.SId, "", STime, ETime);
if (saleList.Any())
{
ClassIdArr.AddRange(saleList.Select(x => x.ClassId));
}
ClassIdArr = ClassIdArr.Distinct().ToList();
List<RB_Class_ViewModel> CAlllist = new List<RB_Class_ViewModel>();
if (ClassIdArr.Any())
{
//再次查询所有的班级
CAlllist = classRepository.GetClassListRepository(new RB_Class_ViewModel() { Group_Id = GroupId, Q_ClassIds = string.Join(",", ClassIdArr) });
}
if (CAlllist.Any())
{
string classIds = string.Join(",", CAlllist.Select(x => x.ClassId));
//查询时间范围内 所有班级的学生上课金额
List<RB_Teaching_BonusDetail_ViewModel> StudentHoursList = new List<RB_Teaching_BonusDetail_ViewModel>();
//根据学生签到表 查询
var stulist = class_CheckRepository.GetStudentConsumptionHoursDetialList(0, -1, 0, startTime, endTime, "", 0, "", GroupId, classIds);
if (stulist.Any())
{
foreach (var item in stulist)
{
decimal UnitPrice = Math.Round(item.UnitPrice, 6, MidpointRounding.AwayFromZero);
StudentHoursList.Add(new RB_Teaching_BonusDetail_ViewModel
{
Date = item.ClassDate,
TimeBucket = item.StartDate + "~" + item.EndDate,
TeacherId = item.OrderGuestId,
TeacherName = item.GuestName,
ClassId = item.ClassId,
ClassName = item.ClassName,
ClassNo = item.ClassNo,
CouseId = item.CouseId,
CourseName = item.CourseName,
KSNum = item.CurrentDeductionHours,
Hours = 0,
HourMoney = 0,
UnitPrice = UnitPrice,
Money = Math.Round(item.UnitPrice * item.CurrentDeductionHours, 6, MidpointRounding.AwayFromZero)
});
}
}
foreach (var item in CAlllist)
{
#region 学生课耗
var slist = StudentHoursList.Where(x => x.ClassId == item.ClassId).ToList();
decimal TStuMoney = slist.Sum(x => x.Money);//上课总金额
#endregion
#region 提成
decimal BonusMoney = teacherBonusList.Where(x => x.ClassId == item.ClassId).FirstOrDefault()?.Money ?? 0;//老师课时费
decimal PerfMoney = meritsProfitList.Where(x => x.ClassId == item.ClassId).FirstOrDefault()?.Money ?? 0;//老师绩效
decimal SellMoney = saleList.Where(x => x.ClassId == item.ClassId).FirstOrDefault()?.CurrentPeriodMoney ?? 0;//销售提成
#endregion
#region 单据
var flist = financeList.Where(x => x.TCID == item.ClassId).ToList();
decimal OtherIncome = flist.Where(y => y.Type == WFTempLateClassEnum.IN && y.Is_Cashier == 1 && y.OrderID <= 0).Sum(x => x.Money ?? 0);
decimal Fee = flist.Where(x => x.Type == WFTempLateClassEnum.IN && x.Is_Cashier == 1 && x.OrderID <= 0 && x.Fee > 0).GroupBy(x => new { x.FrID, x.Fee }).Sum(x => x.Key.Fee ?? 0);
OtherIncome -= Fee;
decimal OtherCost = flist.Where(y => y.Type == WFTempLateClassEnum.OUT && y.OrderID <= 0).Sum(x => x.Money ?? 0);//支出也要排除订单订单退款
#endregion
UpdateClassReportDate(TotalReport, IncomeReport, OtherIncomeReport, BonusReport, PerfReport, SellReport, OtherCostReport, month, TStuMoney, BonusMoney, PerfMoney, SellMoney, OtherIncome, OtherCost);
}
}
#endregion
#region 最新留学
//查询留学的
List<int> SourceIdArr = new List<int>();
var lxOrderList = orderRepository.GetListForReport(new RB_Order_ViewModel() { Group_Id = GroupId, School_Id = branchitem.SId, OrderType = Common.Enum.Sale.OrderTypeEnum.StudyabroadOrder, Q_OrderState = 1, ConfirmSTime = startTime, ConfirmETime = endTime });
if (lxOrderList.Any())
{
SourceIdArr.AddRange(lxOrderList.Select(x => x.SourceId).Distinct());
}
//财务单据 付款单制单那月就统计进那月， 王姐的需求 因为这个是按照月份分摊的
var lxFinanceList = RB_FinanceRepository.GetListForReportClass(new RB_Finance_Extend() { RB_Group_Id = GroupId, RB_Branch_Id = branchitem.SId, TCID = -3, sAduitDate = Convert.ToDateTime(startTime), eAduitDate = Convert.ToDateTime(endTime), FinanceType = 2, OrderSource = OrderResourceEnum.EducationStudy }, datebaseStr);
if (lxFinanceList.Any())
{
SourceIdArr.AddRange(lxFinanceList.Select(x => x.TCID ?? 0));
}
List<RB_Order_Guest_ViewModel> lxGuestList = new List<RB_Order_Guest_ViewModel>();
List<Model.ViewModel.StudyAbroad.RB_StudyAbroad_ViewModel> saList = new List<Model.ViewModel.StudyAbroad.RB_StudyAbroad_ViewModel>();
if (SourceIdArr.Any())
{
if (lxOrderList.Any())
{
string orderIds = string.Join(",", lxOrderList.Select(x => x.OrderId));
//查询学生
lxGuestList = order_GuestRepository.GetOrderGuestListRepository(new RB_Order_Guest_ViewModel() { Group_Id = GroupId, OrderIds = orderIds });
}
string saIds = string.Join(",", SourceIdArr.Distinct());
//查询留学产品
saList = studyAbroadRepository.GetStudyAbroadListExtRepository(new Model.ViewModel.StudyAbroad.RB_StudyAbroad_ViewModel() { Group_Id = GroupId, QIds = saIds });
}
foreach (var item in saList)
{
#region 订单
var olist = lxOrderList.Where(x => x.SourceId == item.Id).ToList();
decimal ShiShou = olist.Sum(item => item.Income - item.Refund + item.PlatformTax);
#endregion
#region 财务单据
var lxflist = lxFinanceList.Where(x => x.TCID == item.Id).ToList();
//排序订单单据
var cfinanceList = lxflist.Where(x => x.OrderID == 0 && x.Type == WFTempLateClassEnum.IN).ToList();
decimal QTShouRu = cfinanceList.Where(y => y.Is_Cashier == 1).Sum(x => x.Money ?? 0);
QTShouRu -= cfinanceList.Where(x => x.Is_Cashier == 1 && x.Fee > 0).GroupBy(x => new { x.FrID, x.Fee }).Sum(x => x.Key.Fee ?? 0);
//支出
decimal TiCheng = (olist != null && olist.Any()) ? olist.Where(x => x.IsCommissionGive == 1).Sum(x => x.CommissionMoney + x.ExtraCommissionMoney) : 0;//销售提成 -留学提成暂定
decimal ClassFee = 0;//老师提成 -留学暂定
decimal JiXiaoMoney = 0;//绩效提成 -留学暂定
var ofinanceList = lxflist.Where(x => (x.OrderID == 0 || (x.OrderID > 0 && x.OtherType == 45)) && x.Type == WFTempLateClassEnum.OUT).ToList();
decimal QTZhiChu = ofinanceList.Sum(x => x.Money ?? 0);
#endregion
UpdateClassReportDate(TotalReport, IncomeReport, OtherIncomeReport, BonusReport, PerfReport, SellReport, OtherCostReport, month, ShiShou, ClassFee, JiXiaoMoney, TiCheng, QTShouRu, QTZhiChu);
}
#endregion
}
Console.WriteLine("班级OK");
#endregion
#region 费用模块
var FYList = RGList.Where(x => x.ReportType == 1).OrderBy(x => x.Sort).ThenBy(x => x.CostTypeId).ToList();
List<RB_SimpleReport> FYReport = new List<RB_SimpleReport>();
//初始赋值营业总毛利
RB_SimpleReport fTotalReport = new RB_SimpleReport()
{
Year = Year,
Abstract = "营业利润(亏损)",
Rb_Group_Id = GroupId,
Sort = 0,
BranchId = branchitem.SId,
CostTypeIds = "",
April = TotalReport.April,
August = TotalReport.August,
December = TotalReport.December,
February = TotalReport.February,
FirstQuarter = TotalReport.FourthQuarter,
FourthQuarter = TotalReport.FourthQuarter,
HalfReport = TotalReport.HalfReport,
January = TotalReport.January,
July = TotalReport.July,
June = TotalReport.June,
LastHalfReport = TotalReport.LastHalfReport,
March = TotalReport.March,
May = TotalReport.May,
November = TotalReport.November,
October = TotalReport.October,
SecondQuarter = TotalReport.SecondQuarter,
September = TotalReport.September,
ThirdQuarter = TotalReport.ThirdQuarter,
Total = TotalReport.Total
};//管销费用
int i = 8;
foreach (var gitem in FYList)
{
RB_SimpleReport groupReport = new RB_SimpleReport() { Year = Year, Abstract = gitem.Name + "小计", Rb_Group_Id = GroupId, Sort = 0, BranchId = branchitem.SId, CostTypeIds = string.Join(",", gitem.ChildList.Select(x => x.CostTypeId)) };
foreach (var qitem in gitem.ChildList.OrderBy(x=>x.Sort).ThenBy(x=>x.CostTypeId)) {
//子集具体费用
RB_SimpleReport fReport = new RB_SimpleReport() { Year = Year, Abstract = qitem.Name, Rb_Group_Id = GroupId, Sort = i, BranchId = branchitem.SId, CostTypeIds = qitem.CostTypeId.ToString() };
foreach (var month in shareList)
{
sDate = Convert.ToDateTime(Year + "-" + month + "-01").ToString("yyyy-MM-dd");
eDate = DateTime.Parse((Year + "-" + month + "-01")).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
decimal FYMoney = 0;
#region 日常费用
//当月的财务单据
var Financelist = RZFinancelist.Where(x => x.CostTypeId == qitem.CostTypeId && ((x.Type == WFTempLateClassEnum.OUT && x.CreateDate.Value.ToString("yyyy-MM") == Convert.ToDateTime(sDate).ToString("yyyy-MM")) || (x.Type != WFTempLateClassEnum.OUT && x.TradeDate.Value.ToString("yyyy-MM") == Convert.ToDateTime(sDate).ToString("yyyy-MM"))) && x.RB_Branch_Id == branchitem.SId).ToList();
//获取收入和支出
var cfinanceList = Financelist.Where(x => x.Type == WFTempLateClassEnum.IN).ToList();
decimal ShouRu = cfinanceList.Where(y => y.Is_Cashier == 1).Sum(x => x.Money ?? 0);
ShouRu -= cfinanceList.Where(x => x.Is_Cashier == 1 && x.Fee > 0).GroupBy(x => new { x.FrID, x.Fee }).Sum(x => x.Key.Fee ?? 0);
//支出
var ofinanceList = Financelist.Where(x => x.Type == WFTempLateClassEnum.OUT).ToList();
decimal ZhiChu = ofinanceList.Sum(x => x.Money ?? 0);
if (gitem.IncomeType == 2)
{
FYMoney = ZhiChu - ShouRu;
}
else {
FYMoney = ShouRu - ZhiChu;
}
#endregion
#region 预付款处理
if (qitem.Name.Contains("预付"))
{
//预付款 冲抵
var MatchList = Financelist.Where(x => x.MatchMoney > 0).ToList();
if (MatchList.Any())
{
//减去冲抵的金额
var newFList = MatchList.Select(x => x.FrID).Distinct().ToList();
decimal DelMoney = 0;
foreach (var item in newFList)
{
DelMoney += MatchList.Where(x => x.FrID == item).FirstOrDefault()?.MatchMoney ?? 0;
}
FYMoney -= DelMoney;
}
}
#endregion
#region 资金调拨汇兑损益
if (qitem.Name == "汇兑损益")
{
var calist = CapitalAllocationFinanceList.Where(x => x.AllotDate.Value.ToString("yyyy-MM") == Convert.ToDateTime(sDate).ToString("yyyy-MM") && x.BranchId == branchitem.SId).ToList();
//资金调拨的汇兑损益
decimal iMoney = calist.Where(x => x.AllotType == 1)?.Sum(x => x.Money ?? 0) ?? 0;
decimal oMoney = calist.Where(x => x.AllotType == 2)?.Sum(x => x.Money ?? 0) ?? 0;
FYMoney += oMoney - iMoney;
}
#endregion
switch (month) {
case "01":
fReport.January = FYMoney; groupReport.January += FYMoney; if (gitem.IncomeType == 1) fTotalReport.January += FYMoney; else fTotalReport.January -= FYMoney;
break;
case "02":
fReport.February = FYMoney; groupReport.February += FYMoney; if (gitem.IncomeType == 1) fTotalReport.February += FYMoney; else fTotalReport.February -= FYMoney;
break;
case "03":
fReport.March = FYMoney; groupReport.March += FYMoney; if (gitem.IncomeType == 1) fTotalReport.March += FYMoney; else fTotalReport.March -= FYMoney;
break;
case "04":
fReport.April = FYMoney; groupReport.April += FYMoney; if (gitem.IncomeType == 1) fTotalReport.April += FYMoney; else fTotalReport.April -= FYMoney;
break;
case "05":
fReport.May = FYMoney; groupReport.May += FYMoney; if (gitem.IncomeType == 1) fTotalReport.May += FYMoney; else fTotalReport.May -= FYMoney;
break;
case "06":
fReport.June = FYMoney; groupReport.June += FYMoney; if (gitem.IncomeType == 1) fTotalReport.June += FYMoney; else fTotalReport.June -= FYMoney;
break;
case "07":
fReport.July = FYMoney; groupReport.July += FYMoney; if (gitem.IncomeType == 1) fTotalReport.July += FYMoney; else fTotalReport.July -= FYMoney;
break;
case "08":
fReport.August = FYMoney; groupReport.August += FYMoney; if (gitem.IncomeType == 1) fTotalReport.August += FYMoney; else fTotalReport.August -= FYMoney;
break;
case "09":
fReport.September = FYMoney; groupReport.September += FYMoney; if (gitem.IncomeType == 1) fTotalReport.September += FYMoney; else fTotalReport.September -= FYMoney;
break;
case "10":
fReport.October = FYMoney; groupReport.October += FYMoney; if (gitem.IncomeType == 1) fTotalReport.October += FYMoney; else fTotalReport.October -= FYMoney;
break;
case "11":
fReport.November = FYMoney; groupReport.November += FYMoney; if (gitem.IncomeType == 1) fTotalReport.November += FYMoney; else fTotalReport.November -= FYMoney;
break;
case "12":
fReport.December = FYMoney; groupReport.December += FYMoney; if (gitem.IncomeType == 1) fTotalReport.December += FYMoney; else fTotalReport.December -= FYMoney;
break;
}
}
i++;
FYReport.Add(fReport);
}
groupReport.Sort = i; i++;
FYReport.Add(groupReport);
}
fTotalReport.Sort = i; i++;
FYReport.Add(fTotalReport);
Console.WriteLine("费用OK");
RB_SimpleReport NotReport = new RB_SimpleReport() { Year = Year, Abstract = "以下项均不计入计算", Rb_Group_Id = GroupId, Sort = 0, BranchId = branchitem.SId, CostTypeIds = "" };
NotReport.Sort = i; i++;
#endregion
#region 往来模块
var WLList = RGList.Where(x => x.ReportType == 2).OrderBy(x => x.Sort).ThenBy(x => x.CostTypeId).ToList();
List<RB_SimpleReport> WLReport = new List<RB_SimpleReport>();
foreach (var gitem in WLList)
{
RB_SimpleReport groupReport = new RB_SimpleReport() { Year = Year, Abstract = gitem.Name + "小计", Rb_Group_Id = GroupId, Sort = 0, BranchId = branchitem.SId, CostTypeIds = string.Join(",", gitem.ChildList.Select(x => x.CostTypeId)) };
foreach (var qitem in gitem.ChildList.OrderBy(x => x.Sort).ThenBy(x => x.CostTypeId))
{
//子集具体费用
RB_SimpleReport fReport = new RB_SimpleReport() { Year = Year, Abstract = qitem.Name, Rb_Group_Id = GroupId, Sort = i, BranchId = branchitem.SId, CostTypeIds = qitem.CostTypeId.ToString() };
foreach (var month in shareList)
{
sDate = Convert.ToDateTime(Year + "-" + month + "-01").ToString("yyyy-MM-dd");
eDate = DateTime.Parse((Year + "-" + month + "-01")).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
decimal FYMoney = 0;
#region 日常费用
//当月的财务单据
var Financelist = RZFinancelist.Where(x => x.CostTypeId == qitem.CostTypeId && ((x.Type == WFTempLateClassEnum.OUT && x.CreateDate.Value.ToString("yyyy-MM") == Convert.ToDateTime(sDate).ToString("yyyy-MM")) || (x.Type != WFTempLateClassEnum.OUT && x.TradeDate.Value.ToString("yyyy-MM") == Convert.ToDateTime(sDate).ToString("yyyy-MM"))) && x.RB_Branch_Id == branchitem.SId).ToList();
//获取收入和支出
var cfinanceList = Financelist.Where(x => x.Type == WFTempLateClassEnum.IN).ToList();
decimal ShouRu = cfinanceList.Where(y => y.Is_Cashier == 1).Sum(x => x.Money ?? 0);
ShouRu -= cfinanceList.Where(x => x.Is_Cashier == 1 && x.Fee > 0).GroupBy(x => new { x.FrID, x.Fee }).Sum(x => x.Key.Fee ?? 0);
//支出
var ofinanceList = Financelist.Where(x => x.Type == WFTempLateClassEnum.OUT).ToList();
decimal ZhiChu = ofinanceList.Sum(x => x.Money ?? 0);
if (gitem.IncomeType == 2)
{
FYMoney = ZhiChu - ShouRu;
}
else
{
FYMoney = ShouRu - ZhiChu;
}
#endregion
#region 预付款处理
if (qitem.Name.Contains("预付"))
{
//预付款 冲抵
var MatchList = Financelist.Where(x => x.MatchMoney > 0).ToList();
if (MatchList.Any())
{
//减去冲抵的金额
var newFList = MatchList.Select(x => x.FrID).Distinct().ToList();
decimal DelMoney = 0;
foreach (var item in newFList)
{
DelMoney += MatchList.Where(x => x.FrID == item).FirstOrDefault()?.MatchMoney ?? 0;
}
FYMoney -= DelMoney;
}
}
#endregion
#region 资金调拨汇兑损益
if (qitem.Name == "汇兑损益")
{
var calist = CapitalAllocationFinanceList.Where(x => x.AllotDate.Value.ToString("yyyy-MM") == Convert.ToDateTime(sDate).ToString("yyyy-MM") && x.BranchId == branchitem.SId).ToList();
//资金调拨的汇兑损益
decimal iMoney = calist.Where(x => x.AllotType == 1)?.Sum(x => x.Money ?? 0) ?? 0;
decimal oMoney = calist.Where(x => x.AllotType == 2)?.Sum(x => x.Money ?? 0) ?? 0;
FYMoney += oMoney - iMoney;
}
#endregion
RB_SimpleReport cfReport = new RB_SimpleReport();
switch (month)
{
case "01":
fReport.January = FYMoney; groupReport.January += FYMoney;
break;
case "02":
fReport.February = FYMoney; groupReport.February += FYMoney;
break;
case "03":
fReport.March = FYMoney; groupReport.March += FYMoney;
break;
case "04":
fReport.April = FYMoney; groupReport.April += FYMoney;
break;
case "05":
fReport.May = FYMoney; groupReport.May += FYMoney;
break;
case "06":
fReport.June = FYMoney; groupReport.June += FYMoney;
break;
case "07":
fReport.July = FYMoney; groupReport.July += FYMoney;
break;
case "08":
fReport.August = FYMoney; groupReport.August += FYMoney;
break;
case "09":
fReport.September = FYMoney; groupReport.September += FYMoney;
break;
case "10":
fReport.October = FYMoney; groupReport.October += FYMoney;
break;
case "11":
fReport.November = FYMoney; groupReport.November += FYMoney;
break;
case "12":
fReport.December = FYMoney; groupReport.December += FYMoney;
break;
}
}
i++;
WLReport.Add(fReport);
}
groupReport.Sort = i; i++;
WLReport.Add(groupReport);
}
Console.WriteLine("往来OK");
#endregion
#region 其他模块
var QTList = RGList.Where(x => x.ReportType == 3).OrderBy(x => x.Sort).ThenBy(x => x.CostTypeId).ToList();
List<RB_SimpleReport> QTReport = new List<RB_SimpleReport>();
foreach (var gitem in QTList)
{
RB_SimpleReport groupReport = new RB_SimpleReport() { Year = Year, Abstract = gitem.Name + "小计", Rb_Group_Id = GroupId, Sort = 0, BranchId = branchitem.SId, CostTypeIds = string.Join(",", gitem.ChildList.Select(x => x.CostTypeId)) };
foreach (var qitem in gitem.ChildList.OrderBy(x => x.Sort).ThenBy(x => x.CostTypeId))
{
//子集具体费用
RB_SimpleReport fReport = new RB_SimpleReport() { Year = Year, Abstract = qitem.Name, Rb_Group_Id = GroupId, Sort = i, BranchId = branchitem.SId, CostTypeIds = qitem.CostTypeId.ToString() };
foreach (var month in shareList)
{
sDate = Convert.ToDateTime(Year + "-" + month + "-01").ToString("yyyy-MM-dd");
eDate = DateTime.Parse((Year + "-" + month + "-01")).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
decimal FYMoney = 0;
#region 日常费用
//当月的财务单据
var Financelist = RZFinancelist.Where(x => x.CostTypeId == qitem.CostTypeId && ((x.Type == WFTempLateClassEnum.OUT && x.CreateDate.Value.ToString("yyyy-MM") == Convert.ToDateTime(sDate).ToString("yyyy-MM")) || (x.Type != WFTempLateClassEnum.OUT && x.TradeDate.Value.ToString("yyyy-MM") == Convert.ToDateTime(sDate).ToString("yyyy-MM"))) && x.RB_Branch_Id == branchitem.SId).ToList();
//获取收入和支出
var cfinanceList = Financelist.Where(x => x.Type == WFTempLateClassEnum.IN).ToList();
decimal ShouRu = cfinanceList.Where(y => y.Is_Cashier == 1).Sum(x => x.Money ?? 0);
ShouRu -= cfinanceList.Where(x => x.Is_Cashier == 1 && x.Fee > 0).GroupBy(x => new { x.FrID, x.Fee }).Sum(x => x.Key.Fee ?? 0);
//支出
var ofinanceList = Financelist.Where(x => x.Type == WFTempLateClassEnum.OUT).ToList();
decimal ZhiChu = ofinanceList.Sum(x => x.Money ?? 0);
if (gitem.IncomeType == 2)
{
FYMoney = ZhiChu - ShouRu;
}
else
{
FYMoney = ShouRu - ZhiChu;
}
#endregion
#region 预付款处理
if (qitem.Name.Contains("预付"))
{
//预付款 冲抵
var MatchList = Financelist.Where(x => x.MatchMoney > 0).ToList();
if (MatchList.Any())
{
//减去冲抵的金额
var newFList = MatchList.Select(x => x.FrID).Distinct().ToList();
decimal DelMoney = 0;
foreach (var item in newFList)
{
DelMoney += MatchList.Where(x => x.FrID == item).FirstOrDefault()?.MatchMoney ?? 0;
}
FYMoney -= DelMoney;
}
}
#endregion
#region 资金调拨汇兑损益
if (qitem.Name == "汇兑损益")
{
var calist = CapitalAllocationFinanceList.Where(x => x.AllotDate.Value.ToString("yyyy-MM") == Convert.ToDateTime(sDate).ToString("yyyy-MM") && x.BranchId == branchitem.SId).ToList();
//资金调拨的汇兑损益
decimal iMoney = calist.Where(x => x.AllotType == 1)?.Sum(x => x.Money ?? 0) ?? 0;
decimal oMoney = calist.Where(x => x.AllotType == 2)?.Sum(x => x.Money ?? 0) ?? 0;
FYMoney += oMoney - iMoney;
}
#endregion
switch (month)
{
case "01":
fReport.January = FYMoney; groupReport.January += FYMoney;
break;
case "02":
fReport.February = FYMoney; groupReport.February += FYMoney;
break;
case "03":
fReport.March = FYMoney; groupReport.March += FYMoney;
break;
case "04":
fReport.April = FYMoney; groupReport.April += FYMoney;
break;
case "05":
fReport.May = FYMoney; groupReport.May += FYMoney;
break;
case "06":
fReport.June = FYMoney; groupReport.June += FYMoney;
break;
case "07":
fReport.July = FYMoney; groupReport.July += FYMoney;
break;
case "08":
fReport.August = FYMoney; groupReport.August += FYMoney;
break;
case "09":
fReport.September = FYMoney; groupReport.September += FYMoney;
break;
case "10":
fReport.October = FYMoney; groupReport.October += FYMoney;
break;
case "11":
fReport.November = FYMoney; groupReport.November += FYMoney;
break;
case "12":
fReport.December = FYMoney; groupReport.December += FYMoney;
break;
}
}
i++;
QTReport.Add(fReport);
}
groupReport.Sort = i; i++;
QTReport.Add(groupReport);
}
Console.WriteLine("其他OK");
#endregion
#region 计算统计
SchoolReportList.Add(IncomeReport);
SchoolReportList.Add(OtherIncomeReport);
SchoolReportList.Add(BonusReport);
SchoolReportList.Add(PerfReport);
SchoolReportList.Add(SellReport);
SchoolReportList.Add(OtherCostReport);
SchoolReportList.Add(TotalReport);
SchoolReportList.Add(NotReport);
SchoolReportList.AddRange(FYReport);
SchoolReportList.AddRange(WLReport);
SchoolReportList.AddRange(QTReport);
SchoolReportList.ForEach(x =>
{
x.FirstQuarter = x.January + x.February + x.March;
x.SecondQuarter = x.April + x.May + x.June;
x.ThirdQuarter = x.July + x.August + x.September;
x.FourthQuarter = x.October + x.November + x.December;
x.HalfReport = x.FirstQuarter + x.SecondQuarter;
x.LastHalfReport = x.ThirdQuarter + x.FourthQuarter;
x.Total = x.HalfReport + x.LastHalfReport;
});
AllSchoolReportList.AddRange(SchoolReportList);
#endregion
}
if (AllSchoolReportList.Any()) {
Console.WriteLine("开始汇总插入:" + AllSchoolReportList.Count());
//计算汇总
var HZList = AllSchoolReportList.GroupBy(x => new { x.Year, x.Abstract, x.Rb_Group_Id, x.Sort, x.CostTypeIds }).Select(x => new RB_SimpleReport_Extend()
{
ID = 0,
Rb_Group_Id = x.Key.Rb_Group_Id,
Year = x.Key.Year,
Abstract = x.Key.Abstract,
April = x.Sum(y => y.April),
August = x.Sum(y => y.August),
BranchId = -1,
CostTypeIds = x.Key.CostTypeIds,
December = x.Sum(y => y.December),
February = x.Sum(y => y.February),
FirstQuarter = x.Sum(y => y.FirstQuarter),
FourthQuarter = x.Sum(z => z.FourthQuarter),
HalfReport = x.Sum(z => z.HalfReport),
January = x.Sum(z => z.January),
July = x.Sum(z => z.July),
June = x.Sum(z => z.June),
LastHalfReport = x.Sum(z => z.LastHalfReport),
March = x.Sum(z => z.March),
May = x.Sum(z => z.May),
November = x.Sum(z => z.November),
October = x.Sum(z => z.October),
SecondQuarter = x.Sum(z => z.SecondQuarter),
September = x.Sum(z => z.September),
Sort = x.Key.Sort,
ThirdQuarter = x.Sum(z => z.ThirdQuarter),
Total = x.Sum(z => z.Total)
}).ToList();
AllSchoolReportList.AddRange(HZList);
var OldList = edu_simpleReportRepository.GetList(new RB_SimpleReport_Extend() { Year = Year, Rb_Group_Id = GroupId, BranchId = -2 });//查询所有的
foreach (var item in AllSchoolReportList) {
var oldModel = OldList.Where(x => x.Year == item.Year && x.Abstract == item.Abstract && x.BranchId == item.BranchId).FirstOrDefault();
if (oldModel == null)
{
edu_simpleReportRepository.Insert(item);
}
else {
item.ID = oldModel.ID;
edu_simpleReportRepository.Update(item);
oldModel.IsUpdate = 1;
}
}
var delList = OldList.Where(x => x.IsUpdate == 0).ToList();
edu_simpleReportRepository.DeleteBatch(delList);
Console.WriteLine("汇总OK");
}
var fumodel = edu_UpdateRecordRepository.GetList(new RB_Report_UpdateRecord() { Type = 2 }).FirstOrDefault();
if (fumodel != null)
{
fumodel.EndDate = DateTime.Now;
edu_UpdateRecordRepository.Update(fumodel);
}
return true;
}
catch (Exception ex)
{
LogHelper.Write(ex, "InsertBatchSimple_V2");
}
return true;
}
/// <summary>
/// 更新班级数据
/// </summary>
/// <param name="IncomeReport"></param>
/// <param name="OtherIncomeReport"></param>
/// <param name="BonusReport"></param>
/// <param name="PerfReport"></param>
/// <param name="SellReport"></param>
/// <param name="OtherCostReport"></param>
/// <param name="month"></param>
/// <param name="TStuMoney"></param>
/// <param name="BonusMoney"></param>
/// <param name="PerfMoney"></param>
/// <param name="SellMoney"></param>
/// <param name="OtherIncome"></param>
/// <param name="OtherCost"></param>
private static void UpdateClassReportDate(RB_SimpleReport TotalReport, RB_SimpleReport IncomeReport, RB_SimpleReport OtherIncomeReport, RB_SimpleReport BonusReport, RB_SimpleReport PerfReport, RB_SimpleReport SellReport, RB_SimpleReport OtherCostReport, string month, decimal TStuMoney, decimal BonusMoney, decimal PerfMoney, decimal SellMoney, decimal OtherIncome, decimal OtherCost)
{
switch (month)
{
case "01":
IncomeReport.January += TStuMoney;
OtherIncomeReport.January += OtherIncome;
BonusReport.January += BonusMoney;
PerfReport.January += PerfMoney;
SellReport.January += SellMoney;
OtherCostReport.January += OtherCost;
TotalReport.January += TStuMoney + OtherIncome - BonusMoney - PerfMoney - SellMoney - OtherCost;
break;
case "02":
IncomeReport.February += TStuMoney;
OtherIncomeReport.February += OtherIncome;
BonusReport.February += BonusMoney;
PerfReport.February += PerfMoney;
SellReport.February += SellMoney;
OtherCostReport.February += OtherCost;
TotalReport.February += TStuMoney + OtherIncome - BonusMoney - PerfMoney - SellMoney - OtherCost;
break;
case "03":
IncomeReport.March += TStuMoney;
OtherIncomeReport.March += OtherIncome;
BonusReport.March += BonusMoney;
PerfReport.March += PerfMoney;
SellReport.March += SellMoney;
OtherCostReport.March += OtherCost;
TotalReport.March += TStuMoney + OtherIncome - BonusMoney - PerfMoney - SellMoney - OtherCost;
break;
case "04":
IncomeReport.April += TStuMoney;
OtherIncomeReport.April += OtherIncome;
BonusReport.April += BonusMoney;
PerfReport.April += PerfMoney;
SellReport.April += SellMoney;
OtherCostReport.April += OtherCost;
TotalReport.April += TStuMoney + OtherIncome - BonusMoney - PerfMoney - SellMoney - OtherCost;
break;
case "05":
IncomeReport.May += TStuMoney;
OtherIncomeReport.May += OtherIncome;
BonusReport.May += BonusMoney;
PerfReport.May += PerfMoney;
SellReport.May += SellMoney;
OtherCostReport.May += OtherCost;
TotalReport.May += TStuMoney + OtherIncome - BonusMoney - PerfMoney - SellMoney - OtherCost;
break;
case "06":
IncomeReport.June += TStuMoney;
OtherIncomeReport.June += OtherIncome;
BonusReport.June += BonusMoney;
PerfReport.June += PerfMoney;
SellReport.June += SellMoney;
OtherCostReport.June += OtherCost;
TotalReport.June += TStuMoney + OtherIncome - BonusMoney - PerfMoney - SellMoney - OtherCost;
break;
case "07":
IncomeReport.July += TStuMoney;
OtherIncomeReport.July += OtherIncome;
BonusReport.July += BonusMoney;
PerfReport.July += PerfMoney;
SellReport.July += SellMoney;
OtherCostReport.July += OtherCost;
TotalReport.July += TStuMoney + OtherIncome - BonusMoney - PerfMoney - SellMoney - OtherCost;
break;
case "08":
IncomeReport.August += TStuMoney;
OtherIncomeReport.August += OtherIncome;
BonusReport.August += BonusMoney;
PerfReport.August += PerfMoney;
SellReport.August += SellMoney;
OtherCostReport.August += OtherCost;
TotalReport.August += TStuMoney + OtherIncome - BonusMoney - PerfMoney - SellMoney - OtherCost;
break;
case "09":
IncomeReport.September += TStuMoney;
OtherIncomeReport.September += OtherIncome;
BonusReport.September += BonusMoney;
PerfReport.September += PerfMoney;
SellReport.September += SellMoney;
OtherCostReport.September += OtherCost;
TotalReport.September += TStuMoney + OtherIncome - BonusMoney - PerfMoney - SellMoney - OtherCost;
break;
case "10":
IncomeReport.October += TStuMoney;
OtherIncomeReport.October += OtherIncome;
BonusReport.October += BonusMoney;
PerfReport.October += PerfMoney;
SellReport.October += SellMoney;
OtherCostReport.October += OtherCost;
TotalReport.October += TStuMoney + OtherIncome - BonusMoney - PerfMoney - SellMoney - OtherCost;
break;
case "11":
IncomeReport.November += TStuMoney;
OtherIncomeReport.November += OtherIncome;
BonusReport.November += BonusMoney;
PerfReport.November += PerfMoney;
SellReport.November += SellMoney;
OtherCostReport.November += OtherCost;
TotalReport.November += TStuMoney + OtherIncome - BonusMoney - PerfMoney - SellMoney - OtherCost;
break;
case "12":
IncomeReport.December += TStuMoney;
OtherIncomeReport.December += OtherIncome;
BonusReport.December += BonusMoney;
PerfReport.December += PerfMoney;
SellReport.December += SellMoney;
OtherCostReport.December += OtherCost;
TotalReport.December += TStuMoney + OtherIncome - BonusMoney - PerfMoney - SellMoney - OtherCost;
break;
}
}
/// <summary>
/// 获取简易报表列表
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public List<RB_SimpleReport_Extend> GetEasyReportList(RB_SimpleReport_Extend model)
{
var list = edu_simpleReportRepository.GetList(model);
return list;
}
/// <summary>
/// 获取管销费用
/// </summary>
/// <param name="financeList"></param>
/// <returns></returns>
public decimal GetGXFY(List<RB_Finance_Extend> Financelist)
{
decimal GXFY = 0;
GXFY += GetCostIds(50, Financelist, false, 1, true);
GXFY += GetCostIds(15, Financelist, false, 1, true);
GXFY += GetCostIds(16, Financelist, false, 1, true);
GXFY += GetCostIds(17, Financelist, false, 1, true);
GXFY += GetCostIds(18, Financelist, false, 1, true);
GXFY += GetCostIds(19, Financelist, false, 1, true);
GXFY += GetCostIds(20, Financelist, false, 1, true);
GXFY += GetCostIds(21, Financelist, false, 1, true);
GXFY += GetCostIds(22, Financelist, false, 1, true);
GXFY += GetCostIds(23, Financelist, false, 1, true);
GXFY += GetCostIds(52, Financelist, false, 1, true);
GXFY += GetCostIds(24, Financelist, false, 1, true);
GXFY += GetCostIds(25, Financelist, false, 1, true);
GXFY += GetCostIds(26, Financelist, false, 1, true);
GXFY += GetCostIds(27, Financelist, false, 1, true);
GXFY += GetCostIds(28, Financelist, false, 1, true);
GXFY += GetCostIds(29, Financelist, false, 1, true);
GXFY += GetCostIds(30, Financelist, false, 1, true);
GXFY += GetCostIds(31, Financelist, false, 1, true);
GXFY += GetCostIds(32, Financelist, false, 1, true);
GXFY += GetCostIds(33, Financelist, false, 1, true);
GXFY += GetCostIds(34, Financelist, false, 1, true);
GXFY += GetCostIds(35, Financelist, false, 1, true);
GXFY += GetCostIds(36, Financelist, false, 1, true);
GXFY += GetCostIds(41, Financelist, false, 1, true);
GXFY += GetCostIds(39, Financelist, false, 1, true);
GXFY += GetCostIds(40, Financelist, false, 1, true);
GXFY += GetCostIds(51, Financelist, false, 1, true);
return GXFY;
}
public decimal GetCostIds(int Type, List<RB_Finance_Extend> NewfinanceList, bool flag = false, int count = 0, bool IsRCSZ = false)
{
string ids = string.Empty;
decimal Money = 0, Fee = 0;
if (NewfinanceList != null && NewfinanceList.Count() > 0)
{
switch (Type)
{
//佣金收入
case 1:
NewfinanceList = NewfinanceList.Where(z => (z.Name.Contains("乳胶") || z.Name.Contains("御宝") || z.Name.Contains("乐天") || z.Name.Contains("茶道") || z.Name.Contains("高岛屋") || z.Name.Contains("TOKIS") || z.Name.Contains("电器LAOX") || z.Name.Contains("机场免税店") || z.Name.Contains("永山免税店") || z.Name.Contains("公社免税店") || z.Name.Contains("电器AKKYONE") || z.Name.Contains("橡胶店") || z.Name.Contains("琅勃拉邦古丝绸") || z.Name.Contains("老挝万象金沉香") || z.Name.Contains("老挝万象东盟珠宝") || z.Name.Contains("琅勃拉邦皇家乳胶") || z.Name.Contains("老挝土产") || z.Name.Contains("老挝红木") || z.Name.Contains("琅勃拉邦风情园") || z.Name.Contains("锅具") || z.Name.Contains("琅勃拉邦银寨") || z.Name.Contains("琅勃拉邦咖啡庄园") || z.Name.Contains("金占巴百草园")) && z.Type == WFTempLateClassEnum.IN).ToList();
break;
//自费收入
case 2:
NewfinanceList = NewfinanceList.Where(z => (z.Name.Contains("自费") || z.Name.Contains("自费收支结算")) && z.Type == WFTempLateClassEnum.IN).ToList();
break;
//其他收入
case -1:
var NoOtherList = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.IN && (z.Name.Contains("团费") || z.Name.Contains("小费") || z.Name.Contains("地接费（结算）") || z.Name.Contains("自费") || z.Name.Contains("自费收支结算") || z.Name.Contains("乳胶") || z.Name.Contains("御宝") || z.Name.Contains("御宝（珠宝店）") || z.Name.Contains("乐天") || z.Name.Contains("茶道") || z.Name.Contains("高岛屋") || z.Name.Contains("TOKIS") || z.Name.Contains("电器LAOX") || z.Name.Contains("机场免税店") || z.Name.Contains("永山免税店") || z.Name.Contains("公社免税店") || z.Name.Contains("电器AKKYONE") || z.Name.Contains("橡胶店") || z.Name.Contains("琅勃拉邦古丝绸") || z.Name.Contains("老挝万象金沉香") || z.Name.Contains("老挝万象东盟珠宝") || z.Name.Contains("琅勃拉邦皇家乳胶") || z.Name.Contains("老挝土产") || z.Name.Contains("老挝红木") || z.Name.Contains("琅勃拉邦风情园") || z.Name.Contains("锅具") || z.Name.Contains("琅勃拉邦银寨") || z.Name.Contains("琅勃拉邦咖啡庄园") || z.Name.Contains("金占巴百草园"))).ToList();
NewfinanceList = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.IN && !NoOtherList.Select(x => x.FrID).Contains(z.FrID)).ToList();
break;
//小费收入
case 3:
var xflist = NewfinanceList.Where(z => z.Name.Contains("小费") && z.Type == WFTempLateClassEnum.IN).ToList();
NewfinanceList = NewfinanceList.Where(x => xflist.Select(z => z.FrID).Contains(x.FrID)).ToList();
break;
//佣金+小费+自费
case 4:
NewfinanceList = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.IN && (z.Name.Contains("地接收入") || z.Name.Contains("小费") || z.Name.Contains("自费"))).ToList();
break;
//邀请函、名单表
case 5:
NewfinanceList = NewfinanceList.Where(z => (z.Name.Contains("邀请函") || z.Name.Contains("名单表")) && z.Type == WFTempLateClassEnum.OUT).ToList();
break;
//车资
case 6:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("车资") && z.Type == WFTempLateClassEnum.OUT).ToList(); break;
//国内联运机票
case 7:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("国内联运") && z.Type == WFTempLateClassEnum.OUT).ToList(); break;
//签证
case 8:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("签证") && z.Type == WFTempLateClassEnum.OUT).ToList(); break;
//其他支出
case 9:
//同时排除取消退款那种情况
var dellist = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.OUT && z.Name.Contains("取消退款")).ToList();
if (dellist != null && dellist.Count() > 0)
{
List<int> FrIdList = dellist.Select(x => x.FrID).ToList();
foreach (var item in FrIdList)
{
var delClist = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.OUT && z.Name.Contains("国际段机票") && z.FrID == item).ToList();
if (delClist != null && delClist.Count() > 0)
{
//说明这个是属于机票的取消
NewfinanceList = NewfinanceList.Where(z => z.FrID != item).ToList();
}
}
}
NewfinanceList = NewfinanceList.Where(z =>
(
z.Type == WFTempLateClassEnum.OUT && (!z.Name.Contains("邀请函") && !z.Name.Contains("名单表") && !z.Name.Contains("车资") && !z.Name.Contains("国际段机票") && !z.Name.Contains("机票罚金") && !z.Name.Contains("机票税金") && !z.Name.Contains("机票定金") && !z.Name.Contains("机票退税") && !z.Name.Contains("国际内陆段机票") && !z.Name.Contains("延时费") && !z.Name.Contains("国内联运") && !z.Name.Contains("签证") && !z.Name.Contains("地接费（领取）") && !z.Name.Contains("地接费（结算）") && !z.Name.Contains("赔偿") && !z.Name.Contains("领队佣金") && !z.Name.Contains("领队奖励金") && !z.Name.Contains("导游佣金") && !z.Name.Contains("旅游责任险") && !z.Name.Contains("保险费")))
).ToList();
break;
//地接款支出 2019-04-24 ld 徐总需求 小于0的表示收入
case 10:
NewfinanceList.Where(z => z.Name.Contains("地接费（结算）")).ToList().ForEach(z =>
{
//判断 如果是负数的地接费结算 表示是收入
if (z.Type == WFTempLateClassEnum.OUT && (z.Money ?? 0) < 0)
{
z.Type = WFTempLateClassEnum.IN;
z.Money = 0 - z.Money;
z.PayMoney = 0 - (z.PayMoney ?? 0);
z.Fee = 0 - (z.Fee ?? 0);
}
});
NewfinanceList = NewfinanceList.Where(z => (z.Name.Contains("地接费（领取）") || z.Name.Contains("地接费（结算）")) && z.Type == WFTempLateClassEnum.OUT).ToList(); break;
//地接款收入 2019-04-24 ld 徐总需求 小于0的表示收入
case 11:
NewfinanceList.Where(z => z.Name.Contains("地接费（结算）")).ToList().ForEach(z =>
{
//判断 如果是负数的地接费结算 表示是收入
if (z.Type == WFTempLateClassEnum.OUT && (z.Money ?? 0) < 0)
{
z.Type = WFTempLateClassEnum.IN;
z.Money = 0 - z.Money;
z.PayMoney = 0 - (z.PayMoney ?? 0);
z.Fee = 0 - (z.Fee ?? 0);
}
});
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("地接费（结算）") && z.Type == WFTempLateClassEnum.IN).ToList(); break;
//小费收入
case 12:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("小费") && z.Type == WFTempLateClassEnum.IN).ToList(); break;
//自费收入
case 13:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("自费") && z.Type == WFTempLateClassEnum.IN).ToList(); break;
//简易报表
//意外险
case 14:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("旅游责任险") || z.Name.Contains("保险费")).ToList(); break;
//工资
case 15:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("工资") && !z.Name.Contains("员工工资(还款)")).ToList(); break;
//业务提成
case 16:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("业务提成（签证）") || z.Name.Contains("业务提成（地接）") || z.Name.Contains("业务提成（票务）") || z.Name.Contains("人头奖励（销售）")).ToList(); break;
//社保
case 17:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("社会保障费")).ToList(); break;
//员工福利及竞赛奖金
case 18:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("福利及奖金")).ToList(); break;
//办公用品
case 19:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("办公用品")).ToList(); break;
//差旅费
case 20:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("差旅费")).ToList(); break;
//电话费(通訊費)
case 21:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("电话费") || z.Name.Contains("通讯费")).ToList(); break;
//物管水电费
case 22:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("物管水电费")).ToList(); break;
//房租
case 23:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("房租")).ToList(); break;
//广告费
case 24:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("广告费")).ToList(); break;
//业务招待
case 25:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("业务招待")).ToList(); break;
//累计折旧
case 26:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("累计折旧")).ToList(); break;
//税金
case 27:
NewfinanceList = NewfinanceList.Where(z => z.Name == "税金").ToList(); break;
//平台使用费
case 28:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("平台使用费")).ToList(); break;
//電腦系统费
case 29:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("電腦系统费") || z.Name.Contains("系统使用费")).ToList(); break;
//市内交通费
case 30:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("市内交通费")).ToList(); break;
//团队建设费
case 31:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("团队建设费")).ToList(); break;
//会务费
case 32:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("会务费")).ToList(); break;
//同行返利
case 33:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("同行返利") || z.Name.Contains("平台大红包返款") || z.Name.Contains("平台佣金")).ToList(); break;
//工会经费
case 34:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("工会经费")).ToList(); break;
//律师费
case 35:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("律师费")).ToList(); break;
//服务器管理费
case 36:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("服务器管理费")).ToList(); break;
//导游佣金
case 37:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("导游佣金")).ToList(); break;
//领队佣金
case 38:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("领队佣金") || z.Name.Contains("领队奖励金")).ToList(); break;
//航空公司保证金（会退）
case 39:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("航空公司保证金（会退）")).ToList(); break;
//航空公司大订金（会抵扣）
case 40:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("航空公司大订金（会抵扣）")).ToList(); break;
//汇款手续费
case 41:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("付款手续费") || z.Name.Contains("刷卡手续费") || z.Name.Contains("转账手续费")).ToList(); break;
//利息收入
case 42:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("利息收入")).ToList(); break;
//汇兑损益
case 43:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("汇兑损益")).ToList(); break;
//营业外收入
case 44:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("营业外收入") || z.Name.Contains("商品收入") || z.Name.Contains("运费收入") || z.Name.Contains("司导保险收入")).ToList(); break;
//其他营业支出
case 48:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("其他营业支出") || z.Name.Contains("退换货费用") || z.Name.Contains("采购成本") || z.Name.Contains("商品退款") || z.Name.Contains("税费") || z.Name.Contains("司导保险成本") || z.Name.Contains("采购退回") || z.Name.Contains("溢收款退款")).ToList(); break;
//所得税费用
case 49:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("所得税费用")).ToList(); break;
//营业税金
case 50:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("营业税金及附加")).ToList(); break;
//简易报表其他
case 51://&& !z.Name.Contains("营业外收入")
NewfinanceList = NewfinanceList.Where(z => !z.Name.Contains("所得税费用")
&& !z.Name.Contains("其他营业支出")
&& !z.Name.Contains("营业外收入")
&& !z.Name.Contains("应付款")
&& !z.Name.Contains("应收款")
&& !z.Name.Contains("汇兑损益")
&& !z.Name.Contains("利息收入")
&& (!z.Name.Contains("付款手续费") && !z.Name.Contains("刷卡手续费") && !z.Name.Contains("转账手续费"))
&& !z.Name.Contains("航空公司大订金（会抵扣）")
&& !z.Name.Contains("航空公司保证金（会退）")
&& !z.Name.Contains("领队佣金")
&& !z.Name.Contains("领队奖励金")
&& !z.Name.Contains("导游佣金")
&& !z.Name.Contains("服务器管理费")
&& !z.Name.Contains("律师费")
&& !z.Name.Contains("工会经费")
&& !z.Name.Contains("同行返利")
&& !z.Name.Contains("平台大红包返款")
&& !z.Name.Contains("会务费")
&& !z.Name.Contains("团队建设费")
&& !z.Name.Contains("市内交通费")
&& !z.Name.Contains("電腦系统费")
&& !z.Name.Contains("系统使用费")
&& !z.Name.Contains("系统费")
&& !z.Name.Contains("平台使用费")
&& z.Name != "税金"
&& !z.Name.Contains("累计折旧")
&& !z.Name.Contains("业务招待")
&& !z.Name.Contains("累计折旧")
&& !z.Name.Contains("广告费")
&& !z.Name.Contains("房租")
&& !z.Name.Contains("物管水电费")
&& !z.Name.Contains("电话费")
&& !z.Name.Contains("通讯费")
&& !z.Name.Contains("差旅费")
&& !z.Name.Contains("办公用品")
&& !z.Name.Contains("福利及奖金")
&& !z.Name.Contains("社会保障费")
&& !z.Name.Contains("业务提成")
&& !z.Name.Contains("人头奖励")
&& !z.Name.Contains("工资")
&& !z.Name.Contains("旅游责任险")
&& !z.Name.Contains("保险费")
&& !z.Name.Contains("营业税金及附加")
&& z.Type != WFTempLateClassEnum.ShouldIncome
&& z.Type != WFTempLateClassEnum.ShouldPay
&& !z.Name.Contains("投资款")
&& !z.Name.Contains("利润分配")
&& !z.Name.Contains("员工红利")
&& !z.Name.Contains("酒店预付款")
&& !z.Name.Contains("CHI包房")
&& !z.Name.Contains("预付环球门票")
&& !z.Name.Contains("门票预付款")
&& !z.Name.Contains("餐厅预付款")
&& !z.Name.Contains("车资预付款")
&& !z.Name.Contains("机票预付款")
&& !z.Name.Contains("电商预付款")
&& !z.Name.Contains("保证金")
&& !z.Name.Contains("其他保证金")
&& !z.Name.Contains("机票保证金")
&& !z.Name.Contains("押金")
&& !z.Name.Contains("员工工资(还款)")
&& !z.Name.Contains("挂账单冲抵")
&& !z.Name.Contains("机票收入")
&& !z.Name.Contains("快递费")
&& !z.Name.Contains("照片服务费")
&& !z.Name.Contains("地接OP备用金")
&& !z.Name.Contains("领队罚金")
&& !z.Name.Contains("景点门票")
&& !z.Name.Contains("用房手配费")
&& !z.Name.Contains("餐厅手配费")
&& !z.Name.Contains("景点门票手配费")
&& !z.Name.Contains("平台佣金")
&& !z.Name.Contains("居间佣金")
&& !z.Name.Contains("商品收入")
&& !z.Name.Contains("运费收入")
&& !z.Name.Contains("退换货费用")
&& !z.Name.Contains("采购成本")
&& !z.Name.Contains("运杂费")
&& !z.Name.Contains("商品退款")
&& !z.Name.Contains("电商存货")
&& !z.Name.Contains("采购退回")
&& !z.Name.Contains("溢收款退款")
&& !z.Name.Contains("税费")
&& !z.Name.Contains("司导保险收入")
&& !z.Name.Contains("司导保险成本")
&& !z.Name.Contains("备用金")
&& !z.Name.Contains("个人借款")
&& !z.Name.Contains("其他代垫代收")
&& !z.Name.Contains("陈芳英款项")
).ToList();
break;
//管销费用
case 57:
NewfinanceList = NewfinanceList.Where(z => (!z.Name.Contains("所得税费用")
&& !z.Name.Contains("其他营业支出")
&& !z.Name.Contains("营业外收入")
&& !z.Name.Contains("应付款")
&& !z.Name.Contains("应收款")
&& !z.Name.Contains("汇兑损益")
&& !z.Name.Contains("利息收入")
&& !z.Name.Contains("旅游责任险")
&& !z.Name.Contains("保险费")
&& z.Type != WFTempLateClassEnum.ShouldIncome
&& z.Type != WFTempLateClassEnum.ShouldPay
&& !z.Name.Contains("投资款")
&& !z.Name.Contains("利润分配")
&& !z.Name.Contains("领队保证金")
&& !z.Name.Contains("导游保证金")
&& !z.Name.Contains("员工红利")
&& !z.Name.Contains("平台保证金")
&& !z.Name.Contains("其他保证金")
&& !z.Name.Contains("挂账单冲抵")
&& !z.Name.Contains("押金")) || (z.Name.Contains("快递费"))).ToList(); break;
//快递费
case 52:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("快递费") || z.Name.Contains("运杂费")).ToList(); break;
////营业总成本
//case 53:
// NewfinanceList =NewfinanceList.Where(z => z.Name.Contains("邀请函") || z.Name.Contains("名单表") || z.Name.Contains("车资") || z.Name.Contains("签证") || z.Name.Contains("地接费（领取）") || z.Name.Contains("地接费（结算）")).ToList();
// break;
//赔偿
case 54:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("赔偿")).ToList();
break;
//应收款
case 55:
NewfinanceList = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.ShouldIncome).ToList(); break;
//应付款
case 56:
NewfinanceList = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.ShouldPay).ToList(); break;
//应收款投资
case 58:
NewfinanceList = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.IN && z.Name.Contains("投资款")).ToList(); break;
//应付款投资
case 59:
NewfinanceList = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.OUT && z.Name.Contains("投资款")).ToList(); break;
//收入利润分配
case 61:
NewfinanceList = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.IN && z.Name.Contains("利润分配")).ToList(); break;
//支出利润分配
case 62:
NewfinanceList = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.OUT && z.Name.Contains("利润分配")).ToList(); break;
//收入员工红利
case 63:
NewfinanceList = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.IN && z.Name.Contains("员工红利")).ToList(); break;
//支出员工红利
case 64:
NewfinanceList = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.OUT && z.Name.Contains("员工红利")).ToList(); break;
//收入预付款
case 73:
NewfinanceList = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.IN && z.Name.Contains("预付款")).ToList(); break;
//支出预付款
case 74:
NewfinanceList = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.OUT && z.Name.Contains("预付款")).ToList(); break;
//收入保证金.押金预付款
case 75:
NewfinanceList = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.IN && (z.Name.Contains("平台保证金") || z.Name.Contains("押金") || z.Name.Contains("机票保证金") || z.Name.Contains("其他保证金"))).ToList(); break;
//支出保证金.押金预付款
case 76:
NewfinanceList = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.OUT && (z.Name.Contains("平台保证金") || z.Name.Contains("押金") || z.Name.Contains("机票保证金") || z.Name.Contains("其他保证金"))).ToList(); break;
case 79://日本印象 手配费
NewfinanceList = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.IN && z.Name.Contains("用房手配费") && z.Name.Contains("餐厅手配费") && z.Name.Contains("景点门票手配费")).ToList(); break;
//电商居间佣金
case 80:
NewfinanceList = NewfinanceList.Where(z => z.Name.Contains("居间佣金")).ToList(); break;
//备用金
case 81:
NewfinanceList = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.IN && z.Name.Contains("备用金")).ToList(); break;
//备用金
case 82:
NewfinanceList = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.OUT && z.Name.Contains("备用金")).ToList(); break;
//个人借款
case 83:
NewfinanceList = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.IN && z.Name.Contains("个人借款")).ToList(); break;
//个人借款
case 84:
NewfinanceList = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.OUT && z.Name.Contains("个人借款")).ToList(); break;
//其他代垫代收
case 85:
NewfinanceList = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.IN && z.Name.Contains("其他代垫代收")).ToList(); break;
//其他代垫代收
case 86:
NewfinanceList = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.OUT && z.Name.Contains("其他代垫代收")).ToList(); break;
//陈芳英款项
case 87:
NewfinanceList = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.IN && z.Name.Contains("陈芳英款项")).ToList(); break;
//陈芳英款项
case 88:
NewfinanceList = NewfinanceList.Where(z => z.Type == WFTempLateClassEnum.OUT && z.Name.Contains("陈芳英款项")).ToList(); break;
}
if (NewfinanceList != null && NewfinanceList.Count() > 0)
{
if (IsRCSZ)
{
Money = NewfinanceList.Where(y => y.Type == WFTempLateClassEnum.IN).Sum(x => x.Money.Value);
Fee = NewfinanceList.Where(x => x.Type == WFTempLateClassEnum.IN && x.Fee > 0).GroupBy(x => new { x.FrID, x.Fee }).Sum(x => x.Key.Fee ?? 0);
Money -= Fee;
Money = NewfinanceList.Where(y => y.Type == WFTempLateClassEnum.OUT).Sum(x => x.Money.Value) - Money;
}
else
{
if (Type == 9)
{
if (flag)
{
//收
Money = NewfinanceList.Where(y => y.Type == WFTempLateClassEnum.IN && y.Is_Cashier == 1).Sum(x => x.Money.Value);
Fee = NewfinanceList.Where(x => x.Type == WFTempLateClassEnum.IN && x.Is_Cashier == 1 && x.Fee > 0).GroupBy(x => new { x.FrID, x.Fee }).Sum(x => x.Key.Fee ?? 0);
Money -= Fee;
//支
Money = NewfinanceList.Where(y => y.Type == WFTempLateClassEnum.OUT && y.Is_Cashier == 1).Sum(x => x.Money.Value) - Money;
}
else
{
Money = NewfinanceList.Where(y => y.Type == WFTempLateClassEnum.IN).Sum(x => x.Money.Value);
Fee = NewfinanceList.Where(x => x.Type == WFTempLateClassEnum.IN && x.Fee > 0).GroupBy(x => new { x.FrID, x.Fee }).Sum(x => x.Key.Fee ?? 0);
Money -= Fee;
Money = NewfinanceList.Where(y => y.Type == WFTempLateClassEnum.OUT).Sum(x => x.Money.Value) - Money;
}
}
else if (Type == 66 || Type == 68 || Type == 70 || Type == 72 || Type == 74)
{
//预付款 冲抵
Money = NewfinanceList.Sum(x => x.Money.Value);
Fee = NewfinanceList.Where(x => x.Fee > 0).GroupBy(x => new { x.FrID, x.Fee }).Sum(x => x.Key.Fee ?? 0);
Money -= Fee;
var MatchList = NewfinanceList.Where(x => x.MatchMoney > 0).ToList();
if (MatchList.Count() > 0)
{
//减去冲抵的金额
var newFList = MatchList.Select(x => x.FrID).Distinct().ToList();
decimal DelMoney = 0;
foreach (var item in newFList)
{
DelMoney += MatchList.Where(x => x.FrID == item).FirstOrDefault()?.MatchMoney ?? 0;
}
Money -= DelMoney;
}
}
else
{
if (flag)
{
Money = NewfinanceList.Where(y => y.Is_Cashier == 1).Sum(x => x.Money.Value);
Fee = NewfinanceList.Where(x => x.Is_Cashier == 1 && x.Fee > 0).GroupBy(x => new { x.FrID, x.Fee }).Sum(x => x.Key.Fee ?? 0);
Money -= Fee;
}
else
{
Money = NewfinanceList.Sum(x => x.Money.Value);
Fee = NewfinanceList.Where(x => x.Fee > 0).GroupBy(x => new { x.FrID, x.Fee }).Sum(x => x.Key.Fee ?? 0);
Money -= Fee;
}
}
}
}
}
return Money;
}
public string GetCostIds(int Type, List<RB_Costtype_Extend> costList)
{
string ids = string.Empty;
switch (Type)
{
//佣金收入
case 1:
ids = string.Join(",", costList.Where(z => (z.Name.Contains("乳胶") || z.Name.Contains("御宝") || z.Name.Contains("乐天") || z.Name.Contains("茶道") || z.Name.Contains("高岛屋") || z.Name.Contains("TOKIS") || z.Name.Contains("电器LAOX") || z.Name.Contains("机场免税店") || z.Name.Contains("永山免税店") || z.Name.Contains("公社免税店") || z.Name.Contains("电器AKKYONE") || z.Name.Contains("橡胶店") || z.Name.Contains("琅勃拉邦古丝绸") || z.Name.Contains("老挝万象金沉香") || z.Name.Contains("老挝万象东盟珠宝") || z.Name.Contains("琅勃拉邦皇家乳胶") || z.Name.Contains("老挝土产") || z.Name.Contains("老挝红木") || z.Name.Contains("琅勃拉邦风情园") || z.Name.Contains("锅具") || z.Name.Contains("琅勃拉邦银寨") || z.Name.Contains("琅勃拉邦咖啡庄园") || z.Name.Contains("金占巴百草园"))).Select(x => x.ID + "").ToList());
break;
//自费收入
case 2:
ids = string.Join(",", costList.Where(z => z.Name.Contains("自费") || z.Name.Contains("自费收支结算")).Select(x => x.ID + "").ToList());
break;
//小费收入
case 3:
ids = string.Join(",", costList.Where(z => z.Name.Contains("小费")).Select(x => x.ID + "").ToList());
break;
//佣金+小费+自费
case 4:
ids = string.Join(",", costList.Where(z => z.Name.Contains("地接收入") || z.Name.Contains("小费") || z.Name.Contains("自费")).Select(x => x.ID + "").ToList());
break;
//邀请函、名单表
case 5:
ids = string.Join(",", costList.Where(z => z.Name.Contains("邀请函") || z.Name.Contains("名单表")).Select(x => x.ID + "").ToList());
break;
//车资
case 6:
ids = string.Join(",", costList.Where(z => z.Name.Contains("车资")).Select(x => x.ID + "").ToList()); break;
//国内联运机票
case 7:
ids = string.Join(",", costList.Where(z => z.Name.Contains("国内联运")).Select(x => x.ID + "").ToList()); break;
//签证
case 8:
ids = string.Join(",", costList.Where(z => z.Name.Contains("签证")).Select(x => x.ID + "").ToList()); break;
//其他
case 9:
ids = string.Join(",", costList.Where(z => !z.Name.Contains("邀请函") && !z.Name.Contains("名单表") && !z.Name.Contains("车资") && !z.Name.Contains("机票") && !z.Name.Contains("联运机票") && !z.Name.Contains("签证") && !z.Name.Contains("地接费（领取）") && !z.Name.Contains("地接费（结算）") && !z.Name.Contains("自费") && !z.Name.Contains("御宝") && !z.Name.Contains("乐天") && !z.Name.Contains("茶道") && !z.Name.Contains("高岛屋") && !z.Name.Contains("TOKIS") && !z.Name.Contains("电器LAOX") && !z.Name.Contains("机场免税店") && !z.Name.Contains("永山免税店") && !z.Name.Contains("公社免税店") && !z.Name.Contains("电器AKKONE") && !z.Name.Contains("橡胶店")).Select(x => x.ID + "").ToList());
break;
case 10:
//地接款支出
ids = string.Join(",", costList.Where(z => z.Name.Contains("地接费（领取）")).Select(x => x.ID + "").ToList()); break;
//地接款收入
case 11:
ids = string.Join(",", costList.Where(z => z.Name.Contains("地接费（结算）")).Select(x => x.ID + "").ToList()); break;
//小费收入
case 12:
ids = string.Join(",", costList.Where(z => z.Name.Contains("小费")).Select(x => x.ID + "").ToList()); break;
//自费收入
case 13:
ids = string.Join(",", costList.Where(z => z.Name.Contains("自费")).Select(x => x.ID + "").ToList()); break;
//简易报表
//意外险
case 14:
ids = string.Join(",", costList.Where(z => z.Name.Contains("保险费") || z.Name.Contains("旅游责任险")).Select(x => x.ID + "").ToList()); break;
//工资
case 15:
ids = string.Join(",", costList.Where(z => z.Name.Contains("工资") && !z.Name.Contains("员工工资(还款)")).Select(x => x.ID + "").ToList()); break;
//员工提成
case 16:
ids = string.Join(",", costList.Where(z => z.Name.Contains("业务提成（操作）") || z.Name.Contains("业务提成（签证）") || z.Name.Contains("业务提成（票务）") || z.Name.Contains("业务提成（地接）") || z.Name.Contains("业务提成（销售）") || z.Name.Contains("人头奖励（销售）")).Select(x => x.ID + "").ToList()); break;
//社保
case 17:
ids = string.Join(",", costList.Where(z => z.Name.Contains("社会保障费")).Select(x => x.ID + "").ToList()); break;
//员工福利及竞赛奖金
case 18:
ids = string.Join(",", costList.Where(z => z.Name.Contains("福利及奖金")).Select(x => x.ID + "").ToList()); break;
//办公用品
case 19:
ids = string.Join(",", costList.Where(z => z.Name.Contains("办公用品")).Select(x => x.ID + "").ToList()); break;
//差旅费
case 20:
ids = string.Join(",", costList.Where(z => z.Name.Contains("差旅费")).Select(x => x.ID + "").ToList()); break;
//电话费(通訊費)
case 21:
ids = string.Join(",", costList.Where(z => z.Name.Contains("电话费") || z.Name.Contains("通讯费")).Select(x => x.ID + "").ToList()); break;
//物管水电费
case 22:
ids = string.Join(",", costList.Where(z => z.Name.Contains("物管水电费")).Select(x => x.ID + "").ToList()); break;
//房租
case 23:
ids = string.Join(",", costList.Where(z => z.Name.Contains("房租")).Select(x => x.ID + "").ToList()); break;
//广告费
case 24:
ids = string.Join(",", costList.Where(z => z.Name.Contains("广告费")).Select(x => x.ID + "").ToList()); break;
//业务招待
case 25:
ids = string.Join(",", costList.Where(z => z.Name.Contains("业务招待")).Select(x => x.ID + "").ToList()); break;
//累计折旧
case 26:
ids = string.Join(",", costList.Where(z => z.Name.Contains("累计折旧")).Select(x => x.ID + "").ToList()); break;
//税金
case 27:
ids = string.Join(",", costList.Where(z => z.Name == "税金").Select(x => x.ID + "").ToList()); break;
//平台使用费
case 28:
ids = string.Join(",", costList.Where(z => z.Name.Contains("平台使用费")).Select(x => x.ID + "").ToList()); break;
//電腦系统费
case 29:
ids = string.Join(",", costList.Where(z => z.Name.Contains("電腦系统费") || z.Name.Contains("系统使用费")).Select(x => x.ID + "").ToList()); break;
//市内交通费
case 30:
ids = string.Join(",", costList.Where(z => z.Name.Contains("市内交通费")).Select(x => x.ID + "").ToList()); break;
//团队建设费
case 31:
ids = string.Join(",", costList.Where(z => z.Name.Contains("团队建设费")).Select(x => x.ID + "").ToList()); break;
//会务费
case 32:
ids = string.Join(",", costList.Where(z => z.Name.Contains("会务费")).Select(x => x.ID + "").ToList()); break;
//同行返利
case 33:
ids = string.Join(",", costList.Where(z => z.Name.Contains("同行返利") || z.Name.Contains("平台大红包返款") || z.Name.Contains("平台佣金")).Select(x => x.ID + "").ToList()); break;
//工会经费
case 34:
ids = string.Join(",", costList.Where(z => z.Name.Contains("工会经费")).Select(x => x.ID + "").ToList()); break;
//律师费
case 35:
ids = string.Join(",", costList.Where(z => z.Name.Contains("律师费")).Select(x => x.ID + "").ToList()); break;
//服务器管理费
case 36:
ids = string.Join(",", costList.Where(z => z.Name.Contains("服务器管理费")).Select(x => x.ID + "").ToList()); break;
//导游佣金
case 37:
ids = string.Join(",", costList.Where(z => z.Name.Contains("导游佣金")).Select(x => x.ID + "").ToList()); break;
//领队佣金
case 38:
ids = string.Join(",", costList.Where(z => z.Name.Contains("领队佣金") || z.Name.Contains("领队奖励金")).Select(x => x.ID + "").ToList()); break;
//航空公司保证金（会退）
case 39:
ids = string.Join(",", costList.Where(z => z.Name.Contains("航空公司保证金（会退）")).Select(x => x.ID + "").ToList()); break;
//航空公司大订金（会抵扣）
case 40:
ids = string.Join(",", costList.Where(z => z.Name.Contains("航空公司大订金（会抵扣）")).Select(x => x.ID + "").ToList()); break;
//汇款手续费
case 41:
ids = string.Join(",", costList.Where(z => z.Name.Contains("付款手续费") || z.Name.Contains("刷卡手续费") || z.Name.Contains("转账手续费")).Select(x => x.ID + "").ToList()); break;
//利息收入
case 42:
ids = string.Join(",", costList.Where(z => z.Name.Contains("利息收入")).Select(x => x.ID + "").ToList()); break;
//汇兑损益
case 43:
ids = string.Join(",", costList.Where(z => z.Name.Contains("汇兑损益")).Select(x => x.ID + "").ToList()); break;
//营业外收入
case 44:
ids = string.Join(",", costList.Where(z => z.Name.Contains("营业外收入") || z.Name.Contains("商品收入") || z.Name.Contains("运费收入") || z.Name.Contains("司导保险收入")).Select(x => x.ID + "").ToList()); break;
//营业外收入1（公司日常收入单）
case 45:
//ids = string.Join(",", costList.Where(z => z.Name.Contains("营业外收入1")).Select(x => x.ID + "").ToList()); break;
//应收款(老系统type=6)
case 46:
ids = string.Join(",", costList.Where(z => z.Type == WFTempLateClassEnum.ShouldIncome || (z.Type == WFTempLateClassEnum.IN && (z.Name.Contains("投资款") || z.Name.Contains("平台保证金") || z.Name.Contains("押金") || z.Name.Contains("其他保证金")))).Select(x => x.ID + "").ToList()); break;
//应付款(老系统type=7)
case 47:
ids = string.Join(",", costList.Where(z => z.Type == WFTempLateClassEnum.ShouldPay || (z.Type == WFTempLateClassEnum.OUT && (z.Name.Contains("投资款") || z.Name.Contains("平台保证金") || z.Name.Contains("押金") || z.Name.Contains("其他保证金")))).Select(x => x.ID + "").ToList()); break;
//其他营业支出
case 48:
ids = string.Join(",", costList.Where(z => z.Name.Contains("其他营业支出") || z.Name.Contains("退换货费用") || z.Name.Contains("采购成本") || z.Name.Contains("商品退款") || z.Name.Contains("税费") || z.Name.Contains("司导保险成本") || z.Name.Contains("采购退回") || z.Name.Contains("溢收款退款")).Select(x => x.ID + "").ToList()); break;
//所得税费用
case 49:
ids = string.Join(",", costList.Where(z => z.Name.Contains("所得税费用")).Select(x => x.ID + "").ToList()); break;
//营业税金
case 50:
ids = string.Join(",", costList.Where(z => z.Name.Contains("营业税金及附加")).Select(x => x.ID + "").ToList()); break;
//简易报表其他
case 51:
ids = string.Join(",",
costList.Where(z => !z.Name.Contains("所得税费用")
&& !z.Name.Contains("其他营业支出")
&& !z.Name.Contains("应付款")
&& !z.Name.Contains("应收款")
&& !z.Name.Contains("营业外收入")
&& !z.Name.Contains("汇兑损益")
&& !z.Name.Contains("利息收入")
&& (!z.Name.Contains("付款手续费")
&& !z.Name.Contains("刷卡手续费")
&& !z.Name.Contains("转账手续费"))
&& !z.Name.Contains("航空公司大订金（会抵扣）")
&& !z.Name.Contains("航空公司保证金（会退）")
&& !z.Name.Contains("领队佣金")
&& !z.Name.Contains("领队奖励金")
&& !z.Name.Contains("导游佣金")
&& !z.Name.Contains("服务器管理费")
&& !z.Name.Contains("律师费")
&& !z.Name.Contains("工会经费")
&& !z.Name.Contains("同行返利")
&& !z.Name.Contains("平台大红包返款")
&& !z.Name.Contains("会务费")
&& !z.Name.Contains("团队建设费")
&& !z.Name.Contains("市内交通费")
&& !z.Name.Contains("電腦系统费")
&& !z.Name.Contains("系统使用费")
&& !z.Name.Contains("系统费")
&& !z.Name.Contains("平台使用费")
&& z.Name != "税金"
&& !z.Name.Contains("累计折旧")
&& !z.Name.Contains("业务招待")
&& !z.Name.Contains("累计折旧")
&& !z.Name.Contains("广告费")
&& !z.Name.Contains("房租")
&& !z.Name.Contains("物管水电费")
&& !z.Name.Contains("电话费")
&& !z.Name.Contains("通讯费")
&& !z.Name.Contains("差旅费")
&& !z.Name.Contains("办公用品")
&& !z.Name.Contains("福利及奖金")
&& !z.Name.Contains("社会保障费")
&& !z.Name.Contains("业务提成")
&& !z.Name.Contains("人头奖励")
&& !z.Name.Contains("工资")
&& !z.Name.Contains("保险费")
&& !z.Name.Contains("旅游责任险")
&& !z.Name.Contains("营业税金及附加")
&& z.Type != WFTempLateClassEnum.ShouldIncome
&& z.Type != WFTempLateClassEnum.ShouldPay
&& !z.Name.Contains("投资款")
&& !z.Name.Contains("利润分配")
&& !z.Name.Contains("员工红利")
&& !z.Name.Contains("保证金")
&& !z.Name.Contains("其他保证金")
&& !z.Name.Contains("酒店预付款")
&& !z.Name.Contains("CHI包房")
&& !z.Name.Contains("预付环球门票")
&& !z.Name.Contains("门票预付款")
&& !z.Name.Contains("餐厅预付款")
&& !z.Name.Contains("车资预付款")
&& !z.Name.Contains("机票预付款")
&& !z.Name.Contains("机票保证金")
&& !z.Name.Contains("电商预付款")
&& !z.Name.Contains("押金")
&& !z.Name.Contains("员工工资(还款)")
&& !z.Name.Contains("挂账单冲抵")
&& !z.Name.Contains("机票收入")
&& !z.Name.Contains("快递费")
&& !z.Name.Contains("照片服务费")
&& !z.Name.Contains("地接OP备用金")
&& !z.Name.Contains("领队罚金")
&& !z.Name.Contains("景点门票")
&& !z.Name.Contains("用房手配费")
&& !z.Name.Contains("餐厅手配费")
&& !z.Name.Contains("景点门票手配费")
&& !z.Name.Contains("平台佣金")
&& !z.Name.Contains("居间佣金")
&& !z.Name.Contains("商品收入")
&& !z.Name.Contains("运费收入")
&& !z.Name.Contains("退换货费用")
&& !z.Name.Contains("采购成本")
&& !z.Name.Contains("运杂费")
&& !z.Name.Contains("商品退款")
&& !z.Name.Contains("电商存货")
&& !z.Name.Contains("采购退回")
&& !z.Name.Contains("溢收款退款")
&& !z.Name.Contains("税费")
&& !z.Name.Contains("司导保险")
&& !z.Name.Contains("备用金")
&& !z.Name.Contains("个人借款")
&& !z.Name.Contains("其他代垫代收")
&& !z.Name.Contains("陈芳英款项")
).Select(x => x.ID + "").ToList()); break;
//快递费
case 52:
ids = string.Join(",", costList.Where(z => z.Name.Contains("快递费") || z.Name.Contains("运杂费")).Select(x => x.ID + "").ToList()); break;
//应收应付款
case 53:
ids = string.Join(",", costList.Where(z => z.Type == WFTempLateClassEnum.ShouldIncome || z.Type == WFTempLateClassEnum.ShouldPay).Select(x => x.ID + "").ToList()); break;
//应收应付款(投资款)
case 55:
ids = string.Join(",", costList.Where(z => z.Name.Contains("投资款")).Select(x => x.ID + "").ToList()); break;
//利润分配
case 61:
ids = string.Join(",", costList.Where(z => z.Name.Contains("利润分配")).Select(x => x.ID + "").ToList()); break;
//员工红利
case 62:
ids = string.Join(",", costList.Where(z => z.Name.Contains("员工红利")).Select(x => x.ID + "").ToList()); break;
//酒店预付款
case 63:
ids = string.Join(",", costList.Where(z => z.Name.Contains("酒店预付款") || z.Name.Contains("CHI包房")).Select(x => x.ID + "").ToList()); break;
//门票预付款
case 64:
ids = string.Join(",", costList.Where(z => z.Name.Contains("门票预付款") || z.Name.Contains("预付环球门票")).Select(x => x.ID + "").ToList()); break;
//餐厅预付款
case 65:
ids = string.Join(",", costList.Where(z => z.Name.Contains("餐厅预付款")).Select(x => x.ID + "").ToList()); break;
//车资预付款
case 66:
ids = string.Join(",", costList.Where(z => z.Name.Contains("预付款")).Select(x => x.ID + "").ToList()); break;
//机票预付款
case 67:
ids = string.Join(",", costList.Where(z => z.Name.Contains("机票预付款")).Select(x => x.ID + "").ToList()); break;
//保证金押金预付款
case 68:
ids = string.Join(",", costList.Where(z => z.Name.Contains("平台保证金") || z.Name.Contains("押金") || z.Name.Contains("机票保证金") || z.Name.Contains("其他保证金")).Select(x => x.ID + "").ToList()); break;
//领队导游保证金
case 69:
ids = string.Join(",", costList.Where(z => z.Name.Contains("领队保证金") || z.Name.Contains("导游保证金")).Select(x => x.ID + "").ToList()); break;
//同行返利
case 70:
ids = string.Join(",", costList.Where(z => z.Name.Contains("居间佣金")).Select(x => x.ID + "").ToList()); break;
//备用金
case 71:
ids = string.Join(",", costList.Where(z => z.Name.Contains("备用金")).Select(x => x.ID + "").ToList()); break;
//个人借款
case 72:
ids = string.Join(",", costList.Where(z => z.Name.Contains("个人借款")).Select(x => x.ID + "").ToList()); break;
//其他代垫代收
case 73:
ids = string.Join(",", costList.Where(z => z.Name.Contains("其他代垫代收")).Select(x => x.ID + "").ToList()); break;
//陈芳英款项
case 74:
ids = string.Join(",", costList.Where(z => z.Name.Contains("陈芳英款项")).Select(x => x.ID + "").ToList()); break;
}
return ids;
}
#endregion
#region 业绩提成
/// <summary>
/// 获取订单提成明细
/// </summary>
/// <param name="orderIds"></param>
/// <param name="group_Id"></param>
/// <returns></returns>
public List<RB_Sell_Achievements_Emp_ViewModel> GetAchListForOrderIds(string orderIds, int group_Id)
{
return sell_Achievements_EmpRepository.GetList(new RB_Sell_Achievements_Emp_ViewModel() { Group_Id = group_Id, OrderIds = orderIds });
}
/// <summary>
/// 获取订单返佣列表
/// </summary>
/// <param name="orderIds"></param>
/// <param name="group_Id"></param>
/// <returns></returns>
public List<RB_Order_ReturnComission_ViewModel> GetOrderReturnComissionList(string orderIds, int group_Id)
{
return order_ReturnComissionRepository.GetOrderReturnComissionListRepositpry(new RB_Order_ReturnComission_ViewModel() { QOrderIds = orderIds, GroupId = group_Id });
}
#endregion
#region 月结窗口
/// <summary>
/// 月结设置分页列表
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <param name="dmodel"></param>
/// <returns></returns>
public List<RB_RollingAccount_Extend> GetFinanceMonthStatementPageList(int pageIndex, int pageSize, out long count, RB_RollingAccount_Extend dmodel)
{
var list = rollingAccountRepository.GetPageList(pageIndex, pageSize, out count, dmodel);
if (list.Any()) {
//查询校区
List<int> SchoolIdList = JsonHelper.DeserializeObject<List<int>>("[" + string.Join(",", list.Select(x => x.SchoolIds)) + "]");
string SchoolIds = string.Join(",", SchoolIdList.Where(x => x >= 0).Distinct().ToList());
var schoolList = schoolRepository.GetSchoolListRepository(new Model.ViewModel.User.RB_School_ViewModel() { Group_Id = dmodel.GroupId, QSIds = SchoolIds });
//查询创建人
string userIds = string.Join(',', list.Select(x => x.UpdateBy).Distinct());
var userList = accountRepository.GetEmployeeListRepository(new Model.ViewModel.User.Employee_ViewModel() { Group_Id = dmodel.GroupId, QIds = userIds });
foreach (var item in list) {
item.UpdateByName = userList.Where(x => x.Id == item.UpdateBy).FirstOrDefault()?.EmployeeName ?? "";
item.SchoolList = new List<Model.ViewModel.User.RB_School_ViewModel>();
if (item.SchoolIds == "-1")
{
item.SchoolList.Add(new Model.ViewModel.User.RB_School_ViewModel()
{
SId = -1,
SName = "全部"
});
}
else {
List<int> SchoolId2List = JsonHelper.DeserializeObject<List<int>>("[" + item.SchoolIds + "]");
var sList = schoolList.Where(x => SchoolId2List.Contains(x.SId)).ToList();
item.SchoolList = sList;
}
}
}
return list;
}
/// <summary>
/// 新增/修改 月结设置
/// </summary>
/// <param name="dmodel"></param>
/// <returns></returns>
public string SetFinanceMonthStatementInfo(RB_RollingAccount_Extend dmodel)
{
if (dmodel.Id > 0)
{
//验证 除此之外 其他是否有包含
var list = rollingAccountRepository.GetList(new RB_RollingAccount_Extend() { GroupId = dmodel.GroupId, Month = dmodel.Month });
list = list.Where(x => x.Id != dmodel.Id).ToList();
if (list.Any()) {
foreach (var item in dmodel.SchoolList) {
if (list.Where(x => ("," + x.SchoolIds + ",").Contains("," + item.SId + ",")).Any()) {
return item.SName + "已存在其他设置里，请核实后再试";
}
}
}
dmodel.SchoolIds = string.Join(",", dmodel.SchoolList.Select(x => x.SId));
Dictionary<string, object> keyValues = new Dictionary<string, object>() {
{ nameof(RB_RollingAccount_Extend.Month), dmodel.Month},
{ nameof(RB_RollingAccount_Extend.SchoolIds), dmodel.SchoolIds},
{ nameof(RB_RollingAccount_Extend.State), dmodel.State},
{ nameof(RB_RollingAccount_Extend.UpdateBy), dmodel.UpdateBy},
{ nameof(RB_RollingAccount_Extend.UpdateTime), dmodel.UpdateTime},
};
List<WhereHelper> wheres = new List<WhereHelper>() {
new WhereHelper(){
FiledName=nameof(RB_RollingAccount_Extend.Id),
FiledValue=dmodel.Id,
OperatorEnum=OperatorEnum.Equal
}
};
bool flag = rollingAccountRepository.Update(keyValues, wheres);
return flag ? "" : "出错了，请联系管理员";
}
else {
var list = rollingAccountRepository.GetList(new RB_RollingAccount_Extend() { GroupId = dmodel.GroupId, Month = dmodel.Month });
if (list.Any())
{
foreach (var item in dmodel.SchoolList)
{
if (list.Where(x => ("," + x.SchoolIds + ",").Contains("," + item.SId + ",")).Any())
{
return item.SName + "已存在其他设置里，请核实后再试";
}
}
}
dmodel.SchoolIds = string.Join(",", dmodel.SchoolList.Select(x => x.SId));
bool flag = rollingAccountRepository.Insert(dmodel) >0;
return flag ? "" : "出错了，请联系管理员";
}
}
#endregion
#region 报表统计 6.28
/// <summary>
/// 获取老师课时统计
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <param name="teacherId"></param>
/// <param name="schoolId"></param>
/// <param name="startMonth"></param>
/// <param name="endMonth"></param>
/// <returns></returns>
public object GetTeacherConsumptionHoursStatistics(int pageIndex, int pageSize, out long count, int teacherId, int schoolId, string startMonth, string endMonth, int groupId)
{
List<object> RList = new List<object>();
//首先查询所有的老师 + 助教
var userList = accountRepository.GetEmployeePageListRepository(pageIndex, pageSize, out count, new Model.ViewModel.User.Employee_ViewModel() { Group_Id = groupId, AccountId = teacherId, School_Id = schoolId, IsHoursStatistics = 1, AccountTypeStr = "2", HoursStartTime = startMonth, HoursEndTime = endMonth });
if (userList.Any())
{
//查询当前时间范围 这些老师的上课课时
string userIds = string.Join(",", userList.Select(x => x.AccountId));
var tTimeList = class_CheckRepository.GetTeacherHoursList(groupId, userIds, startMonth, endMonth);
List<RB_Class_ViewModel> ClassList = new List<RB_Class_ViewModel>();
if (tTimeList.Any()) {
//查询班级信息
string classIds = string.Join(",", tTimeList.Select(x => x.ClassId).Distinct());
ClassList = classRepository.GetClassListRepository(new RB_Class_ViewModel() { Group_Id = groupId, Q_ClassIds = classIds });
}
//班级类型查询
var cTypeList = class_TypeRepository.GetClassTypeListRepository(new RB_Class_Type_ViewModel() { Group_Id = groupId });
//查询期间 学生的上课总课时 + 合同课时单价
var sTimeList = class_CheckRepository.GetStudentHoursList(groupId, userIds, startMonth, endMonth);
//基础配置
var configModel = class_ConfigRepository.GetClassConfigRepository(new RB_Class_Config_ViewModel() { Group_Id = groupId });
if (configModel == null || configModel.BasicHourFee <= 0 || configModel.BasicMinutes <= 0) { return "未配置课时费基础设置"; }
//根据学生id
//查询课时费 （要注意 需排除每个月老师的基础课时）
foreach (var item in userList)
{
var tlist = tTimeList.Where(x => x.TeacherId == item.AccountId).ToList();//多班级
int TotalStudyNum = tlist.Sum(x => x.StudyNum);
decimal ksNum = Math.Round(Convert.ToDecimal(TotalStudyNum) / configModel.BasicMinutes, 2, MidpointRounding.AwayFromZero); // 未达到一课时时间 算不算一课时
decimal Hours = Math.Round(Convert.ToDecimal(TotalStudyNum) / 60, 2, MidpointRounding.AwayFromZero);//小时
decimal KSMoney = 0;
foreach (var qitem in tlist) {
var classModel = ClassList.Where(x => x.ClassId == qitem.ClassId).FirstOrDefault();
var typeModel = cTypeList.Where(x => x.CTypeId == classModel.ClassType).FirstOrDefault();
//课时费 需要每个班级 单独计算 因为 班级有附加费用
KSMoney += Math.Round(Convert.ToDecimal(qitem.StudyNum) / configModel.BasicMinutes * (configModel.BasicHourFee + (typeModel?.AddHourFee ?? 0) + (item?.BaseHourFee ?? 0)), 2, MidpointRounding.AwayFromZero);
}
decimal StuMoney = Math.Round(sTimeList.Where(x => x.TeacherId == item.AccountId).Sum(x => x.CurrentDeductionHours * x.UnitPrice), 2, MidpointRounding.AwayFromZero);
RList.Add(new
{
item.Id,
item.AccountId,
item.EmployeeName,
LeaveStatus = item.LeaveStatus.ToName(),
KSNum = ksNum,
Hours,
KSMoney,
StuLearnMoney = StuMoney,
item.SchoolName
});
}
}
return RList;
}
/// <summary>
/// 获取老师课时汇总统计
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <param name="teacherId"></param>
/// <param name="schoolId"></param>
/// <param name="startMonth"></param>
/// <param name="endMonth"></param>
/// <param name="groupId"></param>
/// <returns></returns>
public object GetTeacherConsumptionHoursStatistics_V2(int pageIndex, int pageSize, out long count, int teacherId, int schoolId, string startMonth, string endMonth, int groupId)
{
List<object> RList = new List<object>();
//首先查询所有的老师
var userList = accountRepository.GetEmployeePageListRepository(pageIndex, pageSize, out count, new Model.ViewModel.User.Employee_ViewModel() { Group_Id = groupId, AccountId = teacherId, School_Id = schoolId, IsHoursStatistics = 1, AccountTypeStr = "2", HoursStartTime = startMonth, HoursEndTime = endMonth });
if (userList.Any())
{
//班级类型查询
var cTypeList = class_TypeRepository.GetClassTypeListRepository(new RB_Class_Type_ViewModel() { Group_Id = groupId });
//基础配置
var configModel = class_ConfigRepository.GetClassConfigRepository(new RB_Class_Config_ViewModel() { Group_Id = groupId });
if (configModel == null || configModel.BasicHourFee <= 0 || configModel.BasicMinutes <= 0) { return RList; }
//查询当前时间范围 这些老师的上课课时
string userIds = string.Join(",", userList.Select(x => x.AccountId));
List<RB_Teaching_BonusDetail_ViewModel> DateList = new List<RB_Teaching_BonusDetail_ViewModel>();
//根据学生签到表 查询
var list = class_CheckRepository.GetTeacherConsumptionHoursDetialList(userIds, schoolId, 0, startMonth, endMonth, groupId);
if (list.Any())
{
foreach (var item in list)
{
decimal ClassHourMinute = configModel.BasicMinutes;//课时分钟数
//if (item.ClassHourMinute > 0) { ClassHourMinute = item.ClassHourMinute; }
//上课课时
decimal KSNum = item.CurrentDeductionHours;//使用签到表的课时
if (item.ClassId == 18) { KSNum = 0; }//特殊班级处理
decimal Hours = Math.Round(KSNum * ClassHourMinute / 60, 2, MidpointRounding.AwayFromZero);//小时
var typeModel = cTypeList.Where(x => x.CTypeId == item.ClassType).FirstOrDefault();
decimal unitPrice = configModel.BasicHourFee + (typeModel?.AddHourFee ?? 0) + (item?.BaseHourFee ?? 0) + (item?.CourseAddHoursMoney ?? 0);
//课时费 需要每个班级 单独计算 因为 班级有附加费用
decimal KSMoney = KSNum * unitPrice;
decimal HourMoney = Math.Round(unitPrice / ClassHourMinute * 60, 2, MidpointRounding.AwayFromZero);//没小时课时费
DateList.Add(new RB_Teaching_BonusDetail_ViewModel
{
Date = item.ClassDate,
TimeBucket = item.StartDate + "~" + item.EndDate,
TeacherId = item.TeacherId,
TeacherName = item.TeacherName,
ClassId = item.ClassId,
ClassName = item.ClassName,
CouseId = item.CouseId,
CourseName = item.CourseName,
KSNum = KSNum,
Hours = Hours,
HourMoney = HourMoney,
UnitPrice = unitPrice,
Money = KSMoney,
CouseClassify = 1
});
}
}
//查询试听课
var rclist = reserve_ClassRepository.GetTeacherHoursDetialList(userIds, schoolId, 0, startMonth, endMonth, groupId);
if (rclist.Any()) {
decimal ClassHourMinute = configModel.BasicMinutes;//课时分钟数
decimal Hours = Math.Round(1 * ClassHourMinute / 60, 2, MidpointRounding.AwayFromZero);//小时
foreach (var item in rclist) {
DateList.Add(new RB_Teaching_BonusDetail_ViewModel
{
Date = item.ClassDate,
TimeBucket = item.ClassTime + "~" + item.EndTime,
TeacherId = item.TeacherId,
TeacherName = item.TeacherName,
ClassId = item.ReserveClassId,
ClassName = "试听课",
CouseId = item.TrialLessonId,
CourseName = item.LessonName,
KSNum = 1,
Hours = Hours,
HourMoney = Math.Round(item.ClassHourFee / Hours, 2, MidpointRounding.AwayFromZero),
UnitPrice = item.ClassHourFee,
Money = item.ClassHourFee,
CouseClassify = 2
});
}
}
//查询期间 学生的上课总课时 + 合同课时单价
var sTimeList = class_CheckRepository.GetStudentHoursList(groupId, userIds, startMonth, endMonth);
DateList = DateList.OrderBy(x => x.Date).ToList();
//根据学生id
//查询课时费 （要注意 需排除每个月老师的基础课时）
foreach (var item in userList)
{
var tlist = DateList.Where(x => x.TeacherId == item.AccountId).ToList();//多班级
//初始化 基础课时
DateTime STime = Convert.ToDateTime(startMonth);
while (true)
{
DateTime ETime = STime.AddMonths(1).AddDays(-1);
var mlist = tlist.Where(x => x.Date >= STime && x.Date <= ETime).ToList();
#region 基础课时处理
decimal DeductionMoney = 0;
if (configModel.BasicHours > 0 && item.BaseHoursEnabled == 1 && STime >= Convert.ToDateTime(item.EnableTime))
{
//遍历所有老师
List<int> TeacherIdList = mlist.Select(x => x.TeacherId).Distinct().ToList();
foreach (var tId in TeacherIdList)
{
var qblist = mlist.Where(x => x.TeacherId == tId).OrderBy(x => x.Date).ThenBy(x => x.UnitPrice).ToList();
decimal BasicHours = (decimal)(configModel.BasicHours + item.BaseHoursAdd);
foreach (var qitem in qblist)
{
decimal CourseHour = 0, DeductionHour = 0;
if (BasicHours > qitem.KSNum)
{
DeductionHour = qitem.KSNum;
BasicHours -= qitem.KSNum;
DeductionMoney += qitem.KSNum * qitem.UnitPrice;
}
else
{
DeductionHour = BasicHours;
CourseHour = qitem.KSNum - BasicHours;
DeductionMoney += BasicHours * qitem.UnitPrice;
BasicHours = 0;
}
qitem.KSNum = CourseHour;
qitem.DKNum = DeductionHour;
qitem.Money = CourseHour * qitem.UnitPrice;
if (BasicHours <= 0)
{
break;
}
}
}
}
#endregion
if (STime.Month == Convert.ToDateTime(endMonth).Month)
{
break;
}
STime = STime.AddMonths(1);
}
decimal ksNum = tlist.Sum(x => x.KSNum);
decimal dkNum = tlist.Sum(x => x.DKNum);
decimal Hours = tlist.Sum(x => x.Hours);
decimal KSMoney = tlist.Sum(x => x.Money);
decimal StuMoney = Math.Round(sTimeList.Where(x => x.TeacherId == item.AccountId).Sum(x => x.CurrentDeductionHours * x.UnitPrice), 2, MidpointRounding.AwayFromZero);
RList.Add(new
{
item.Id,
item.AccountId,
item.EmployeeName,
LeaveStatus = item.LeaveStatus.ToName(),
KSNum = ksNum,
DKNum = dkNum,
Hours,
KSMoney,
StuLearnMoney = StuMoney,
item.SchoolName
});
}
}
return RList;
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="teacherId"></param>
/// <param name="schoolId"></param>
/// <param name="startMonth"></param>
/// <param name="endMonth"></param>
/// <param name="groupId"></param>
/// <returns></returns>
public List<ExcelDataSource> GetTeacherConsumptionHoursStatisticsToExcel(int teacherId, int schoolId, string startMonth, string endMonth, int groupId)
{
List<ExcelDataSource> RList = new List<ExcelDataSource>();
//首先查询所有的老师 + 助教
var userList = accountRepository.GetEmployeeListRepository(new Model.ViewModel.User.Employee_ViewModel() { Group_Id = groupId, AccountId = teacherId, School_Id = schoolId, IsHoursStatistics = 1, AccountTypeStr = "2", HoursStartTime = startMonth, HoursEndTime = endMonth });
if (userList.Any())
{
//班级类型查询
var cTypeList = class_TypeRepository.GetClassTypeListRepository(new RB_Class_Type_ViewModel() { Group_Id = groupId });
//基础配置
var configModel = class_ConfigRepository.GetClassConfigRepository(new RB_Class_Config_ViewModel() { Group_Id = groupId });
if (configModel == null || configModel.BasicHourFee <= 0 || configModel.BasicMinutes <= 0) { return new List<ExcelDataSource>(); }
//查询当前时间范围 这些老师的上课课时
string userIds = string.Join(",", userList.Select(x => x.AccountId));
List<RB_Teaching_BonusDetail_ViewModel> DateList = new List<RB_Teaching_BonusDetail_ViewModel>();
//根据学生签到表 查询
var list = class_CheckRepository.GetTeacherConsumptionHoursDetialList(userIds, schoolId, 0, startMonth, endMonth, groupId);
if (list.Any())
{
foreach (var item in list)
{
decimal ClassHourMinute = configModel.BasicMinutes;//课时分钟数
//if (item.ClassHourMinute > 0) { ClassHourMinute = item.ClassHourMinute; }
//上课课时
decimal KSNum = item.CurrentDeductionHours;//使用签到表课时
if (item.ClassId == 18) { KSNum = 0; }//特殊班级处理
decimal Hours = Math.Round(KSNum * ClassHourMinute / 60, 2, MidpointRounding.AwayFromZero);//小时
var typeModel = cTypeList.Where(x => x.CTypeId == item.ClassType).FirstOrDefault();
decimal unitPrice = configModel.BasicHourFee + (typeModel?.AddHourFee ?? 0) + (item?.BaseHourFee ?? 0) + (item?.CourseAddHoursMoney ?? 0);
//课时费 需要每个班级 单独计算 因为 班级有附加费用
decimal KSMoney = KSNum * unitPrice;
decimal HourMoney = Math.Round(unitPrice / ClassHourMinute * 60, 2, MidpointRounding.AwayFromZero);//没小时课时费
DateList.Add(new RB_Teaching_BonusDetail_ViewModel
{
Date = item.ClassDate,
TimeBucket = item.StartDate + "~" + item.EndDate,
TeacherId = item.TeacherId,
TeacherName = item.TeacherName,
ClassId = item.ClassId,
ClassName = item.ClassName,
CouseId = item.CouseId,
CourseName = item.CourseName,
KSNum = KSNum,
Hours = Hours,
HourMoney = HourMoney,
UnitPrice = unitPrice,
Money = KSMoney
});
}
}
//查询试听课
var rclist = reserve_ClassRepository.GetTeacherHoursDetialList(userIds, schoolId, 0, startMonth, endMonth, groupId);
if (rclist.Any())
{
decimal ClassHourMinute = configModel.BasicMinutes;//课时分钟数
decimal Hours = Math.Round(1 * ClassHourMinute / 60, 2, MidpointRounding.AwayFromZero);//小时
foreach (var item in rclist)
{
DateList.Add(new RB_Teaching_BonusDetail_ViewModel
{
Date = item.ClassDate,
TimeBucket = item.ClassTime + "~" + item.EndTime,
TeacherId = item.TeacherId,
TeacherName = item.TeacherName,
ClassId = item.ReserveClassId,
ClassName = "试听课",
CouseId = item.TrialLessonId,
CourseName = item.LessonName,
KSNum = 1,
Hours = Hours,
HourMoney = Math.Round(item.ClassHourFee / Hours, 2, MidpointRounding.AwayFromZero),
UnitPrice = item.ClassHourFee,
Money = item.ClassHourFee,
CouseClassify = 2
});
}
}
DateList = DateList.OrderBy(x => x.Date).ToList();
//查询期间 学生的上课总课时 + 合同课时单价
var sTimeList = class_CheckRepository.GetStudentHoursList(groupId, userIds, startMonth, endMonth);
//根据学生id
//查询课时费 （要注意 需排除每个月老师的基础课时）
int Num = 1;
decimal TKSNum = 0, TDKNum = 0, TStuMoney = 0, THours = 0, TKSMoney = 0;
foreach (var item in userList)
{
var tlist = DateList.Where(x => x.TeacherId == item.AccountId).ToList();//多班级
//初始化 基础课时
DateTime STime = Convert.ToDateTime(startMonth);
while (true)
{
DateTime ETime = STime.AddMonths(1).AddDays(-1);
var mlist = tlist.Where(x => x.Date >= STime && x.Date <= ETime).ToList();
#region 基础课时处理
decimal DeductionMoney = 0;
if (configModel.BasicHours > 0 && item.BaseHoursEnabled == 1 && STime >= Convert.ToDateTime(item.EnableTime))
{
//遍历所有老师
List<int> TeacherIdList = mlist.Select(x => x.TeacherId).Distinct().ToList();
foreach (var tId in TeacherIdList)
{
var qblist = mlist.Where(x => x.TeacherId == tId).OrderBy(x => x.Date).ThenBy(x => x.UnitPrice).ToList();
decimal BasicHours = (decimal)(configModel.BasicHours + item.BaseHoursAdd);
foreach (var qitem in qblist)
{
decimal CourseHour = 0, DeductionHour = 0;
if (BasicHours > qitem.KSNum)
{
DeductionHour = qitem.KSNum;
BasicHours -= qitem.KSNum;
DeductionMoney += qitem.KSNum * qitem.UnitPrice;
}
else
{
DeductionHour = BasicHours;
CourseHour = qitem.KSNum - BasicHours;
DeductionMoney += BasicHours * qitem.UnitPrice;
BasicHours = 0;
}
qitem.KSNum = CourseHour;
qitem.DKNum = DeductionHour;
qitem.Money = CourseHour * qitem.UnitPrice;
if (BasicHours <= 0)
{
break;
}
}
}
}
#endregion
if (STime.Month == Convert.ToDateTime(endMonth).Month)
{
break;
}
STime = STime.AddMonths(1);
}
decimal ksNum = tlist.Sum(x => x.KSNum);
decimal dkNum = tlist.Sum(x => x.DKNum);
decimal Hours = tlist.Sum(x => x.Hours);
decimal KSMoney = tlist.Sum(x => x.Money);
decimal StuMoney = Math.Round(sTimeList.Where(x => x.TeacherId == item.AccountId).Sum(x => x.CurrentDeductionHours * x.UnitPrice), 2, MidpointRounding.AwayFromZero);
ExcelDataSource firstRow = new ExcelDataSource(30)
{
ExcelRows = new List<ExcelColumn>()
{
new ExcelColumn(value: Num.ToString()){ },
new ExcelColumn(value: item.SchoolName){ },
new ExcelColumn(value: item.LeaveStatus.ToName()){ },
new ExcelColumn(value: item.EmployeeName){ },
new ExcelColumn(value: ksNum.ToString("#0.00")){ },
new ExcelColumn(value: dkNum.ToString("#0.00")){ },
new ExcelColumn(value: StuMoney.ToString("#0.00")){ },
new ExcelColumn(value: Hours.ToString("#0.00")){ },
new ExcelColumn(value: KSMoney.ToString("#0.00")){ }
}
};
RList.Add(firstRow);
Num++;
TKSNum += ksNum;
TDKNum += dkNum;
TStuMoney += StuMoney;
THours += Hours;
TKSMoney += KSMoney;
}
ExcelDataSource firstRowT = new ExcelDataSource(30)
{
ExcelRows = new List<ExcelColumn>()
{
new ExcelColumn(value: "合计"){ },
new ExcelColumn(value: ""){ },
new ExcelColumn(value: ""){ },
new ExcelColumn(value: ""){ },
new ExcelColumn(value: TKSNum.ToString("#0.00")){ },
new ExcelColumn(value: TDKNum.ToString("#0.00")){ },
new ExcelColumn(value: TStuMoney.ToString("#0.00")){ },
new ExcelColumn(value: THours.ToString("#0.00")){ },
new ExcelColumn(value: TKSMoney.ToString("#0.00")){ }
}
};
RList.Add(firstRowT);
}
return RList;
}
/// <summary>
/// 获取老师课时统计明细列表
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <param name="teacherId"></param>
/// <param name="schoolId"></param>
/// <param name="classId"></param>
/// <param name="startMonth"></param>
/// <param name="endMonth"></param>
/// <param name="group_Id"></param>
/// <returns></returns>
public List<RB_Teaching_BonusDetail_ViewModel> GetTeacherConsumptionHoursDetialPageList(int pageIndex, int pageSize, out long count, int teacherId, int schoolId, int classId, string startMonth, string endMonth, int groupId)
{
List<RB_Teaching_BonusDetail_ViewModel> RList = new List<RB_Teaching_BonusDetail_ViewModel>();
//根据学生签到表 查询
var list = class_CheckRepository.GetTeacherConsumptionHoursDetialPageList(pageIndex, pageSize, out count, teacherId, schoolId, classId, startMonth, endMonth, groupId);
var rclist = new List<Model.ViewModel.Reserve.RB_Reserve_Class_Extend>();
if (pageSize == 1000 || pageSize == 10000) {
//查询试听课
rclist = reserve_ClassRepository.GetTeacherHoursDetialList(teacherId > 0 ? teacherId.ToString() : "", schoolId, 0, startMonth, endMonth, groupId);
}
if (list.Any() || rclist.Any()) {
//班级类型查询
var cTypeList = class_TypeRepository.GetClassTypeListRepository(new RB_Class_Type_ViewModel() { Group_Id = groupId });
//基础配置
var configModel = class_ConfigRepository.GetClassConfigRepository(new RB_Class_Config_ViewModel() { Group_Id = groupId });
if (configModel == null || configModel.BasicHourFee <= 0 || configModel.BasicMinutes <= 0) { return RList; }
foreach (var item in list) {
decimal ClassHourMinute = configModel.BasicMinutes;//课时分钟数
//if (item.ClassHourMinute > 0) { ClassHourMinute = item.ClassHourMinute; }
//上课课时
decimal KSNum = item.CurrentDeductionHours;//课时直接取这里的了， 这个可手动调整了；
if (item.ClassId == 18) { KSNum = 0; }//特殊班级处理
decimal Hours = Math.Round(KSNum * ClassHourMinute / 60, 2, MidpointRounding.AwayFromZero);//小时
var typeModel = cTypeList.Where(x => x.CTypeId == item.ClassType).FirstOrDefault();
decimal unitPrice = configModel.BasicHourFee + (typeModel?.AddHourFee ?? 0) + (item?.BaseHourFee ?? 0) + (item?.CourseAddHoursMoney ?? 0);
//课时费 需要每个班级 单独计算 因为 班级有附加费用
decimal KSMoney = KSNum * unitPrice;
decimal HourMoney = Math.Round(unitPrice / ClassHourMinute * 60, 2, MidpointRounding.AwayFromZero);//每小时课时费
RList.Add(new RB_Teaching_BonusDetail_ViewModel
{
Date = item.ClassDate,
TimeBucket = item.StartDate + "~" + item.EndDate,
TeacherId = item.TeacherId,
TeacherName = item.TeacherName,
ClassId = item.ClassId,
ClassName = item.ClassName,
ClassNo = item.ClassNo,
CouseId = item.CouseId,
CourseName = item.CourseName,
KSNum = KSNum,
Hours = Hours,
HourMoney = HourMoney,
UnitPrice = unitPrice,
Money = KSMoney,
BaseHoursEnabled = item.BaseHoursEnabled,
EnableTime = item.EnableTime,
BaseHoursAdd = item.BaseHoursAdd,
SName = item.SName
});
}
//试听课
if (rclist.Any())
{
decimal ClassHourMinute = configModel.BasicMinutes;//课时分钟数
decimal Hours = Math.Round(1 * ClassHourMinute / 60, 2, MidpointRounding.AwayFromZero);//小时
foreach (var item in rclist)
{
RList.Add(new RB_Teaching_BonusDetail_ViewModel
{
Date = item.ClassDate,
TimeBucket = item.ClassTime + "~" + item.EndTime,
TeacherId = item.TeacherId,
TeacherName = item.TeacherName,
ClassId = item.ReserveClassId,
ClassName = "试听课",
CouseId = item.TrialLessonId,
CourseName = item.LessonName,
KSNum = 1,
Hours = Hours,
HourMoney = Math.Round(item.ClassHourFee / Hours, 2, MidpointRounding.AwayFromZero),
UnitPrice = item.ClassHourFee,
Money = item.ClassHourFee,
CouseClassify = 2,
BaseHoursEnabled = item.BaseHoursEnabled,
EnableTime = item.EnableTime,
BaseHoursAdd = item.BaseHoursAdd,
SName = item.SName
});
}
}
RList = RList.OrderBy(x => x.Date).ToList();
//初始化 基础课时
DateTime STime = Convert.ToDateTime(startMonth);
while (true)
{
DateTime ETime = STime.AddMonths(1).AddDays(-1);
var mlist = RList.Where(x => x.Date >= STime && x.Date <= ETime && x.BaseHoursEnabled == 1 && STime >= Convert.ToDateTime(x.EnableTime)).ToList();
#region 基础课时处理
decimal DeductionMoney = 0;
if (configModel.BasicHours > 0 && mlist.Any())
{
//遍历所有老师
List<int> TeacherIdList = mlist.Select(x => x.TeacherId).Distinct().ToList();
foreach (var item in TeacherIdList)
{
var qblist = mlist.Where(x => x.TeacherId == item).OrderBy(x => x.Date).ThenBy(x => x.UnitPrice).ToList();
decimal BasicHours = (decimal)(configModel.BasicHours + (mlist.FirstOrDefault()?.BaseHoursAdd ?? 0));
foreach (var qitem in qblist)
{
decimal CourseHour = 0, DeductionHour = 0;
if (BasicHours > qitem.KSNum)
{
DeductionHour = qitem.KSNum;
BasicHours -= qitem.KSNum;
DeductionMoney += qitem.KSNum * qitem.UnitPrice;
}
else
{
DeductionHour = BasicHours;
CourseHour = qitem.KSNum - BasicHours;
DeductionMoney += BasicHours * qitem.UnitPrice;
BasicHours = 0;
}
qitem.KSNum = CourseHour;
qitem.DKNum = DeductionHour;
qitem.Money = CourseHour * qitem.UnitPrice;
if (BasicHours <= 0)
{
break;
}
}
}
}
#endregion
if (STime.Month == Convert.ToDateTime(endMonth).Month)
{
break;
}
STime = STime.AddMonths(1);
}
}
return RList;
}
/// <summary>
/// 导出excel
/// </summary>
/// <param name="teacherId"></param>
/// <param name="schoolId"></param>
/// <param name="classId"></param>
/// <param name="startMonth"></param>
/// <param name="endMonth"></param>
/// <param name="groupId"></param>
/// <returns></returns>
public List<ExcelDataSource> GetTeacherConsumptionHoursDetialPageListToExcel(int teacherId, int schoolId, int classId, string startMonth, string endMonth, int groupId)
{
List<ExcelDataSource> RDateList = new List<ExcelDataSource>();
List<RB_Teaching_BonusDetail_ViewModel> RList = new List<RB_Teaching_BonusDetail_ViewModel>();
//根据学生签到表 查询
var list = class_CheckRepository.GetTeacherConsumptionHoursDetialList(teacherId.ToString(), schoolId, classId, startMonth, endMonth, groupId);
//查询试听课
var rclist = reserve_ClassRepository.GetTeacherHoursDetialList(teacherId > 0 ? teacherId.ToString() : "", schoolId, 0, startMonth, endMonth, groupId);
if (list.Any() || rclist.Any())
{
//班级类型查询
var cTypeList = class_TypeRepository.GetClassTypeListRepository(new RB_Class_Type_ViewModel() { Group_Id = groupId });
//基础配置
var configModel = class_ConfigRepository.GetClassConfigRepository(new RB_Class_Config_ViewModel() { Group_Id = groupId });
if (configModel == null || configModel.BasicHourFee <= 0 || configModel.BasicMinutes <= 0) { return RDateList; }
decimal TKSNum = 0, TDKNum = 0, THourMoney = 0, THours = 0, TKSMoney = 0;
foreach (var item in list)
{
decimal ClassHourMinute = configModel.BasicMinutes;//课时分钟数
//if (item.ClassHourMinute > 0) { ClassHourMinute = item.ClassHourMinute; }
//上课课时
decimal KSNum = item.CurrentDeductionHours;
if (item.ClassId == 18) { KSNum = 0; }//特殊班级处理
decimal Hours = Math.Round(KSNum * ClassHourMinute / 60, 2, MidpointRounding.AwayFromZero);//小时
var typeModel = cTypeList.Where(x => x.CTypeId == item.ClassType).FirstOrDefault();
decimal unitPrice = configModel.BasicHourFee + (typeModel?.AddHourFee ?? 0) + (item?.BaseHourFee ?? 0) + (item?.CourseAddHoursMoney ?? 0);
//课时费 需要每个班级 单独计算 因为 班级有附加费用
decimal KSMoney = KSNum * unitPrice;
decimal HourMoney = Math.Round(unitPrice / ClassHourMinute * 60, 2, MidpointRounding.AwayFromZero);//没小时课时费
RList.Add(new RB_Teaching_BonusDetail_ViewModel
{
Date = item.ClassDate,
TimeBucket = item.StartDate + "~" + item.EndDate,
TeacherId = item.TeacherId,
TeacherName = item.TeacherName,
ClassId = item.ClassId,
ClassName = item.ClassName,
ClassNo = item.ClassNo,
CouseId = item.CouseId,
CourseName = item.CourseName,
KSNum = KSNum,
Hours = Hours,
HourMoney = HourMoney,
UnitPrice = unitPrice,
Money = KSMoney,
BaseHoursEnabled = item.BaseHoursEnabled,
EnableTime = item.EnableTime,
SName = item.SName
});
}
//试听课
if (rclist.Any())
{
decimal ClassHourMinute = configModel.BasicMinutes;//课时分钟数
decimal Hours = Math.Round(1 * ClassHourMinute / 60, 2, MidpointRounding.AwayFromZero);//小时
foreach (var item in rclist)
{
RList.Add(new RB_Teaching_BonusDetail_ViewModel
{
Date = item.ClassDate,
TimeBucket = item.ClassTime + "~" + item.EndTime,
TeacherId = item.TeacherId,
TeacherName = item.TeacherName,
ClassId = item.ReserveClassId,
ClassName = "试听课",
CouseId = item.TrialLessonId,
CourseName = item.LessonName,
KSNum = 1,
Hours = Hours,
HourMoney = Math.Round(item.ClassHourFee / Hours, 2, MidpointRounding.AwayFromZero),
UnitPrice = item.ClassHourFee,
Money = item.ClassHourFee,
CouseClassify = 2,
BaseHoursEnabled = item.BaseHoursEnabled,
EnableTime = item.EnableTime,
SName = item.SName
});
}
}
RList = RList.OrderBy(x => x.Date).ToList();
//初始化 基础课时
DateTime STime = Convert.ToDateTime(startMonth);
while (true)
{
DateTime ETime = STime.AddMonths(1).AddDays(-1);
var mlist = RList.Where(x => x.Date >= STime && x.Date <= ETime && x.BaseHoursEnabled == 1 && STime >= Convert.ToDateTime(x.EnableTime)).ToList();
#region 基础课时处理
decimal DeductionMoney = 0;
if (configModel.BasicHours > 0 && mlist.Any())
{
//遍历所有老师
List<int> TeacherIdList = mlist.Select(x => x.TeacherId).Distinct().ToList();
foreach (var item in TeacherIdList)
{
var qblist = mlist.Where(x => x.TeacherId == item).OrderBy(x => x.Date).ThenBy(x => x.UnitPrice).ToList();
decimal BasicHours = (decimal)(configModel.BasicHours + (mlist.FirstOrDefault()?.BaseHoursAdd ?? 0));
foreach (var qitem in qblist)
{
decimal CourseHour = 0, DeductionHour = 0;
if (BasicHours > qitem.KSNum)
{
DeductionHour = qitem.KSNum;
BasicHours -= qitem.KSNum;
DeductionMoney += qitem.KSNum * qitem.UnitPrice;
}
else
{
DeductionHour = BasicHours;
CourseHour = qitem.KSNum - BasicHours;
DeductionMoney += BasicHours * qitem.UnitPrice;
BasicHours = 0;
}
qitem.KSNum = CourseHour;
qitem.DKNum = DeductionHour;
qitem.Money = CourseHour * qitem.UnitPrice;
if (BasicHours <= 0)
{
break;
}
}
}
}
#endregion
if (STime.Month == Convert.ToDateTime(endMonth).Month)
{
break;
}
STime = STime.AddMonths(1);
}
foreach (var item in RList) {
ExcelDataSource firstRow = new ExcelDataSource(30)
{
ExcelRows = new List<ExcelColumn>()
{
new ExcelColumn(value: item.Date.ToString("yyyy-MM-dd")){ },
new ExcelColumn(value: item.TimeBucket){ },
new ExcelColumn(value: item.SName){ },
new ExcelColumn(value: item.ClassNo){ },
new ExcelColumn(value: item.ClassName){ },
new ExcelColumn(value: item.CourseName){ },
new ExcelColumn(value: item.TeacherName){ },
new ExcelColumn(value: item.UnitPrice.ToString("#0.00")){ },
new ExcelColumn(value: item.KSNum.ToString("#0.00")){ },
new ExcelColumn(value: item.DKNum.ToString("#0.00")){ },
new ExcelColumn(value: item.Hours.ToString("#0.00")){ },
new ExcelColumn(value: item.HourMoney.ToString("#0.00")){ },
new ExcelColumn(value: item.Money.ToString("#0.00")){ }
}
};
RDateList.Add(firstRow);
TKSNum += item.KSNum;
TDKNum += item.DKNum;
THourMoney += item.HourMoney;
THours += item.Hours;
TKSMoney += item.Money;
}
ExcelDataSource firstRowT = new ExcelDataSource(30)
{
ExcelRows = new List<ExcelColumn>()
{
new ExcelColumn(value: "合计"){ },
new ExcelColumn(value: ""){ },
new ExcelColumn(value: ""){ },
new ExcelColumn(value: ""){ },
new ExcelColumn(value: ""){ },
new ExcelColumn(value: ""){ },
new ExcelColumn(value: ""){ },
new ExcelColumn(value: ""){ },
new ExcelColumn(value: TKSNum.ToString("#0.00")){ },
new ExcelColumn(value: TDKNum.ToString("#0.00")){ },
new ExcelColumn(value: THours.ToString("#0.00")){ },
new ExcelColumn(value: THourMoney.ToString("#0.00")){ },
new ExcelColumn(value: TKSMoney.ToString("#0.00")){ }
}
};
RDateList.Add(firstRowT);
}
return RDateList;
}
/// <summary>
/// 获取学生课时统计明细列表
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <param name="teacherId"></param>
/// <param name="schoolId"></param>
/// <param name="classId"></param>
/// <param name="startMonth"></param>
/// <param name="endMonth"></param>
/// <param name="studentName"></param>
/// <param name="orderId"></param>
/// <param name="contractNo"></param>
/// <param name="group_Id"></param>
/// <returns></returns>
public object GetStudentConsumptionHoursDetialPageList(int pageIndex, int pageSize, out long count, int teacherId, int schoolId, int classId, string startMonth, string endMonth, string studentName, int orderId, string contractNo, int groupId,int StudentId)
{
List<object> RList = new List<object>();
//根据学生签到表 查询
var list = class_CheckRepository.GetStudentConsumptionHoursDetialPageList(pageIndex, pageSize, out count, teacherId, schoolId, classId, startMonth, endMonth, studentName, orderId, contractNo, groupId, StudentId);
if (list.Any())
{
//查询关联销售
string userids = string.Join(",", list.Select(x => x.EnterID).Distinct());
var ulist = accountRepository.GetEmployeeListRepository(new Model.ViewModel.User.Employee_ViewModel() { Group_Id = groupId, QIds = userids });
//查询关联老师
string teacherIds = string.Join(",", list.Where(x => x.EnterID != x.HelpEnterId && x.HelpEnterId > 0).Select(x => x.HelpEnterId).Distinct());
List<Model.ViewModel.User.Employee_ViewModel> teacherList = new List<Model.ViewModel.User.Employee_ViewModel>();
if (!string.IsNullOrEmpty(teacherIds))
{
teacherList = accountRepository.GetEmployeeListRepository(new Model.ViewModel.User.Employee_ViewModel() { Group_Id = groupId, QIds = teacherIds });
}
List<RB_Order_ViewModel> sourceTargetList = new List<RB_Order_ViewModel>();
List<int> idList = new List<int>();
var sourceOrderIdList = Common.ConvertHelper.StringToList(string.Join(",", list.Select(x => x.SourceOrderId)));
if (sourceOrderIdList != null && sourceOrderIdList.Count > 0)
{
idList.AddRange(sourceOrderIdList);
}
var targetOrderIdList = Common.ConvertHelper.StringToList(string.Join(",", list.Select(x => x.TargetOrderId)));
if (targetOrderIdList != null && targetOrderIdList.Count > 0)
{
idList.AddRange(targetOrderIdList);
}
if (idList != null && idList.Count > 0)
{
string ids = string.Join(",", idList);
sourceTargetList = orderRepository.GetOrderListRepository(new RB_Order_ViewModel { QOrderIds = ids });
}
foreach (var item in list)
{
if (item.SourceOrderId > 0)
{
item.SourceClassName = sourceTargetList?.Where(qitem => qitem.OrderId == item.SourceOrderId)?.FirstOrDefault()?.ClassName;
}
if (item.TargetOrderId > 0)
{
item.TargetClassName = sourceTargetList?.Where(qitem => qitem.OrderId == item.TargetOrderId)?.FirstOrDefault()?.ClassName;
}
string SaleName = ulist.Where(x => x.Id == item.EnterID).FirstOrDefault()?.EmployeeName ?? "";
string HelpEnterName = "";
if (item.EnterID != item.HelpEnterId && item.HelpEnterId > 0) {
HelpEnterName = teacherList.Where(x => x.Id == item.HelpEnterId).FirstOrDefault()?.EmployeeName ?? "";
}
decimal UnitPrice = Math.Round(item.UnitPrice, 6, MidpointRounding.AwayFromZero);
RList.Add(new
{
ClassDate = item.ClassDate.ToString("yyyy-MM-dd"),
TimeBucket = item.StartDate + "~" + item.EndDate,
item.OrderGuestId,
item.GuestName,
item.TeacherId,
item.TeacherName,
item.ClassId,
item.ClassName,
item.ClassNo,
item.CouseId,
item.CourseName,
item.OrderId,
item.ContractNo,
item.CurrentDeductionHours,
UnitPrice,
XHMoney = Math.Round(item.UnitPrice * item.CurrentDeductionHours, 6, MidpointRounding.AwayFromZero),
SaleName,
HelpEnterName,
item.JoinType,
item.TargetJoinType,
item.SourceOrderId,
item.TargetOrderId,
item.SourceClassName,
item.TargetClassName,
});
}
}
return RList;
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="teacherId"></param>
/// <param name="schoolId"></param>
/// <param name="classId"></param>
/// <param name="startMonth"></param>
/// <param name="endMonth"></param>
/// <param name="studentName"></param>
/// <param name="orderId"></param>
/// <param name="contractNo"></param>
/// <param name="groupId"></param>
/// <returns></returns>
public List<ExcelDataSource> GetStudentConsumptionHoursDetialPageListToExcel(int teacherId, int schoolId, int classId, string startMonth, string endMonth, string studentName, int orderId, string contractNo, int groupId)
{
List<ExcelDataSource> RList = new List<ExcelDataSource>();
//根据学生签到表 查询
var list = class_CheckRepository.GetStudentConsumptionHoursDetialList(teacherId, schoolId, classId, startMonth, endMonth, studentName, orderId, contractNo, groupId);
if (list.Any())
{
//查询关联销售
string userids = string.Join(",", list.Select(x => x.EnterID).Distinct());
var ulist = accountRepository.GetEmployeeListRepository(new Model.ViewModel.User.Employee_ViewModel() { Group_Id = groupId, QIds = userids });
//查询关联老师
string teacherIds = string.Join(",", list.Where(x => x.EnterID != x.HelpEnterId && x.HelpEnterId > 0).Select(x => x.HelpEnterId).Distinct());
List<Model.ViewModel.User.Employee_ViewModel> teacherList = new List<Model.ViewModel.User.Employee_ViewModel>();
if (!string.IsNullOrEmpty(teacherIds))
{
teacherList = accountRepository.GetEmployeeListRepository(new Model.ViewModel.User.Employee_ViewModel() { Group_Id = groupId, QIds = teacherIds });
}
decimal TcdHours = 0, TPrice = 0;
foreach (var item in list)
{
string SaleName = ulist.Where(x => x.Id == item.EnterID).FirstOrDefault()?.EmployeeName ?? "";
string HelpEnterName = "";
if (item.EnterID != item.HelpEnterId && item.HelpEnterId > 0)
{
HelpEnterName = teacherList.Where(x => x.Id == item.HelpEnterId).FirstOrDefault()?.EmployeeName ?? "";
}
decimal UnitPrice = Math.Round(item.UnitPrice, 6, MidpointRounding.AwayFromZero);
ExcelDataSource firstRow = new ExcelDataSource(30)
{
ExcelRows = new List<ExcelColumn>()
{
new ExcelColumn(value: item.OrderId.ToString()){ },
new ExcelColumn(value: item.ContractNo){ },
new ExcelColumn(value: item.ClassDate.ToString("yyyy-MM-dd")){ },
new ExcelColumn(value: item.StartDate + "~" + item.EndDate){ },
new ExcelColumn(value: item.GuestName){ },
new ExcelColumn(value: item.ClassNo){ },
new ExcelColumn(value: item.ClassName){ },
new ExcelColumn(value: item.CourseName){ },
new ExcelColumn(value: item.TeacherName){ },
new ExcelColumn(value: item.CurrentDeductionHours.ToString("#0.00")){ },
new ExcelColumn(value: UnitPrice.ToString("#0.000000")){ },
new ExcelColumn(value: Math.Round(item.UnitPrice * item.CurrentDeductionHours,6,MidpointRounding.AwayFromZero).ToString("#0.000000")){ },
new ExcelColumn(value: SaleName){ },
new ExcelColumn(value: HelpEnterName){ }
}
};
RList.Add(firstRow);
TcdHours += item.CurrentDeductionHours;
TPrice += Math.Round(item.UnitPrice * item.CurrentDeductionHours, 6, MidpointRounding.AwayFromZero);
}
ExcelDataSource firstRowT = new ExcelDataSource(30)
{
ExcelRows = new List<ExcelColumn>()
{
new ExcelColumn(value: "合计"){ },
new ExcelColumn(value: ""){ },
new ExcelColumn(value: ""){ },
new ExcelColumn(value: ""){ },
new ExcelColumn(value: ""){ },
new ExcelColumn(value: ""){ },
new ExcelColumn(value: ""){ },
new ExcelColumn(value: ""){ },
new ExcelColumn(value: ""){ },
new ExcelColumn(value: TcdHours.ToString("#0.00")){ },
new ExcelColumn(value: ""){ },
new ExcelColumn(value: TPrice.ToString("#0.00")){ },
new ExcelColumn(value: ""){ },
new ExcelColumn(value: ""){ }
}
};
RList.Add(firstRowT);
}
return RList;
}
/// <summary>
/// 获取销售业绩排名统计
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <param name="saleId"></param>
/// <param name="schoolId"></param>
/// <param name="startMonth"></param>
/// <param name="endMonth"></param>
/// <param name="group_Id"></param>
/// <returns></returns>
public List<RB_Order_ViewModel> GetSaleAchievementsRankStatistics(int pageIndex, int pageSize, out long count, int saleId, int schoolId, string startMonth, string endMonth, int group_Id)
{
var list = orderRepository.GetSaleAchievementsRankStatistics(pageIndex, pageSize, out count, saleId, schoolId, startMonth, endMonth, group_Id);
if (list.Any()) {
string userIds = string.Join(",", list.Select(x => x.EnterID).Distinct());
var ulist = accountRepository.GetEmployeeListRepository(new Model.ViewModel.User.Employee_ViewModel() { Group_Id = group_Id, QIds = userIds });
foreach (var item in list) {
item.EnterName = ulist.Where(x => x.Id == item.EnterID).FirstOrDefault()?.EmployeeName ?? "";
}
}
return list;
}
/// <summary>
/// 获取销售业绩排名 （单据版）
/// </summary>
/// <param name="startMonth"></param>
/// <param name="endMonth"></param>
/// <param name="group_Id"></param>
/// <returns></returns>
public List<RB_Order_ViewModel> GetSaleAchievementsRankStatistics_V2(string startMonth, string endMonth, int group_Id)
{
List<RB_Order_ViewModel> RList = new List<RB_Order_ViewModel>();
List<int> empIdList = new List<int>();//业务员列表
//首先获取当月新增订单
var olist = orderRepository.GetNewOrderStatistics(startMonth, endMonth, group_Id);//业务员 应收 报名人数 订单数量
if (olist.Any()) {
empIdList.AddRange(olist.Select(x => x.EnterID));
}
//查询当月已上课金额
var ylist = orderRepository.GetOrderClassHoursMoneyList(startMonth, endMonth, group_Id);//业务员 已上课金额
if (ylist.Any())
{
empIdList.AddRange(ylist.Select(x => x.EnterID));
}
//查询当月结课人数
var jlist = orderRepository.GetOrderGuestEndClassList(startMonth, endMonth, group_Id);//业务员 结课数量
if (jlist.Any())
{
empIdList.AddRange(jlist.Select(x => x.EnterID));
}
#region 财务单据收入
//直接查询关联订单的单据
var flist = RB_FinanceRepository.GetListForReport(new RB_Finance_Extend() { RB_Group_Id = group_Id, IsSelectOrder = 1, FinanceType = 2, sAduitDate = Convert.ToDateTime(startMonth), eAduitDate = Convert.ToDateTime(endMonth) });
if (flist.Any()) {
empIdList.AddRange(flist.Select(x => x.CreateBy ?? 0));
}
#endregion
//查询人员
empIdList = empIdList.Where(x => x > 0).Distinct().ToList();
if (empIdList.Any()) {
string userIds = string.Join(",", empIdList);
var ulist = accountRepository.GetEmployeeListRepository(new Model.ViewModel.User.Employee_ViewModel() { Group_Id = group_Id, QIds = userIds });
foreach (var umodel in ulist) {
var omodel = olist.Where(x => x.EnterID == umodel.Id).FirstOrDefault();
var ymodel = ylist.Where(x => x.EnterID == umodel.Id).FirstOrDefault();
var jmodel = jlist.Where(x => x.EnterID == umodel.Id).FirstOrDefault();
var fclist = flist.Where(x => x.CreateBy == umodel.Id).ToList();
decimal Income = fclist.Where(x => x.Type == WFTempLateClassEnum.IN).Sum(x => x.Money ?? 0);
decimal PlatformTax = fclist.Where(x => x.Type == WFTempLateClassEnum.IN).GroupBy(x => new { x.FrID, x.Fee }).Sum(x => x.Key.Fee ?? 0);
decimal Refund = fclist.Where(x => x.Type == WFTempLateClassEnum.OUT).Sum(x => x.Money ?? 0);
RList.Add(new RB_Order_ViewModel()
{
EnterID = umodel.Id,
EnterName = umodel.EmployeeName,
DeptName = umodel.DeptName,
SchoolName = umodel.SchoolName,
PreferPrice = omodel?.PreferPrice ?? 0,
Income = Income,
PlatformTax = PlatformTax,
Refund = Refund,
YSKMoney = ymodel?.YSKMoney ?? 0,
GuestNum = omodel?.GuestNum ?? 0,
JKNum = jmodel?.JKNum ?? 0,
CancelNum = omodel?.CancelNum ?? 0
});
}
}
RList = RList.OrderByDescending(x => x.Income).ThenBy(x => x.PreferPrice).ToList();
return RList;
}
/// <summary>
/// 获取Excel导出
/// </summary>
/// <param name="saleId"></param>
/// <param name="schoolId"></param>
/// <param name="startMonth"></param>
/// <param name="endMonth"></param>
/// <param name="group_Id"></param>
/// <returns></returns>
public List<RB_Order_ViewModel> GetSaleAchievementsRankStatisticsToExcel(int saleId, int schoolId, string startMonth, string endMonth, int group_Id)
{
var list = orderRepository.GetSaleAchievementsRankStatisticsToList(saleId, schoolId, startMonth, endMonth, group_Id);
if (list.Any())
{
string userIds = string.Join(",", list.Select(x => x.EnterID).Distinct());
var ulist = accountRepository.GetEmployeeListRepository(new Model.ViewModel.User.Employee_ViewModel() { Group_Id = group_Id, QIds = userIds });
foreach (var item in list)
{
item.EnterName = ulist.Where(x => x.Id == item.EnterID).FirstOrDefault()?.EmployeeName ?? "";
}
}
return list;
}
#endregion
#region 班级相关统计
/// <summary>
/// 班级月度课耗统计
/// </summary>
/// <param name="classId"></param>
/// <param name="userInfo"></param>
/// <returns></returns>
public object GetClassMonthStatistics(int classId, UserInfo userInfo)
{
var classModel = classRepository.GetEntity(classId);
if (classModel == null) { return ""; }
string StartTime = classModel.OpenTime.ToString("yyyy-MM-dd");
string EndTime = DateTime.Now.ToString("yyyy-MM-dd");
#region 班级下老师的课时费
List<RB_Teaching_BonusDetail_ViewModel> TeacherHoursList = new List<RB_Teaching_BonusDetail_ViewModel>();
var TeacherAlist = class_CheckRepository.GetTeacherConsumptionHoursDetialList("", -1, classId, StartTime, EndTime, userInfo.Group_Id);
if (TeacherAlist.Any())
{
//上这班的老师
string teacherIds = string.Join(",", TeacherAlist.Select(x => x.TeacherId).Distinct());
//根据学生签到表 查询
var list = class_CheckRepository.GetTeacherConsumptionHoursDetialList(teacherIds, -1, 0, StartTime, EndTime, userInfo.Group_Id);
if (list.Any())
{
//班级类型查询
var cTypeList = class_TypeRepository.GetClassTypeListRepository(new RB_Class_Type_ViewModel() { Group_Id = userInfo.Group_Id });
//基础配置
var configModel = class_ConfigRepository.GetClassConfigRepository(new RB_Class_Config_ViewModel() { Group_Id = userInfo.Group_Id });
if (configModel == null || configModel.BasicHourFee <= 0 || configModel.BasicMinutes <= 0) { return TeacherHoursList; }
foreach (var item in list)
{
//上课课时
decimal KSNum = item.CurrentDeductionHours;//签到课时长度
if (item.ClassId == 18) { KSNum = 0; }// 18班 特殊处理
decimal ClassHourMinute = configModel.BasicMinutes;//课时分钟数
//if (item.ClassHourMinute > 0) { ClassHourMinute = item.ClassHourMinute; }
decimal Hours = Math.Round(KSNum * ClassHourMinute / 60, 2, MidpointRounding.AwayFromZero);//小时
var typeModel = cTypeList.Where(x => x.CTypeId == item.ClassType).FirstOrDefault();
decimal unitPrice = configModel.BasicHourFee + (typeModel?.AddHourFee ?? 0) + (item?.BaseHourFee ?? 0);
//课时费 需要每个班级 单独计算 因为 班级有附加费用
decimal KSMoney = KSNum * unitPrice;
decimal HourMoney = Math.Round(unitPrice / ClassHourMinute * 60, 2, MidpointRounding.AwayFromZero);//每小时课时费
TeacherHoursList.Add(new RB_Teaching_BonusDetail_ViewModel
{
Date = item.ClassDate,
TimeBucket = item.StartDate + "~" + item.EndDate,
TeacherId = item.TeacherId,
TeacherName = item.TeacherName,
ClassId = item.ClassId,
ClassName = item.ClassName,
ClassNo = item.ClassNo,
CouseId = item.CouseId,
CourseName = item.CourseName,
KSNum = KSNum,
Hours = Hours,
HourMoney = HourMoney,
UnitPrice = unitPrice,
Money = KSMoney,
BaseHoursEnabled = item.BaseHoursEnabled,
EnableTime = item.EnableTime
});
}
//初始化 基础课时
DateTime STime = Convert.ToDateTime(Convert.ToDateTime(StartTime).ToString("yyyy-MM") + "-01");
while (true)
{
DateTime ETime = STime.AddMonths(1).AddDays(-1);
var mlist = TeacherHoursList.Where(x => x.Date >= STime && x.Date <= ETime && x.BaseHoursEnabled == 1 && STime >= Convert.ToDateTime(x.EnableTime)).ToList();
#region 基础课时处理
decimal DeductionMoney = 0;
if (configModel.BasicHours > 0 && mlist.Any())
{
//遍历所有老师
List<int> TeacherIdList = mlist.Select(x => x.TeacherId).Distinct().ToList();
foreach (var item in TeacherIdList)
{
var qblist = mlist.Where(x => x.TeacherId == item).OrderBy(x => x.Date).ThenBy(x => x.UnitPrice).ToList();
decimal BasicHours = (decimal)(configModel.BasicHours + (mlist.FirstOrDefault()?.BaseHoursAdd ?? 0));
foreach (var qitem in qblist)
{
decimal CourseHour = 0, DeductionHour = 0;
if (BasicHours > qitem.KSNum)
{
DeductionHour = qitem.KSNum;
BasicHours -= qitem.KSNum;
DeductionMoney += qitem.KSNum * qitem.UnitPrice;
}
else
{
DeductionHour = BasicHours;
CourseHour = qitem.KSNum - BasicHours;
DeductionMoney += BasicHours * qitem.UnitPrice;
BasicHours = 0;
}
qitem.KSNum = CourseHour;
qitem.DKNum = DeductionHour;
qitem.Money = CourseHour * qitem.UnitPrice;
if (BasicHours <= 0)
{
break;
}
}
}
}
#endregion
if (STime.ToString("yyyy-MM") == Convert.ToDateTime(EndTime).ToString("yyyy-MM"))
{
break;
}
STime = STime.AddMonths(1);
}
}
TeacherHoursList = TeacherHoursList.Where(x => x.ClassId == classId).ToList();
}
#endregion
#region 班级下学生的课耗
List<RB_Teaching_BonusDetail_ViewModel> StudentHoursList = new List<RB_Teaching_BonusDetail_ViewModel>();
//根据学生签到表 查询
var stulist = class_CheckRepository.GetStudentConsumptionHoursDetialList(0, -1, classId, StartTime, EndTime, "", 0, "", userInfo.Group_Id);
if (stulist.Any())
{
foreach (var item in stulist)
{
decimal UnitPrice = Math.Round(item.UnitPrice, 6, MidpointRounding.AwayFromZero);
StudentHoursList.Add(new RB_Teaching_BonusDetail_ViewModel
{
Date = item.ClassDate,
TimeBucket = item.StartDate + "~" + item.EndDate,
TeacherId = item.OrderGuestId,
TeacherName = item.GuestName,
ClassId = item.ClassId,
ClassName = item.ClassName,
ClassNo = item.ClassNo,
CouseId = item.CouseId,
CourseName = item.CourseName,
KSNum = item.CurrentDeductionHours,
Hours = 0,
HourMoney = 0,
UnitPrice = UnitPrice,
Money = Math.Round(item.UnitPrice * item.CurrentDeductionHours, 6, MidpointRounding.AwayFromZero)
});
}
}
#endregion
List<object> RList = new List<object>();
if (TeacherHoursList.Any())
{
#region 进行月份汇总
string MaxTime = TeacherHoursList.Max(x => x.Date).ToString("yyyy-MM");
//初始化 基础课时
DateTime STime = Convert.ToDateTime(Convert.ToDateTime(StartTime).ToString("yyyy-MM") + "-01");
while (true)
{
DateTime ETime = STime.AddMonths(1).AddDays(-1);
var teacherlist = TeacherHoursList.Where(x => x.Date >= STime && x.Date <= ETime).ToList();
var teacherIdList = teacherlist.Select(x => x.TeacherId).Distinct();
List<object> tRList = new List<object>();
foreach (var qitem in teacherIdList) {
var tlist = teacherlist.Where(x => x.TeacherId == qitem).ToList();
tRList.Add(new
{
TeacherId = qitem,
TeacherName = tlist.FirstOrDefault()?.TeacherName ?? "",
HoursNum = tlist.Sum(x => x.KSNum),
BaseHoursNum = tlist.Sum(x=>x.DKNum),
HoursMoney = tlist.Sum(x => x.Money)
});
}
var studlist = StudentHoursList.Where(x => x.Date >= STime && x.Date <= ETime).ToList();
var studIdList = studlist.Select(x => x.TeacherId).Distinct();
int StuNum = studIdList.Count();
decimal StuSumHours = studlist.Sum(x => x.KSNum);
decimal StuSumMoney = studlist.Sum(x => x.Money);
List<object> sRList = new List<object>();
foreach (var qitem in studIdList) {
var slist = studlist.Where(x => x.TeacherId == qitem).ToList();
sRList.Add(new
{
StudentId = qitem,
StudentName = slist.FirstOrDefault()?.TeacherName ?? "",
HoursNum = slist.Sum(x => x.KSNum),
HoursMoney = slist.Sum(x => x.Money)
});
}
RList.Add(new
{
Month = STime.ToString("yyyy-MM"),
TeacherList = tRList,
StuNum,
StuSumHours,
StuSumMoney,
StudentList = sRList
});
if (STime.ToString("yyyy-MM") == MaxTime)
{
break;
}
STime = STime.AddMonths(1);
}
#endregion
}
return RList;
}
/// <summary>
/// 获取班级月度分摊统计
/// </summary>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <param name="userInfo"></param>
/// <returns></returns>
public object GetAllClassNowMonthStatistics(string startTime, string endTime, int classId, string classNo, int schoolId, UserInfo userInfo)
{
List<object> RList = new List<object>();
List<int> ClassIdArr = new List<int>();
//首先根据签到表 查询出所有的班级 并分页
var list = class_CheckRepository.GetAllClassNowMonthStatistics(startTime, endTime, classId, classNo, schoolId, userInfo.Group_Id);
if (list.Any()) {
ClassIdArr.AddRange(list.Select(x => x.ClassId));
}
//查询时间范围内 除订单外 班级下其他订单 的其他收入 + 其他成本支出
string datebaseStr = Config.ReadConfigKey("EduDateBase");//教育数据库
var financeList = RB_FinanceRepository.GetListForReportClass(new RB_Finance_Extend() { RB_Group_Id = userInfo.Group_Id, RB_Branch_Id = schoolId, TCID = -2, sAduitDate = Convert.ToDateTime(startTime), eAduitDate = Convert.ToDateTime(endTime), FinanceType = 2, OrderSource = OrderResourceEnum.Education }, datebaseStr);
if (financeList.Any()) {
ClassIdArr.AddRange(financeList.Select(x => x.TCID ?? 0));
}
string STime = Convert.ToDateTime(startTime).AddMonths(-1).ToString("yyyy-MM-dd");
string ETime = Convert.ToDateTime(endTime).AddMonths(-1).ToString("yyyy-MM-dd");
var teacherBonusList = teaching_BonusDetailRepository.GetTeacherBonusForDate(userInfo.Group_Id, schoolId, "", STime, ETime);//查询所有班级需要发放
if (teacherBonusList.Any()) {
ClassIdArr.AddRange(teacherBonusList.Select(x => x.ClassId));
}
var meritsProfitList = teaching_PerfRepository.GetTeacherPerfForDate(userInfo.Group_Id, schoolId, "", startTime, endTime);
if (meritsProfitList.Any()) {
ClassIdArr.AddRange(meritsProfitList.Select(x => x.ClassId));
}
var saleList = sell_Commission_DetailsRepository.GetSellCommissionForDate(userInfo.Group_Id, schoolId, "", STime, ETime);
if (saleList.Any()) {
ClassIdArr.AddRange(saleList.Select(x => x.ClassId));
}
ClassIdArr = ClassIdArr.Distinct().ToList();
List<RB_Class_ViewModel> CAlllist = new List<RB_Class_ViewModel>();
if (ClassIdArr.Any())
{
//再次查询所有的班级
CAlllist = classRepository.GetClassListRepository(new RB_Class_ViewModel() { Group_Id = userInfo.Group_Id, Q_ClassIds = string.Join(",", ClassIdArr) });
}
if (CAlllist.Any())
{
string classIds = string.Join(",", CAlllist.Select(x => x.ClassId));
//查询时间范围内 所有班级的学生上课金额
List<RB_Teaching_BonusDetail_ViewModel> StudentHoursList = new List<RB_Teaching_BonusDetail_ViewModel>();
//根据学生签到表 查询
var stulist = class_CheckRepository.GetStudentConsumptionHoursDetialList(0, -1, 0, startTime, endTime, "", 0, "", userInfo.Group_Id, classIds);
if (stulist.Any())
{
foreach (var item in stulist)
{
decimal UnitPrice = Math.Round(item.UnitPrice, 6, MidpointRounding.AwayFromZero);
StudentHoursList.Add(new RB_Teaching_BonusDetail_ViewModel
{
Date = item.ClassDate,
TimeBucket = item.StartDate + "~" + item.EndDate,
TeacherId = item.OrderGuestId,
TeacherName = item.GuestName,
ClassId = item.ClassId,
ClassName = item.ClassName,
ClassNo = item.ClassNo,
CouseId = item.CouseId,
CourseName = item.CourseName,
KSNum = item.CurrentDeductionHours,
Hours = 0,
HourMoney = 0,
UnitPrice = UnitPrice,
Money = Math.Round(item.UnitPrice * item.CurrentDeductionHours, 6, MidpointRounding.AwayFromZero)
});
}
}
//查询时间范围内 所有班级的销售提成 ， 老师课时费 ， 老师绩效
//var teacherBonusList = teaching_BonusDetailRepository.GetTeacherBonusForDate(userInfo.Group_Id, classIds, STime, ETime);
//var meritsProfitList = teaching_PerfRepository.GetTeacherPerfForDate(userInfo.Group_Id, classIds, startTime, endTime);
//var saleList = sell_Commission_DetailsRepository.GetSellCommissionForDate(userInfo.Group_Id, classIds, STime, ETime);
foreach (var item in CAlllist) {
#region 学生课耗
var slist = StudentHoursList.Where(x => x.ClassId == item.ClassId).ToList();
decimal TStuMoney = slist.Sum(x => x.Money);//上课总金额
int TStuNum = slist.Select(x => x.TeacherId).Distinct().Count();//上课学生数量
var StuList = slist.GroupBy(x => new { x.TeacherId, x.TeacherName }).Select(g => new
{
StuId = g.Key.TeacherId,
StuName = g.Key.TeacherName,
KSNum = g.Sum(t => t.KSNum),
Money = g.Sum(t => t.Money)
});
#endregion
#region 提成
decimal BonusMoney = teacherBonusList.Where(x => x.ClassId == item.ClassId).FirstOrDefault()?.Money ?? 0;//老师课时费
decimal PerfMoney = meritsProfitList.Where(x => x.ClassId == item.ClassId).FirstOrDefault()?.Money ?? 0;//老师绩效
decimal SellMoney = saleList.Where(x => x.ClassId == item.ClassId).FirstOrDefault()?.CurrentPeriodMoney ?? 0;//销售提成
#endregion
#region 单据
var flist = financeList.Where(x => x.TCID == item.ClassId).ToList();
decimal OtherIncome = flist.Where(y => y.Type == WFTempLateClassEnum.IN && y.Is_Cashier == 1 && y.OrderID <= 0).Sum(x => x.Money ?? 0);
decimal Fee = flist.Where(x => x.Type == WFTempLateClassEnum.IN && x.Is_Cashier == 1 && x.OrderID <= 0 && x.Fee > 0).GroupBy(x => new { x.FrID, x.Fee }).Sum(x => x.Key.Fee ?? 0);
OtherIncome -= Fee;
decimal OtherCost = flist.Where(y => y.Type == WFTempLateClassEnum.OUT && y.OrderID <= 0).Sum(x => x.Money ?? 0);//支出也要排除订单订单退款
#endregion
decimal THoursNum = list.Where(x => x.ClassId == item.ClassId).FirstOrDefault()?.CurrentDeductionHours ?? 0;
object RModel = new
{
item.ClassId,
item.ClassNo,
OrderIds = "",
item.ClassName,
ClassType = 1,
THoursNum,
TStuNum,
TStuMoney,
StuList,
BonusMoney,
PerfMoney,
SellMoney,
OtherIncome,
OtherCost
};
RList.Add(RModel);
}
}
//查询留学的
List<int> SourceIdArr = new List<int>();
var lxOrderList = orderRepository.GetListForReport(new RB_Order_ViewModel() { Group_Id = userInfo.Group_Id, School_Id = schoolId, OrderType = Common.Enum.Sale.OrderTypeEnum.StudyabroadOrder, Q_OrderState = 1, ConfirmSTime = startTime, ConfirmETime = endTime });
if (lxOrderList.Any()) {
SourceIdArr.AddRange(lxOrderList.Select(x => x.SourceId).Distinct());
}
//财务单据
var lxFinanceList = RB_FinanceRepository.GetListForReportClass(new RB_Finance_Extend() { RB_Group_Id = userInfo.Group_Id, RB_Branch_Id = schoolId, TCID = -2, sAduitDate = Convert.ToDateTime(startTime), eAduitDate = Convert.ToDateTime(endTime), FinanceType = 2, OrderSource = OrderResourceEnum.EducationStudy }, datebaseStr);
if (lxFinanceList.Any())
{
SourceIdArr.AddRange(lxFinanceList.Select(x => x.TCID ?? 0));
}
List<RB_Order_Guest_ViewModel> lxGuestList = new List<RB_Order_Guest_ViewModel>();
List<Model.ViewModel.StudyAbroad.RB_StudyAbroad_ViewModel> saList = new List<Model.ViewModel.StudyAbroad.RB_StudyAbroad_ViewModel>();
if (SourceIdArr.Any())
{
if (lxOrderList.Any())
{
string orderIds = string.Join(",", lxOrderList.Select(x => x.OrderId));
//查询学生
lxGuestList = order_GuestRepository.GetOrderGuestListRepository(new RB_Order_Guest_ViewModel() { Group_Id = userInfo.Group_Id, OrderIds = orderIds });
}
string saIds = string.Join(",", SourceIdArr.Distinct());
//查询留学产品
saList = studyAbroadRepository.GetStudyAbroadListExtRepository(new Model.ViewModel.StudyAbroad.RB_StudyAbroad_ViewModel() { Group_Id = userInfo.Group_Id, QIds = saIds });
}
foreach (var item in saList) {
#region 订单
var olist = lxOrderList.Where(x => x.SourceId == item.Id).ToList();
decimal ShiShou = olist.Sum(item => item.Income - item.Refund + item.PlatformTax);
int GuestNum = olist.Sum(item => item.GuestNum);
var glist = lxGuestList.Where(x => olist.Select(x => x.OrderId).Contains(x.OrderId)).Select(x => new
{
StuId = x.Id,
StuName = x.GuestName
});
#endregion
#region 财务单据
var lxflist = lxFinanceList.Where(x => x.TCID == item.Id).ToList();
//排序订单单据
var cfinanceList = lxflist.Where(x => x.OrderID == 0 && x.Type == WFTempLateClassEnum.IN).ToList();
decimal QTShouRu = cfinanceList.Where(y => y.Is_Cashier == 1).Sum(x => x.Money ?? 0);
QTShouRu -= cfinanceList.Where(x => x.Is_Cashier == 1 && x.Fee > 0).GroupBy(x => new { x.FrID, x.Fee }).Sum(x => x.Key.Fee ?? 0);
//支出
decimal TiCheng = 0;//销售提成 -留学提成暂定
decimal ClassFee = 0;//老师提成 -留学暂定
decimal JiXiaoMoney = 0;//绩效提成 -留学暂定
var ofinanceList = lxflist.Where(x => x.OrderID == 0 && x.Type == WFTempLateClassEnum.OUT).ToList();
decimal QTZhiChu = ofinanceList.Sum(x => x.Money ?? 0);
#endregion
object RModel = new
{
ClassId = item.Id,
ClassNo = "",
OrderIds = string.Join(",", olist.Select(x => x.OrderId)),
ClassName = item.Name,
ClassType = 2,
THoursNum = 0,
TStuNum = GuestNum,
TStuMoney = ShiShou,
StuList = glist,
BonusMoney = ClassFee,
PerfMoney = JiXiaoMoney,
SellMoney = TiCheng,
OtherIncome = QTShouRu,
OtherCost = QTZhiChu
};
RList.Add(RModel);
}
return RList;
}
#endregion
#region 老师-月度耗课
/// <summary>
/// 班级月度课耗统计
/// </summary>
/// <param name="classId"></param>
/// <param name="userInfo"></param>
/// <returns></returns>
public object GetTeacherClassMonthStatistics(int classId, UserInfo userInfo)
{
var classModel = classRepository.GetEntity(classId);
if (classModel == null) { return ""; }
string StartTime = classModel.OpenTime.ToString("yyyy-MM-dd");
string EndTime = DateTime.Now.ToString("yyyy-MM-dd");
#region 班级下老师的课时费
List<RB_Teaching_BonusDetail_ViewModel> TeacherHoursList = new List<RB_Teaching_BonusDetail_ViewModel>();
//根据学生签到表 查询
var list = class_CheckRepository.GetTeacherConsumptionHoursDetialList(userInfo.AccountId.ToString(), -1, classId, StartTime, EndTime, userInfo.Group_Id);
if (list.Any())
{
//班级类型查询
var cTypeList = class_TypeRepository.GetClassTypeListRepository(new RB_Class_Type_ViewModel() { Group_Id = userInfo.Group_Id });
//基础配置
var configModel = class_ConfigRepository.GetClassConfigRepository(new RB_Class_Config_ViewModel() { Group_Id = userInfo.Group_Id });
if (configModel == null || configModel.BasicHourFee <= 0 || configModel.BasicMinutes <= 0) { return TeacherHoursList; }
foreach (var item in list)
{
int Minutes = (int)(Convert.ToDateTime("2021-06-29 " + item.EndDate) - Convert.ToDateTime("2021-06-29 " + item.StartDate)).TotalMinutes;
decimal Hours = Math.Round(Convert.ToDecimal(Minutes) / 60, 2, MidpointRounding.AwayFromZero);//小时
//上课课时
decimal KSNum = item.CurrentDeductionHours;//签到课时长度
var typeModel = cTypeList.Where(x => x.CTypeId == item.ClassType).FirstOrDefault();
decimal unitPrice = configModel.BasicHourFee + (typeModel?.AddHourFee ?? 0) + (item?.BaseHourFee ?? 0);
//课时费 需要每个班级 单独计算 因为 班级有附加费用
decimal KSMoney = KSNum * unitPrice;
decimal ClassHourMinute = configModel.BasicMinutes;//课时分钟数
if (item.ClassHourMinute > 0) { ClassHourMinute = item.ClassHourMinute; }
decimal HourMoney = Math.Round(unitPrice / ClassHourMinute * 60, 2, MidpointRounding.AwayFromZero);//每小时课时费
TeacherHoursList.Add(new RB_Teaching_BonusDetail_ViewModel
{
Date = item.ClassDate,
TimeBucket = item.StartDate + "~" + item.EndDate,
TeacherId = item.TeacherId,
TeacherName = item.TeacherName,
ClassId = item.ClassId,
ClassName = item.ClassName,
ClassNo = item.ClassNo,
CouseId = item.CouseId,
CourseName = item.CourseName,
KSNum = KSNum,
Hours = Hours,
HourMoney = HourMoney,
UnitPrice = unitPrice,
Money = KSMoney,
BaseHoursEnabled = item.BaseHoursEnabled,
EnableTime = item.EnableTime
});
}
//初始化 基础课时
DateTime STime = Convert.ToDateTime(Convert.ToDateTime(StartTime).ToString("yyyy-MM") + "-01");
while (true)
{
DateTime ETime = STime.AddMonths(1).AddDays(-1);
var mlist = TeacherHoursList.Where(x => x.Date >= STime && x.Date <= ETime && x.BaseHoursEnabled == 1 && STime >= Convert.ToDateTime(x.EnableTime)).ToList();
#region 基础课时处理
decimal DeductionMoney = 0;
if (configModel.BasicHours > 0 && mlist.Any())
{
//遍历所有老师
List<int> TeacherIdList = mlist.Select(x => x.TeacherId).Distinct().ToList();
foreach (var item in TeacherIdList)
{
var qblist = mlist.Where(x => x.TeacherId == item).OrderBy(x => x.Date).ThenBy(x => x.UnitPrice).ToList();
decimal BasicHours = (decimal)(configModel.BasicHours + (mlist.FirstOrDefault()?.BaseHoursAdd ?? 0));
foreach (var qitem in qblist)
{
decimal CourseHour = 0, DeductionHour = 0;
if (BasicHours > qitem.KSNum)
{
DeductionHour = qitem.KSNum;
BasicHours -= qitem.KSNum;
DeductionMoney += qitem.KSNum * qitem.UnitPrice;
}
else
{
DeductionHour = BasicHours;
CourseHour = qitem.KSNum - BasicHours;
DeductionMoney += BasicHours * qitem.UnitPrice;
BasicHours = 0;
}
qitem.KSNum = CourseHour;
qitem.DKNum = DeductionHour;
qitem.Money = CourseHour * qitem.UnitPrice;
if (BasicHours <= 0)
{
break;
}
}
}
}
#endregion
if (STime.ToString("yyyy-MM") == Convert.ToDateTime(EndTime).ToString("yyyy-MM"))
{
break;
}
STime = STime.AddMonths(1);
}
}
#endregion
#region 班级下学生的课耗
List<RB_Teaching_BonusDetail_ViewModel> StudentHoursList = new List<RB_Teaching_BonusDetail_ViewModel>();
//根据学生签到表 查询
var stulist = class_CheckRepository.GetStudentConsumptionHoursDetialList(userInfo.AccountId, -1, classId, StartTime, EndTime, "", 0, "", userInfo.Group_Id);
if (stulist.Any())
{
foreach (var item in stulist)
{
decimal UnitPrice = Math.Round(item.UnitPrice, 6, MidpointRounding.AwayFromZero);
StudentHoursList.Add(new RB_Teaching_BonusDetail_ViewModel
{
Date = item.ClassDate,
TimeBucket = item.StartDate + "~" + item.EndDate,
TeacherId = item.OrderGuestId,
TeacherName = item.GuestName,
ClassId = item.ClassId,
ClassName = item.ClassName,
ClassNo = item.ClassNo,
CouseId = item.CouseId,
CourseName = item.CourseName,
KSNum = item.CurrentDeductionHours,
Hours = 0,
HourMoney = 0,
UnitPrice = UnitPrice,
Money = Math.Round(item.UnitPrice * item.CurrentDeductionHours, 6, MidpointRounding.AwayFromZero)
});
}
}
#endregion
List<object> RList = new List<object>();
if (TeacherHoursList.Any())
{
#region 进行月份汇总
string MaxTime = TeacherHoursList.Max(x => x.Date).ToString("yyyy-MM");
//初始化 基础课时
DateTime STime = Convert.ToDateTime(Convert.ToDateTime(StartTime).ToString("yyyy-MM") + "-01");
while (true)
{
DateTime ETime = STime.AddMonths(1).AddDays(-1);
var teacherlist = TeacherHoursList.Where(x => x.Date >= STime && x.Date <= ETime).ToList();
var teacherIdList = teacherlist.Select(x => x.TeacherId).Distinct();
List<object> tRList = new List<object>();
foreach (var qitem in teacherIdList)
{
var tlist = teacherlist.Where(x => x.TeacherId == qitem).ToList();
tRList.Add(new
{
TeacherId = qitem,
TeacherName = tlist.FirstOrDefault()?.TeacherName ?? "",
HoursNum = tlist.Sum(x => x.KSNum),
BaseHoursNum = tlist.Sum(x => x.DKNum),
HoursMoney = tlist.Sum(x => x.Money)
});
}
var studlist = StudentHoursList.Where(x => x.Date >= STime && x.Date <= ETime).ToList();
var studIdList = studlist.Select(x => x.TeacherId).Distinct();
int StuNum = studIdList.Count();
decimal StuSumHours = studlist.Sum(x => x.KSNum);
decimal StuSumMoney = studlist.Sum(x => x.Money);
List<object> sRList = new List<object>();
foreach (var qitem in studIdList)
{
var slist = studlist.Where(x => x.TeacherId == qitem).ToList();
sRList.Add(new
{
StudentId = qitem,
StudentName = slist.FirstOrDefault()?.TeacherName ?? "",
HoursNum = slist.Sum(x => x.KSNum),
HoursMoney = slist.Sum(x => x.Money)
});
}
RList.Add(new
{
Month = STime.ToString("yyyy-MM"),
TeacherList = tRList,
StuNum,
StuSumHours,
StuSumMoney,
StudentList = sRList
});
if (STime.ToString("yyyy-MM") == MaxTime)
{
break;
}
STime = STime.AddMonths(1);
}
#endregion
}
return RList;
}
/// <summary>
/// 获取试听班级月度老师课时费
/// </summary>
/// <param name="reserveClassId"></param>
/// <returns></returns>
public RB_Teaching_BonusDetail_ViewModel GetReserveClassHours(int reserveClassId,int groupId)
{
return teaching_BonusDetailRepository.GetReserveClassHours(reserveClassId, groupId);
}
#endregion
#region 收据管理
/// <summary>
/// 获取收据配置
/// </summary>
/// <param name="userInfo"></param>
/// <returns></returns>
public RB_Receipt_Config_ViewModel GetReceiptConfigInfo(UserInfo userInfo)
{
var model = receipt_ConfigRepository.GetList(new RB_Receipt_Config_ViewModel() { Group_Id = userInfo.Group_Id }).FirstOrDefault();
if (model != null) {
//查询流程
var tempList = Finance_TemplateRepository.GetList(new Rb_Workflow_Template_Finance_Extend() { RB_GroupId = userInfo.Group_Id, TemplateIds = model.TempleteIds });
model.TempleteList = tempList.Select(x => x.Name).ToList();
}
return model;
}
/// <summary>
/// 保存收据配置
/// </summary>
/// <param name="demodel"></param>
/// <returns></returns>
public string SetReceiptConfingInfo(RB_Receipt_Config_ViewModel demodel) {
if (demodel.Id > 0)
{
Dictionary<string, object> keyValues = new Dictionary<string, object>() {
{ nameof(RB_Receipt_Config_ViewModel.Enable), demodel.Enable},
{ nameof(RB_Receipt_Config_ViewModel.TempleteIds), demodel.TempleteIds},
{ nameof(RB_Receipt_Config_ViewModel.Images), demodel.Images},
{ nameof(RB_Receipt_Config_ViewModel.UpdateBy), demodel.UpdateBy},
{ nameof(RB_Receipt_Config_ViewModel.UpdateTime), demodel.UpdateTime},
};
List<WhereHelper> wheres = new List<WhereHelper>() {
new WhereHelper(){
FiledName = nameof(RB_Receipt_Config_ViewModel.Id),
FiledValue = demodel.Id,
OperatorEnum =OperatorEnum.Equal
}
};
bool flag = receipt_ConfigRepository.Update(keyValues, wheres);
return flag ? "" : "出错了，请联系管理员";
}
else {
bool flag = receipt_ConfigRepository.Insert(demodel) > 0;
return flag ? "" : "出错了，请联系管理员";
}
}
/// <summary>
/// 获取收据分页列表
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <param name="demodel"></param>
/// <returns></returns>
public List<RB_Receipt_Info_ViewModel> GetReceiptInfoPageList(int pageIndex, int pageSize, out long count, RB_Receipt_Info_ViewModel demodel)
{
return receipt_InfoRepository.GetPageList(pageIndex, pageSize, out count, demodel);
}
/// <summary>
/// 根据财务单据ID获取收据信息
/// </summary>
/// <param name="financeId"></param>
/// <param name="userInfo"></param>
/// <returns></returns>
public RB_Receipt_Info_ViewModel GetReceiptInfoForFinanceId(int financeId, UserInfo userInfo)
{
var model = receipt_InfoRepository.GetList(new RB_Receipt_Info_ViewModel() { Group_Id = userInfo.Group_Id, FinanceId = financeId }).FirstOrDefault();
if (model != null)
{
var cmodel = receipt_ConfigRepository.GetList(new RB_Receipt_Config_ViewModel() { Group_Id = userInfo.Group_Id }).FirstOrDefault();
if (cmodel != null)
{
model.SealImages = cmodel.Images;
}
}
return model;
}
/// <summary>
/// 新增修改收据信息
/// </summary>
/// <param name="demodel"></param>
/// <returns></returns>
public string SetReceiptInfo(RB_Receipt_Info_ViewModel demodel)
{
if (demodel.Id > 0)
{
var model = receipt_InfoRepository.GetEntity(demodel.Id);
if (model == null) { return "收据不存在"; }
if (model.State == 1) { return "收据已被认领，无法修改"; }
if (model.CreateBy != demodel.CreateBy) { return "无法修改"; }
Dictionary<string, object> keyValues = new Dictionary<string, object>()
{
{ nameof(RB_Receipt_Info_ViewModel.Remitter), demodel.Remitter},
{ nameof(RB_Receipt_Info_ViewModel.Remark), demodel.Remark},
{ nameof(RB_Receipt_Info_ViewModel.UpdateBy), demodel.UpdateBy},
{ nameof(RB_Receipt_Info_ViewModel.UpdateTime), demodel.UpdateTime},
};
List<WhereHelper> wheres = new List<WhereHelper>() {
new WhereHelper(){
FiledName = nameof(RB_Receipt_Info_ViewModel.Id),
FiledValue= demodel.Id,
OperatorEnum=OperatorEnum.Equal
}
};
bool flag = receipt_InfoRepository.Update(keyValues, wheres);
return flag ? "" : "出错了，请联系管理员";
}
else {
var list = receipt_InfoRepository.GetList(new RB_Receipt_Info_ViewModel() { Group_Id = demodel.Group_Id, FinanceId = demodel.FinanceId });
if (list.Any()) { return "该单据已生成收据，无法重复生成"; }
var fmodel = RB_FinanceRepository.GetEntity(demodel.FinanceId);
if (fmodel == null) { return "单据不存在"; }
if (fmodel.CreateBy != demodel.CreateBy) { return "不是制单人无法创建"; }
#region 验证流程
var configModel = receipt_ConfigRepository.GetList(new RB_Receipt_Config_ViewModel() { Group_Id = demodel.Group_Id }).FirstOrDefault();
if (configModel == null || configModel.Enable != 1) { return "收据未启用，无法新增收据"; }
if (!("," + configModel.TempleteIds + ",").Contains("," + (fmodel.TemplateId ?? 0) + ",")) {
return "该单据流程未启用收据功能，无法新增收据";
}
#endregion
//获取数据库最大的序列号
string MaxReceiptNo = receipt_InfoRepository.GetMaxReceiptNo(demodel.Group_Id);
if (MaxReceiptNo == "") { MaxReceiptNo = "80000001"; }
else
{
MaxReceiptNo = (Convert.ToInt32(MaxReceiptNo) + 1).ToString();
}
demodel.ReceiptNo = MaxReceiptNo;
demodel.ClaimCode = new Random().Next(1000, 9999);
bool flag = receipt_InfoRepository.Insert(demodel) > 0;
return flag ? "" : "出错了，请联系管理员";
}
}
/// <summary>
/// 删除收据信息
/// </summary>
/// <param name="receiptId"></param>
/// <param name="userInfo"></param>
/// <returns></returns>
public string DelReceiptInfo(int receiptId, UserInfo userInfo)
{
var model = receipt_InfoRepository.GetEntity(receiptId);
if (model == null || model.Status == 1) { return "收据不存在"; }
if (model.State == 1) { return "收据已认领，无法删除"; }
if (model.CreateBy != userInfo.Id) { return "无法删除"; }
Dictionary<string, object> keyValues = new Dictionary<string, object>()
{
{ nameof(RB_Receipt_Info_ViewModel.Status), 1},
{ nameof(RB_Receipt_Info_ViewModel.UpdateBy), userInfo.Id},
{ nameof(RB_Receipt_Info_ViewModel.UpdateTime), DateTime.Now},
};
List<WhereHelper> wheres = new List<WhereHelper>() {
new WhereHelper(){
FiledName = nameof(RB_Receipt_Info_ViewModel.Id),
FiledValue = receiptId,
OperatorEnum = OperatorEnum.Equal
}
};
bool flag = receipt_InfoRepository.Update(keyValues, wheres);
return flag ? "" : "出问题了，请联系管理员";
}
#endregion
}
}