public override IPageOfList <MonthPayOff> GetByFilter(ParameterFilter filter)
{
string column = @"a.ID,
a.PayOffMonth,
cast(COUNT(b.ID)as signed) as LoadBillCounts,
cast(SUM(c.OrderCounts)as signed) AS OrderCounts,
SUM(b.PreTotalCostFee) AS PreTotalCostFee,
SUM(b.TotalCostFee) AS TotalCostFee,
SUM(b.PreInComeFee) AS PreInComeFee,
SUM(b.InComeFee) AS InComeFee,
SUM(b.TotalMargin) AS TotalMargin,
SUM(b.TotalMargin)/SUM(b.PreInComeFee) AS MarginRate,
a.Remark,
a.`Status`";
string sql = @" FROM MonthPayOff a LEFT JOIN MonthPayOffDetail b ON a.ID=b.MonthPayOffID LEFT JOIN LoadBillInCome c ON b.LoadBillID=c.ID where 1=1";
if (filter.HasQueryString)
{
sql = filter.ToHql();
}
else
{
sql += filter.ToHql();
}
var paras = filter.GetParameters();
var countQuery = NHibernateSession.CreateSQLQuery(string.Format("select COUNT(DISTINCT a.ID) as Count {0}", sql));
var query = NHibernateSession.CreateSQLQuery(string.Format("select {0} {1} group by a.ID {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];
var Count = countQuery.UniqueResult <long>();
if (Count == 0)
{
return(new PageOfList <MonthPayOff>(new List <MonthPayOff>(), pageIndex, pageSize, Count));
}
else
{
var list = query.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(MonthPayOff))).SetFirstResult(pageIndex * pageSize).SetMaxResults(pageSize).List <MonthPayOff>().ToList();
return(new PageOfList <MonthPayOff>(list, pageIndex, pageSize, Count));
}
}
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 IPageOfList <LoadBillReconciliation> GetByMonthPayOffFilter(ParameterFilter filter)
{
string column = @"a.ID,
b.ReconcileDate AS ReconcileDate,
c.Cus_Name AS CusName,
a.LoadBillNum AS LoadBillNum,
a.BillWeight AS FeeWeight,
a.OrderCounts AS ExpressCount,
a.CompletionTime AS CompletionTime,
IFNULL(b.GroundHandlingFee,0) AS GroundHandlingFee,
IFNULL(b.StoreFee,0) AS CostStoreFee,
CASE WHEN b.ID IS NULL THEN a.PreTotalCollectFees ELSE 0 END AS CostExpressFee,
CASE WHEN b.ID IS NULL THEN a.PreTotalOperateFee ELSE 0 END AS CostOperateFee,
null AS CostOtherFee,
null AS CostTotalFee,
b.PayStatus AS CostStatus,
a.LoadFee AS InComeLoadFee,
a.StoreFee AS InComeStoreFee,
a.TotalCollectFees AS InComeExpressFee,
a.TotalOperateFee AS InComeOperateFee,
a.OtherFee AS InComeOtherFee,
null AS InComeTotalFee,
a.PayStatus AS InComeStatus,
null AS TotalGrossProfit,
null AS GrossProfitRate,
CASE IFNULL(a.IsAddMonthPayOff,0) WHEN 0 THEN '待添加到月结表' ELSE '已添加月结表' END AS Status,
cast(a.IsAddMonthPayOff as signed) as IsAddMonthPayOff,
b.ID AS IsReal,
a.OrderWeight as ExpressWeight";
string sql = @" FROM LoadBillInCome a INNER JOIN MonthPayOffDetail d ON a.ID=d.LoadBillID LEFT JOIN LoadBillCost b ON a.LoadBillNum=b.LoadBillNum LEFT JOIN CustomerInfo c ON a.CustomerID=c.ID WHERE 1=1";
if (filter.HasQueryString)
{
sql = filter.ToHql();
}
else
{
sql += filter.ToHql();
}
var paras = filter.GetParameters();
var countQuery = NHibernateSession.CreateSQLQuery(string.Format("select COUNT(a.ID) as Count {0}", sql));
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];
var Count = countQuery.UniqueResult <long>();
var list = query.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(LoadBillReconciliation))).SetFirstResult(pageIndex * pageSize).SetMaxResults(pageSize).List <LoadBillReconciliation>().ToList();
return(new LRPageOfList <LoadBillReconciliation>(list, pageIndex, pageSize, Count));
}
public override IPageOfList <WayBillException> GetByFilter(ParameterFilter filter)
{
string column = @"e.ID AS ID,
w.ID AS IsInputCost,
w.PostingTime AS PostingTime,
e.ExpressNo AS ExpressNo,
e.LoadBillNO AS LoadBillNum,
w.Weight AS Weight,
c.Cus_Name AS CusName,
(CASE WHEN w.ExpressNo IS NULL THEN a.PreExpressFee ELSE w.WayBillFee END) AS WayBillFee,
(CASE WHEN w.ExpressNo IS NULL THEN a.PreOperateFee ELSE w.ProcessingFee END) AS ProcessingFee,
a.ExpressFee AS ExpressFee,
a.OperateFee AS OperateFee,
w.PayStatus AS CostStatus,
a.PayStatus AS InComeStatus,
a.ExpressFee - w.WayBillFee AS WayBillProfit,
e.ExceptionMsg,e.WayBillCostID ";
string sql = @" FROM ExpressNoExceptionDetail e
INNER JOIN WayBillCost w ON e.WayBillCostID = w.ID AND e.Status=0
LEFT JOIN WayBillInCome a ON e.ExpressNo = a.ExpressNo
LEFT JOIN CustomerInfo c ON a.CustomerID = c.ID
where 1=1";
if (filter.HasQueryString)
{
sql = filter.ToHql();
}
else
{
sql += filter.ToHql();
}
var paras = filter.GetParameters();
string strSQL = "SELECT COUNT(e.ID) as Count " + 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 <long>()[0];
long totalCounts = Count;
var list = query.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(WayBillException))).SetFirstResult(pageIndex * pageSize).SetMaxResults(pageSize).List <WayBillException>().ToList();
return(new WRPageOfList <WayBillException>(list, pageIndex, pageSize, totalCounts, new StatModel()));
}