一共2中方法解决 ,第一种是 用一个c#方法,第二种是直接LINQ语句
————————————————————————————————————————
BLL层语句
public IQueryable<HotelOrderInfoModel> GetAllOrderInfo(long? companyid, long? CID, DateTime? CheckTime)
{
//List<CompanyHotel> lst = GetHotelID(companyid);
OrderDataClasses od = new OrderDataClasses();
var list = od.CompanyHotel.Where(o => o.CompanyID == companyid);
IQueryable<HotelOrderInfoModel> info = from h in od.Htel_OrderInfo
//from p in od.Htel_OrderDetail
from d in od.Hotel
from l in list
where h.HotelID == l.HotelID
//where h.OrderCode == p.OrderCode
where h.CreateDate >= CheckTime && h.IsCheckOut == 0
where d.ID == l.HotelID
select new HotelOrderInfoModel
{
HotelNameCN = d.NameCN,
CheckOut = h.Checkout,
OrderState = h.OrderState == null ? -1 : h.OrderState,
RoomNameCN = h.RoomNameCN,
SalePrice = (from p in od.Htel_OrderDetail
where p.OrderCode == h.OrderCode
select
p.SalePrice).Sum(),
Commission = (from pp in od.Htel_OrderDetail
where pp.OrderCode == h.OrderCode
select new OrderCommission
{
OCommission = GetCommission(pp.CheckOutType, pp.SalePrice, pp.CommissionValue)
}
),
CompanyIdList = (from hh in od.Htel_OrderInfo
where h.HotelID == l.HotelID
select
h.CompanyID).ToList()
};
private int? GetCommission(int? ctype,int? saleprice ,int? cvalue)
{
int? result = 0;
switch (ctype)
{
case 84:
result = (saleprice == null ? 0 : saleprice) - (cvalue == null ? 0 : cvalue);
break;
case 85:
result = (cvalue == null ? 0 : cvalue);
break;
case 86:
result = (saleprice == null ? 0 : saleprice) * (cvalue == null ? 0 : cvalue) / 100;
break;
}
return result;
}
public string SumOrderCommission(IQueryable<OrderCommission> ordercommission)
{
int? n =0;
foreach (var ob in ordercommission)
{
n += ob.OCommission;
}
return n.ToString();
}
-----------------------------------------------------------------------------------------------------------------------
MODEL 层
------------------------------------------------------------------------------------------------------------------------
前台页面调用
<%foreach (var item in Model)
{ %>
<tr>
<td>
<%=Html.Encode(item.HotelNameCN) %>
</td>
<td>
<%=Html.Encode(item.RoomNameCN) %>
</td>
<td>
<%=Html.Encode(item.SalePrice) %>
</td>
<td>
<%= mb.SumOrderCommission(item.Commission)%>
</td>
<td>5
</td>
<td>
<%=Html.Encode(item.CheckOut) %>
</td>
</tr>
<%} %>
——————————————————————————————
另一种方法:
LINQ 语句是这样的:
var sell = from s in db.Sell
group s by new { s.SellStore, s.GoodID } into sInto
select new
{
StoreID = sInto.Key.SellStore,
GoodID = sInto.Key.GoodID,
SellNum = sInto.Sum(item => NumberConverter(item.SellMode ?? new char(), Convert.ToInt32(item.TotalSellNum ?? 0)))
};
var sell = from s in db.Sell
group s by new { s.SellStore, s.GoodID } into sInto
select new
{
StoreID = sInto.Key.SellStore,
GoodID = sInto.Key.GoodID,
SellNum = sInto.Sum(item => NumberConverter(item.SellMode ?? new char(), Convert.ToInt32(item.TotalSellNum ?? 0)))
};