/// <summary>
/// 查询平台费用
/// </summary>
/// <param name="PlatformTypeId">平台Id(项目表ParentId)</param>
/// <param name="YearStr">年份</param>
/// <param name="MonthStr">月份</param>
/// <returns></returns>
public List <CWPlatformSpendingMapping> GetCWPlatformSpendingList(List <int> ProjectId, string YearStr, string MonthStr)
{
//左链接
var query = from a in this._context.CWProjects
join b in this._context.CWPlatformSpendings on a.Id equals b.ProfitProjectId into JoinedAB
from b in JoinedAB.DefaultIfEmpty()
where (a.IsEnable.Value == false || a.IsEnable == null) &&
(b.IsEnable.Value == false || b.IsEnable == null) &&
ProjectId.Contains(a.ParentID.Value) &&
(b.YearStr.Equals(YearStr) || b.YearStr == null) &&
(b.MonthStr.Equals(MonthStr) || b.MonthStr == null)
select new CWPlatformSpendingMapping
{
Id = a.Id,
PSId = b.Id,
ParentID = a.ParentID,
ProjectExplanation = a.ProjectExplanation,
PlatformTypeId = b.PlatformTypeId,
ProfitProjectId = b.ProfitProjectId,
YearStr = b.YearStr,
MonthStr = b.MonthStr,
CountMoney = b.CountMoney == null ? 0 : b.CountMoney.Value,
Remark = b.Remark
};
return(new List <CWPlatformSpendingMapping>(query.Distinct()).ToList());
}
/// <summary>
/// 刷单记录明细：根据订单付款时间 、返现状态：已返现
/// </summary>
/// <param name="PlatformTypeId">平台类型</param>
/// <param name="NickIdList">店铺集合</param>
/// <param name="OrderInfoModifiedStart">订单付款开始时间</param>
/// <param name="OrderInfoModifiedEnd">订单付款结束时间</param>
/// <param name="CashBackStatus">返现状态：已返现</param>
/// <param name="OrderStatusId">时间类型</param>
/// <returns></returns>
public List <OrderInfoSalesDetails> GetXMScalpingDetailsList(int PlatformTypeId, List <int> NickIdList, DateTime?OrderInfoModifiedStart, DateTime?OrderInfoModifiedEnd, int OrderStatusId)
{
IQueryable <OrderInfoSalesDetails> query = null;
#region 创单时间
//左链接
query = from a in this._context.XMScalpings
join b in this._context.XMOrderInfoes
on a.OrderCode equals b.OrderCode into JoinedAB
from b in JoinedAB.DefaultIfEmpty()
join e in this._context.XMNicks on b.NickID equals e.nick_id
into eJoin
from eInfo in eJoin.DefaultIfEmpty()
join f in this._context.XMProjects on eInfo.ProjectId equals f.Id
into fJoin
from fInfo in fJoin.DefaultIfEmpty()
where a.IsAbnormal.Value == false &&
eInfo.isEnable == true &&
fInfo.IsEnable == true &&
a.IsEnable.Value == false &&
b.FinancialAudit == true &&
b.IsEnable.Value == false &&
NickIdList.Contains(b.NickID.Value) &&
(PlatformTypeId == -1 || b.PlatformTypeId.Value == PlatformTypeId) &&
((OrderInfoModifiedStart == null && OrderInfoModifiedEnd == null) ||
(b.OrderInfoCreateDate >= OrderInfoModifiedStart && b.OrderInfoCreateDate < OrderInfoModifiedEnd))
orderby b.OrderInfoCreateDate descending
select new OrderInfoSalesDetails
{
ID = b.ID,
PlatformTypeId = b.PlatformTypeId,
NickID = b.NickID,
OrderCode = b.OrderCode,
OrderInfoCreateDate = b.OrderInfoCreateDate,
PayDate = b.PayDate,
DeliveryTime = b.DeliveryTime,
CompletionTime = b.CompletionTime,
WantID = a.WantID,
ProductName = a.ProductName,
SalesPrice = a.SalePrice,
Fee = a.Fee,
OrderPrice = a.Fee + a.Deduction,
Deduction = a.Deduction,
ProjectName = fInfo.ProjectName,
NickName = eInfo.nick,
MarkDate = b.OrderInfoCreateDate
};
#endregion
return(query.ToList());
}
public decimal getScalpingCost(int PlatformTypeId, List <int> NickIdList, DateTime?OrderInfoModifiedStart, DateTime?OrderInfoModifiedEnd)
{
var query = from a in this._context.XMScalpings
join b in this._context.XMOrderInfoes
on a.OrderCode equals b.OrderCode into JoinedAB
from b in JoinedAB.DefaultIfEmpty()
join e in this._context.XMNicks on b.NickID equals e.nick_id
into eJoin
from eInfo in eJoin.DefaultIfEmpty()
join f in this._context.XMProjects on eInfo.ProjectId equals f.Id
into fJoin
from fInfo in fJoin.DefaultIfEmpty()
where a.IsAbnormal.Value == false &&
eInfo.isEnable == true &&
fInfo.IsEnable == true &&
a.IsEnable.Value == false &&
b.FinancialAudit == true &&
b.IsEnable.Value == false &&
NickIdList.Contains(b.NickID.Value) &&
(PlatformTypeId == -1 || b.PlatformTypeId.Value == PlatformTypeId) &&
((OrderInfoModifiedStart == null && OrderInfoModifiedEnd == null) ||
(b.OrderInfoCreateDate >= OrderInfoModifiedStart && b.OrderInfoCreateDate < OrderInfoModifiedEnd))
orderby b.OrderInfoCreateDate descending
select new OrderInfoSalesDetails
{
ID = b.ID,
PlatformTypeId = b.PlatformTypeId,
NickID = b.NickID,
OrderCode = b.OrderCode,
OrderInfoCreateDate = b.OrderInfoCreateDate,
PayDate = b.PayDate,
DeliveryTime = b.DeliveryTime,
CompletionTime = b.CompletionTime,
WantID = a.WantID,
ProductName = a.ProductName,
SalesPrice = a.SalePrice,
Fee = a.Fee,
OrderPrice = a.Fee + a.Deduction,
Deduction = a.Deduction,
ProjectName = fInfo.ProjectName,
NickName = eInfo.nick,
MarkDate = b.OrderInfoCreateDate
};
decimal?cost = query.Select(a => a.OrderPrice).Sum();
return(Math.Round((cost == null ? 0 : (decimal)cost), 2));
}
/// <summary>
/// 返现明细：根据订单付款时间 、返现状态：已返现
/// </summary>
/// <param name="PlatformTypeId">平台类型</param>
/// <param name="NickIdList">店铺集合</param>
/// <param name="OrderInfoModifiedStart">订单付款开始时间</param>
/// <param name="OrderInfoModifiedEnd">订单付款结束时间</param>
/// <param name="CashBackStatus">返现状态：已返现</param>
/// <param name="OrderStatusId">时间类型</param>
/// <returns></returns>
public List <OrderInfoSalesDetails> GetXMCashBackApplicationDetailsList(int PlatformTypeId, List <int> NickIdList, DateTime?OrderInfoModifiedStart, DateTime?OrderInfoModifiedEnd, int CashBackStatus, int OrderStatusId)
{
IQueryable <OrderInfoSalesDetails> query = null;
//左链接
query = from a in this._context.XMCashBackApplications
join b in this._context.XMOrderInfoes
on a.OrderCode equals b.OrderCode into JoinedAB
from b in JoinedAB.DefaultIfEmpty()
join e in this._context.XMNicks on b.NickID equals e.nick_id
into eJoin
from eInfo in eJoin.DefaultIfEmpty()
join f in this._context.XMProjects on eInfo.ProjectId equals f.Id
into fJoin
from fInfo in fJoin.DefaultIfEmpty()
where ((OrderInfoModifiedStart == null && OrderInfoModifiedEnd == null) || (a.CreateTime >= OrderInfoModifiedStart && a.CreateTime < OrderInfoModifiedEnd)) &&
a.CashBackStatus.Value == CashBackStatus &&
(PlatformTypeId == -1 || b.PlatformTypeId.Value == PlatformTypeId) &&
NickIdList.Contains(b.NickID.Value) &&
a.IsEnable.Value == false &&
b.IsEnable.Value == false &&
b.FinancialAudit == true &&
eInfo.isEnable == true &&
fInfo.IsEnable == true
orderby a.CreateTime descending
select new OrderInfoSalesDetails
{
ID = b.ID,
PlatformTypeId = b.PlatformTypeId,
NickID = b.NickID,
OrderCode = b.OrderCode,
OrderInfoCreateDate = b.OrderInfoCreateDate,
PayDate = b.PayDate,
DeliveryTime = b.DeliveryTime,
CompletionTime = b.CompletionTime,
XMCashBackApplicationWantNo = a.WantNo,
BuyerName = a.BuyerName,
CashBackMoney = a.CashBackMoney,
BuyerAlipayNo = a.BuyerAlipayNo,
ProjectName = fInfo.ProjectName,
NickName = eInfo.nick,
MarkDate = a.CreateTime
};
return(query.ToList());
}
/// <summary>
/// 统计安装费用
/// </summary>
/// <param name="PlatformTypeId">平台类型</param>
/// <param name="NickIdList">店铺集合</param>
/// <param name="OrderInfoModifiedStart">订单付款开始时间</param>
/// <param name="OrderInfoModifiedEnd">订单付款结束时间</param>
/// <returns></returns>
public decimal InstallationCost(int PlatformTypeId, List <int> NickIdList, DateTime?OrderInfoModifiedStart, DateTime?OrderInfoModifiedEnd)
{
var query = from a in _context.XMInstallationLists
join b in this._context.XMOrderInfoes
on a.OrderCode equals b.OrderCode into JoinedAB
from b in JoinedAB.DefaultIfEmpty()
join e in this._context.XMNicks on b.NickID equals e.nick_id
into eJoin
from eInfo in eJoin.DefaultIfEmpty()
join f in this._context.XMProjects on eInfo.ProjectId equals f.Id
into fJoin
from fInfo in fJoin.DefaultIfEmpty()
where ((OrderInfoModifiedStart == null && OrderInfoModifiedEnd == null) || (a.CreateDate >= OrderInfoModifiedStart && a.CreateDate < OrderInfoModifiedEnd)) &&
a.IsInstall == true &&
(PlatformTypeId == -1 || b.PlatformTypeId.Value == PlatformTypeId) &&
NickIdList.Contains(b.NickID.Value) &&
b.IsEnable.Value == false &&
b.FinancialAudit == true &&
eInfo.isEnable == true &&
fInfo.IsEnable == true
select new XMInstallationDetails
{
InstallationFee = a.InstallationFee,
};
if (query.Count() > 0)
{
return((decimal)query.Sum(a => a.InstallationFee == null ? 0 : a.InstallationFee));
}
else
{
return(0);
}
}
/// <summary>
/// 获取返现成本
/// </summary>
/// <param name="PlatformTypeId"></param>
/// <param name="NickIdList"></param>
/// <param name="OrderInfoModifiedStart"></param>
/// <param name="OrderInfoModifiedEnd"></param>
/// <param name="CashBackStatus"></param>
/// <returns></returns>
public decimal getCashBackCost(int PlatformTypeId, List <int> NickIdList, DateTime?OrderInfoModifiedStart, DateTime?OrderInfoModifiedEnd, int CashBackStatus)
{
var query = from a in this._context.XMCashBackApplications
join b in this._context.XMOrderInfoes
on a.OrderCode equals b.OrderCode into JoinedAB
from b in JoinedAB.DefaultIfEmpty()
join e in this._context.XMNicks on b.NickID equals e.nick_id
into eJoin
from eInfo in eJoin.DefaultIfEmpty()
join f in this._context.XMProjects on eInfo.ProjectId equals f.Id
into fJoin
from fInfo in fJoin.DefaultIfEmpty()
where ((OrderInfoModifiedStart == null && OrderInfoModifiedEnd == null) || (a.CreateTime >= OrderInfoModifiedStart && a.CreateTime < OrderInfoModifiedEnd)) &&
a.CashBackStatus.Value == CashBackStatus &&
(PlatformTypeId == -1 || b.PlatformTypeId.Value == PlatformTypeId) &&
NickIdList.Contains(b.NickID.Value) &&
a.IsEnable.Value == false &&
b.IsEnable.Value == false &&
b.FinancialAudit == true &&
eInfo.isEnable == true &&
fInfo.IsEnable == true
orderby a.CreateTime descending
select new OrderInfoSalesDetails
{
ID = b.ID,
PlatformTypeId = b.PlatformTypeId,
NickID = b.NickID,
OrderCode = b.OrderCode,
OrderInfoCreateDate = b.OrderInfoCreateDate,
PayDate = b.PayDate,
DeliveryTime = b.DeliveryTime,
CompletionTime = b.CompletionTime,
XMCashBackApplicationWantNo = a.WantNo,
BuyerName = a.BuyerName,
CashBackMoney = a.CashBackMoney,
BuyerAlipayNo = a.BuyerAlipayNo,
ProjectName = fInfo.ProjectName,
NickName = eInfo.nick,
MarkDate = a.CreateTime
};
decimal?cost = query.Select(a => a.CashBackMoney).Sum();
#region 无订单
var query1 = from p in this._context.XMCashBackApplications
join m in _context.XMNicks on p.NickID equals m.nick_id into temp
from pm in temp.DefaultIfEmpty()
where p.OrderCode.StartsWith("NoOrder") && pm.isEnable && (PlatformTypeId == -1 || pm.PlatformTypeId.Value == PlatformTypeId) &&
NickIdList.Contains((int)p.NickID)
select p;
decimal?cost1 = query1.Select(a => a.CashBackMoney).Sum();
#endregion
decimal total = (cost == null ? 0 : (decimal)cost) + (cost1 == null ? 0 : (decimal)cost1);
return(total);
}