public IHttpActionResult GetList4AssociateCommissionReport([FromUri] AssociateCommissionStatisticsReportRequest request, [UserProfile] UserProfile userProfile)
{
IHttpActionResult httpActionResult;
var result = CheckDataRoleAndArrangeParams(request, userProfile, out httpActionResult);
if (!result)
{
return(httpActionResult);
}
var pagerRequest = request.PagerRequest;
var storeFilter = StoreFilter(request.StoreId, request.DataRoleStores);
var sectionFilter = SectionFilter(request.SectionCode);
var userFilter = UserFilter(request.MiniSilverNo, request.Contact);
List <AssociateCommissionStatisticsReportDto> datas;
int totalCount;
using (var db = new YintaiHZhouContext())
{
var associateIncomes = db.IMS_AssociateIncome;
var associates = db.IMS_Associate;
var stores = db.Stores.AsExpandable().Where(storeFilter);
var sections = db.Sections.AsExpandable().Where(sectionFilter);
var users = db.Users.AsExpandable().Where(userFilter);
var associateIncomeHistorys = db.IMS_AssociateIncomeHistory;
//var sql = from ai in associateIncomes
// join a in associates on ai.UserId equals a.UserId
// join store in stores on a.StoreId equals store.Id
// join section in sections on a.SectionId equals section.Id
// join user in users on a.UserId equals user.Id
// let historys = (
// from aih in associateIncomeHistorys
// where aih.SourceType == 1 && aih.Status == 1 && a.UserId == aih.AssociateUserId
// select aih
// )
// select new AssociateCommissionStatisticsReportDto
// {
// Id = ai.Id,
// ApplicationPickUpAmount = ai.RequestAmount,
// Contact = user.Mobile,
// HavePickUpAmount = ai.ReceivedAmount,
// LockedPickUpAmount = historys == null ? 0m : historys.Sum(v => v.AssociateIncome),
// MiniSilverNo = user.Nickname,
// NoPickUpAmount = ai.AvailableAmount,
// SectionCode = section.StoreCode,
// SectionId = section.Id,
// SectionName = section.Name,
// StoreId = store.Id,
// StoreName = store.Name
// };
var sql = from ai in associateIncomes
join a in associates on ai.UserId equals a.UserId
join store in stores on a.StoreId equals store.Id
join section in sections on a.SectionId equals section.Id
join user in users on a.UserId equals user.Id
join aihs in
(
from aih in associateIncomeHistorys
where aih.Status == 1 //《-----冻结
group aih by aih.AssociateUserId into g
select new
{
g.Key,
income = g.Sum(v => v.AssociateIncome)
}
) on ai.UserId equals aihs.Key into temp
from aihs in temp.DefaultIfEmpty()
where a.Status == 1
select new AssociateCommissionStatisticsReportDto
{
Id = ai.Id,
ApplicationPickUpAmount = ai.RequestAmount,
Contact = user.Mobile,
HavePickUpAmount = ai.ReceivedAmount,
LockedPickUpAmount = aihs == null ? 0m : aihs.income,
MiniSilverNo = user.Nickname,
NoPickUpAmount = ai.AvailableAmount,
SectionCode = section.SectionCode,
SectionId = section.Id,
SectionName = section.Name,
StoreId = store.Id,
StoreName = store.Name
};
totalCount = sql.Count();
datas = sql.OrderByDescending(v => v.NoPickUpAmount).Skip(pagerRequest.SkipCount).Take(pagerRequest.PageSize).ToList();
}
var pager = new PagerInfo <AssociateCommissionStatisticsReportDto>(pagerRequest, totalCount, datas);
var execresult = new OkExectueResult <PagerInfo <AssociateCommissionStatisticsReportDto> >(pager);
return(RetrunHttpActionResult4ExectueResult(execresult));
}
public IHttpActionResult GetList4CashAssociateCommissionReport([FromUri] CashAssociateCommissionStatisticsReportRequest request, [UserProfile] UserProfile userProfile)
{
IHttpActionResult httpActionResult;
var result = CheckDataRoleAndArrangeParams(request, userProfile, out httpActionResult);
if (!result)
{
return(httpActionResult);
}
var pagerRequest = request.PagerRequest;
var associateIncomeRequestFilter = CashAssociateCommissionStatisticsReportFilter(request);
var storeFilter = StoreFilter(request.StoreId, request.DataRoleStores);
var sectionFilter = SectionFilter(request.SectionCode);
var userFilter = UserFilter(request.MiniSilverNo, request.Contact);
List <CashAssociateCommissionStatisticsReportDto> datas;
int totalCount;
using (var db = new YintaiHZhouContext())
{
var associateIncomeRequest = db.IMS_AssociateIncomeRequest.AsExpandable().Where(associateIncomeRequestFilter);
var associates = db.IMS_Associate;
var stores = db.Stores.AsExpandable().Where(storeFilter);
var sections = db.Sections.AsExpandable().Where(sectionFilter);
var users = db.Users.AsExpandable().Where(userFilter);
var sql = from air in associateIncomeRequest
join a in associates on air.UserId equals a.UserId
join store in stores on a.StoreId equals store.Id
join section in sections on a.SectionId equals section.Id
join user in users on a.UserId equals user.Id
where air.Status == CashStatus && a.Status == 1
select new CashAssociateCommissionStatisticsReportDto
{
Id = air.Id,
PickUpDate = air.CreateDate,
BankCardNo = air.BankNo,
BankName = air.BankName,
//Fee = 1,
MiniSilverNo = user.Nickname,
PickUpAmount = air.Amount,
PickUpPerson = air.BankAccountName,
SectionCode = section.SectionCode,
SectionId = section.Id,
SectionName = section.Name,
StoreId = store.Id,
StoreName = store.Name,
Contact = user.Mobile
//Taxes = null
};
totalCount = sql.Count();
datas = sql.OrderByDescending(v => v.PickUpDate).Skip(pagerRequest.SkipCount).Take(pagerRequest.PageSize).ToList();
}
var pager = new PagerInfo <CashAssociateCommissionStatisticsReportDto>(pagerRequest, totalCount, datas);
var execresult = new OkExectueResult <PagerInfo <CashAssociateCommissionStatisticsReportDto> >(pager);
return(RetrunHttpActionResult4ExectueResult(execresult));
}
public IHttpActionResult GetList4GiftCardSalesReport([FromUri] GiftCardSalesStatisticsReportRequest request, [UserProfile] UserProfile userProfile)
{
IHttpActionResult httpActionResult;
var result = CheckDataRoleAndArrangeParams(request, userProfile, out httpActionResult);
if (!result)
{
return(httpActionResult);
}
var pagerRequest = request.PagerRequest;
List <GiftCardSalesStatisticsReportDto> datas;
int totalCount;
var giftcardorderFilter = GiftCardOrderFilter(request);
var transFilter = OrderTransactionFilter(request.TransNo);
var paymentFilter = PaymentFilter(request.PaymentMethodCode);
var storeFilter = StoreFilter(request.StoreId, request.DataRoleStores);
using (var db = new YintaiHZhouContext())
{
var giftcardorders = db.IMS_GiftCardOrder.AsExpandable().Where(giftcardorderFilter);
var ordertransactions = db.OrderTransactions.AsExpandable().Where(transFilter);
var payments = db.PaymentMethods.AsExpandable().Where(paymentFilter);
var associateIncomeHistory = db.IMS_AssociateIncomeHistory;
var associates = db.IMS_Associate;
var stores = db.Stores.AsExpandable().Where(storeFilter);
var sql = from gco in giftcardorders
join ot in ordertransactions on gco.No equals ot.OrderNo
join pm in payments on ot.PaymentCode equals pm.Code
join aih in associateIncomeHistory on gco.No equals aih.SourceNo
join a in associates on aih.AssociateUserId equals a.UserId
join store in stores on a.StoreId equals store.Id
where aih.SourceType == 1 && a.Status == 1//<---------------礼品卡
select new GiftCardSalesStatisticsReportDto
{
Amount = gco.Amount,
SalesAmount = gco.Price,
Id = gco.Id,
BuyDate = gco.CreateDate,
OrderNo = ot.OrderNo,
PaymentMethodCode = pm.Code,
PaymentMethodName = pm.Name,
Status = gco.Status,
StoreId = store.Id,
StoreName = store.Name,
TransNo = ot.TransNo
};
totalCount = sql.Count();
datas = sql.OrderByDescending(v => v.BuyDate).Skip(pagerRequest.SkipCount).Take(pagerRequest.PageSize).ToList();
}
var pager = new PagerInfo <GiftCardSalesStatisticsReportDto>(pagerRequest, totalCount, datas);
var execresult = new OkExectueResult <PagerInfo <GiftCardSalesStatisticsReportDto> >(pager);
return(RetrunHttpActionResult4ExectueResult(execresult));
}