public override IPageOfList <WayBillReconciliation> GetByFilter(ParameterFilter filter)
{
//--对账日期 --收寄日期 --运单号 --提单号 --重量 --客户名称 --邮政邮资 --邮政邮件处理费 --客户运费 --客户操作费 --成本状态--收入状态--运费毛利 --操作费毛利
string column = @"a.ReconcileDate as ReconcileDate,
b.ID AS ID,
a.ID AS IsInputCost,
a.PostingTime AS PostingTime,
b.ExpressNo AS ExpressNo,
b.LoadBillNO AS LoadBillNum,
a.Weight AS Weight,
c.Cus_Name AS CusName,
(CASE WHEN a.ExpressNo IS NULL THEN b.PreExpressFee ELSE a.WayBillFee END) AS WayBillFee,
(CASE WHEN a.ExpressNo IS NULL THEN b.PreOperateFee ELSE a.ProcessingFee END) AS ProcessingFee,
b.ExpressFee AS ExpressFee,
b.OperateFee AS OperateFee,
a.PayStatus AS CostStatus,
b.PayStatus AS InComeStatus,
b.ExpressFee-a.WayBillFee AS WayBillProfit,
CASE IFNULL(d.IsAddMonthPayOff,0) WHEN 0 THEN '待添加到月结表' ELSE '已添加月结表' END AS Statement ";
string sql = @" FROM WayBillInCome b
INNER JOIN LoadBillInCome d ON b.LoadBillID=d.ID
LEFT JOIN CustomerInfo c ON d.CustomerID=c.ID
LEFT JOIN WayBillCost a ON a.ExpressNo=b.ExpressNo
LEFT JOIN ExpressNoExceptionDetail e ON a.ID=e.WayBillCostID
where 1=1 AND e.ID is NULL";
if (filter.HasQueryString)
{
sql = filter.ToHql();
}
else
{
sql += filter.ToHql();
}
var paras = filter.GetParameters();
//string strSQL = "select COUNT(b.ID) as Count" + sql;
string strSQL = @"select COUNT(a.ID) as Count,SUM(a.Weight) as TotalWeight,SUM(a.WayBillFee) as TotalWayBillFee,SUM(a.ProcessingFee) as TotalProcessingFee,
SUM(b.ExpressFee) as TotalExpressFee,SUM(b.OperateFee) as TotalOperateFee,SUM(b.ExpressFee-a.WayBillFee) AS TotalWayBillProfit" + sql;
var countQuery = NHibernateSession.CreateSQLQuery(strSQL);
var query = NHibernateSession.CreateSQLQuery(string.Format("select {0} {1} {2} ", column, sql, filter.GetOrderString()));
foreach (var key in paras.Keys)
{
countQuery.SetParameter(key, paras[key]);
query.SetParameter(key, paras[key]);
}
int pageIndex = filter.PageIndex;
int pageSize = filter.PageSize;
var Count = countQuery.List <object[]>()[0];
//long totalCounts = Count;
decimal totalWeight = Count[1] == null ? 0m : decimal.Parse(Count[1].ToString()); //总重量
decimal totalWayBillFee = Count[2] == null ? 0m : decimal.Parse(Count[2].ToString()); //总运费
decimal totalProcessingFee = Count[3] == null ? 0m : decimal.Parse(Count[3].ToString());
decimal totalExpressFee = Count[4] == null ? 0m : decimal.Parse(Count[4].ToString());
decimal totalOperateFee = Count[5] == null ? 0m : decimal.Parse(Count[5].ToString());
decimal totalWayBillProfit = Count[6] == null ? 0m : decimal.Parse(Count[6].ToString());
StatModel model = new StatModel
{
TotalWeight = totalWeight,
TotalWayBillFee = totalWayBillFee,
TotalProcessingFee = totalProcessingFee,
TotalExpressFee = totalExpressFee,
TotalOperateFee = totalOperateFee,
TotalWayBillProfit = totalWayBillProfit
};
//StatModel model = new StatModel();
var list = query.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(WayBillReconciliation))).SetFirstResult(pageIndex * pageSize).SetMaxResults(pageSize).List <WayBillReconciliation>().ToList();
return(new WRPageOfList <WayBillReconciliation>(list, pageIndex, pageSize, Convert.ToInt32(Count[0]), model));
}
public WRPageOfList(IEnumerable <T> items, int pageIndex, int pageSize, long recordTotal, StatModel model)
{
if (items != null)
{
AddRange(items);
}
PageIndex = pageIndex;
PageSize = pageSize;
RecordTotal = recordTotal;
StatModelBy = model;
}