public ActionResult SettlementList(SettlementQueryModel qm) { PagedList <SettlementOrderInfo> list = null; try { list = settlement.getOrderList(qm, new Models.PagingModel { IsPaging = true, PageIndex = this.PageNo, PageSize = this.PageSize }); ViewBag.IsFinance = qm.IsFinance; ViewBag.SettlementStatus = qm.SettlementStatus; } catch (Exception ext) { LogHelper.Error(ext); } return(View(list)); }
// GET: Settlement public ActionResult SettlementIndex(int isFinance = 0) { var dtNow = DateTime.Now; var qm = new SettlementQueryModel() { CompanyName = Request["CompanyName"] == null ? "" : Request["CompanyName"].ToString(), SettlementCode = Request["SettlementCode"] == null ? "" : Request["SettlementCode"].ToString(), OrderCode = Request["OrderCode"] == null ? "" : Request["OrderCode"].ToString(), EndTime = Request["EndTime"] == null ? dtNow : Convert.ToDateTime(Request["EndTime"].ToString()), StartTime = Request["StartTime"] == null?dtNow.AddMonths(-6) : Convert.ToDateTime(Request["StartTime"].ToString()), IsFinance = isFinance }; ViewBag.PageNo = Request["PageNo"] == null ? 1 : Convert.ToInt32(Request["PageNo"].ToString()); ViewBag.PageSize = Request["PageSize"] == null ? 50 : Convert.ToInt32(Request["PageSize"].ToString()); return(View("SettlementIndex", qm)); }
public ActionResult SettlementList(string startTime, string endTime, string orderCode, string settlementCode, int settlementStatus = 0) { try { SettlementQueryModel queryInfo = new SettlementQueryModel(); if (string.IsNullOrEmpty(startTime) || string.IsNullOrEmpty(endTime)) { queryInfo.StartTime = DateTime.Now.AddMonths(-3); queryInfo.EndTime = DateTime.Now; } else { queryInfo.StartTime = DateTime.Parse(startTime); queryInfo.EndTime = DateTime.Parse(endTime); } queryInfo.OrderCode = orderCode; queryInfo.SettlementCode = settlementCode; queryInfo.SupplierId = CurrentUser.SupplierID; queryInfo.SettlementStatus = settlementStatus; ViewBag.QueryInfo = queryInfo; PageOf <SettlementOrderInfo> list = settlement.getSettlementList(queryInfo, new PageDTO() { PageIndex = PageNo, PageSize = 50 }); return(View(list)); } catch (Exception ext) { LogHelper.Error(ext); } return(View()); }
/// <summary> /// 拼接查询条件 /// </summary> /// <param name="queryInfo"></param> /// <param name="dbParameters"></param> /// <returns></returns> private string BindQuery(SettlementQueryModel queryInfo, ParameterCollection dbParameters) { var stringBuilder = new StringBuilder(); if (!string.IsNullOrEmpty(queryInfo.SettlementCode)) { stringBuilder.Append(" and soi.SettlementCode=@SettlementCode"); dbParameters.Append("SettlementCode", queryInfo.SettlementCode); } else { if (!string.IsNullOrEmpty(queryInfo.OrderCode)) { stringBuilder.Append(" and (soi.OrderCode=@OrderCode or soi.RefundCode=@OrderCode)"); dbParameters.Append("OrderCode", queryInfo.OrderCode); } if (queryInfo.SettlementStatus != -0) { stringBuilder.Append(" and soi.SettlementStatus=@SettlementStatus"); dbParameters.Append("SettlementStatus", queryInfo.SettlementStatus); } if (queryInfo.StartTime != null) { stringBuilder.Append(" and soi.CreateTime> @StartTime"); dbParameters.Append("StartTime", queryInfo.StartTime.ToString("yyyy-MM-dd 00:00:00")); } if (queryInfo.EndTime != null) { stringBuilder.Append(" and soi.CreateTime< @EndTime"); dbParameters.Append("EndTime", queryInfo.EndTime.AddDays(1).ToString("yyyy-MM-dd 00:00:00")); } } return(stringBuilder.ToString()); }
public ActionResult ExportSettlementList(string startTime, string endTime, string companyName, string settlementCode, string orderCode, int settlementStatus) { var dtNow = DateTime.Now; SettlementQueryModel queryInfo = new SettlementQueryModel(); if (string.IsNullOrEmpty(startTime) || string.IsNullOrEmpty(endTime)) { queryInfo.StartTime = DateTime.Now.AddMonths(-3); queryInfo.EndTime = DateTime.Now; } else { queryInfo.StartTime = DateTime.Parse(startTime); queryInfo.EndTime = DateTime.Parse(endTime); } queryInfo.OrderCode = orderCode; queryInfo.SettlementCode = settlementCode; queryInfo.SupplierId = CurrentUser.SupplierID; queryInfo.SettlementStatus = settlementStatus; try { PageOf <SettlementOrderInfo> list = settlement.getSettlementList(queryInfo, new PageDTO() { PageIndex = PageNo, PageSize = int.MaxValue }); var dt = new DataTable(); dt.Columns.Add("結算單號"); dt.Columns.Add("生成時間"); dt.Columns.Add("訂單號/結算單號"); dt.Columns.Add("結算方"); dt.Columns.Add("商品總金額"); dt.Columns.Add("商品退款金額"); dt.Columns.Add("商家承擔關稅"); dt.Columns.Add("平台承擔關稅"); dt.Columns.Add("商家承担商品促销费用"); dt.Columns.Add("其他費用"); dt.Columns.Add("結算金額"); dt.Columns.Add("狀態"); List <SettlementOrderInfo> result = list.Items.ToList <SettlementOrderInfo>(); result.ForEach(l => { dt.Rows.Add(new object[] { l.SettlementCode, l.CreateTime.ToString("yyyy-MM-dd"), l.OrderCode, l.CompanyName, "$" + l.ProductTotalAmount, "$" + l.ProductRefundAmount, "$" + l.SupplierBearDutyAmount, "$" + l.BearDutyAmount, "$" + l.PromotionAmount.ToString("f2"), "$" + l.OtherAmount, "$" + (l.SettlementAmount - l.PromotionAmount).ToString("f2"), EnumHelper.GetDescription(l.SettlementStatus, typeof(SettlementStatus)) }); }); ExcelHelper.ExportByWeb(dt, "結算單列表", "settlementlist" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"); } catch (Exception ext) { LogHelper.Error(ext); } return(View()); }
/// <summary> /// 获取结算单列表 /// </summary> /// <param name="queryModel"></param> /// <param name="pModel"></param> /// <returns></returns> public PagedList <SettlementOrderInfo> getOrderList(SettlementQueryModel queryModel, PagingModel pModel) { var sql = @" SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY soi.Createtime DESC) AS RowNumber, soi.SettlementCode, soi.CreateTime, soi.SupplierId, OrderCode = ISNULL(soi.RefundCode,soi.OrderCode), s.CompanyName, soi.RmbProductTotalAmount, CASE WHEN soi.RmbProductRefundAmount>0 THEN soi.RmbProductTotalAmount ELSE 0 END RmbProductRefundAmount, soi.RmbSupplierBearDutyAmount, soi.RmbBearDutyAmount, soi.RmbOtherAmount, soi.RmbSettlementAmount, soi.ProductTotalAmount, CASE WHEN soi.ProductRefundAmount>0 THEN soi.ProductTotalAmount ELSE 0 END ProductRefundAmount, soi.SupplierBearDutyAmount, soi.BearDutyAmount, soi.OtherAmount, soi.SettlementAmount, soi.ExchangeRate, soi.SettlementStatus, DutyAmount = CASE WHEN soi.SettlementStatus=1 THEN sop.TaxAmount ELSE 0.00 END, RmbDutyAmount = CASE WHEN soi.SettlementStatus=1 THEN sop.RmbTaxAmount ELSE 0.00 END, ProductSettlementAmount = CASE WHEN soi.SettlementStatus=1 THEN (soi.ProductTotalAmount - ISNULL(CASE WHEN soi.RmbProductRefundAmount>0 THEN soi.RmbProductTotalAmount ELSE 0 END,0)*(1-sop.Commission/100)) ELSE 0.00 END, RmbProductSettlementAmount= CASE WHEN soi.SettlementStatus=1 THEN (soi.RmbProductTotalAmount - ISNULL(CASE WHEN soi.RmbProductRefundAmount>0 THEN soi.RmbProductTotalAmount ELSE 0 END,0))*(1-sop.Commission/100) ELSE 0.00 END, soi.SettlementTime, soi.TradeCode, ISNULL(SupplierPromotionDutyAmount,0) SupplierPromotionDutyAmount, ISNULL(PromotionAmount - SupplierPromotionDutyAmount,0) PromotionDutyAmount, ISNULL(SupplierPromotionDutyAmount,0)*soi.ExchangeRate RmbSupplierPromotionDutyAmount, ISNULL(PromotionAmount - SupplierPromotionDutyAmount,0)*soi.ExchangeRate RmbPromotionDutyAmount FROM SettlementOrderInfo soi(NOLOCK) INNER JOIN Supplier s(NOLOCK) ON s.SupplierID = soi.SupplierId LEFT JOIN SettlementOrderProducts sop(NOLOCK) ON sop.SettlementCode = soi.SettlementCode AND soi.SettlementType = 2 LEFT JOIN ( SELECT sp.SettlementCode,SUM(ISNULL(OriginalPrice - PromotionPrice,0)*sp.Quantity) PromotionAmount,SUM(ISNULL((OriginalPrice - PromotionPrice)*sp.Quantity*(p.PromotionCost/100.0),0)) SupplierPromotionDutyAmount FROM SettlementOrderProducts sp(NOLOCK) INNER JOIN SettlementOrderInfo s(NOLOCK) ON sp.SettlementCode = s.SettlementCode INNER JOIN OrderPromotions op ON op.OrderCode=s.OrderCode AND op.Sku=sp.Sku INNER JOIN Promotions p ON p.Id=op.PromotionId WHERE sp.Quantity>0 GROUP BY sp.SettlementCode ) t ON t.SettlementCode = soi.SettlementCode WHERE 1=1 {0} {1} {2} {3} {4} {5} ) a WHERE 1=1 {6} SELECT TotalCount = Count(soi.SettlementCode), ProductTotalAmount = ISNULL(SUM(ISNULL(soi.ProductTotalAmount,0)),0), RmbProductTotalAmount = ISNULL(SUM(ISNULL(soi.RmbProductTotalAmount,0)),0), ProductRefundAmount = ISNULL(SUM(ISNULL(CASE WHEN soi.ProductRefundAmount>0 THEN soi.ProductTotalAmount ELSE 0 END,0)),0), SupplierBearDutyAmount = ISNULL(SUM(ISNULL(soi.SupplierBearDutyAmount,0)),0), BearDutyAmount = ISNULL(SUM(ISNULL(soi.BearDutyAmount,0)),0), OtherAmount = ISNULL(SUM(ISNULL(soi.OtherAmount,0)),0), SettlementAmount = ISNULL(SUM(ISNULL(soi.SettlementAmount,0)),0), RmbProductTotalAmount = ISNULL(SUM(ISNULL(soi.RmbProductTotalAmount,0)),0), RmbProductRefundAmount = ISNULL(SUM(ISNULL(CASE WHEN soi.RmbProductRefundAmount>0 THEN soi.RmbProductTotalAmount ELSE 0 END,0)),0), RmbSupplierBearDutyAmount = ISNULL(SUM(ISNULL(soi.RmbSupplierBearDutyAmount,0)),0), RmbBearDutyAmount = ISNULL(SUM(ISNULL(soi.RmbBearDutyAmount,0)),0), RmbOtherAmount = ISNULL(SUM(ISNULL(soi.RmbOtherAmount,0)),0), RmbSettlementAmount = ISNULL(SUM(ISNULL(soi.RmbSettlementAmount,0)),0), SupplierPromotionDutyAmount = ISNULL(SUM(ISNULL(SupplierPromotionDutyAmount,0)),0) , RmbSupplierPromotionDutyAmount = ISNULL(SUM(ISNULL(SupplierPromotionDutyAmount,0)*soi.ExchangeRate),0), PromotionDutyAmount = ISNULL(SUM(ISNULL(PromotionAmount - SupplierPromotionDutyAmount,0)),0), RmbPromotionDutyAmount = ISNULL(SUM(ISNULL(PromotionAmount - SupplierPromotionDutyAmount,0)*soi.ExchangeRate),0) FROM SettlementOrderInfo soi(NOLOCK) INNER JOIN Supplier s(NOLOCK) ON s.SupplierID = soi.SupplierId LEFT JOIN ( SELECT sp.SettlementCode,SUM(ISNULL(OriginalPrice - PromotionPrice,0)*sp.Quantity) PromotionAmount,SUM(ISNULL((OriginalPrice - PromotionPrice)*sp.Quantity*(p.PromotionCost/100.0),0)) SupplierPromotionDutyAmount FROM SettlementOrderProducts sp(NOLOCK) INNER JOIN SettlementOrderInfo s(NOLOCK) ON sp.SettlementCode = s.SettlementCode INNER JOIN OrderPromotions op ON op.OrderCode=s.OrderCode AND op.Sku=sp.Sku INNER JOIN Promotions p ON p.Id=op.PromotionId WHERE sp.Quantity>0 GROUP BY sp.SettlementCode ) t ON t.SettlementCode = soi.SettlementCode WHERE 1=1 {0} {1} {2} {3} {4} {5} "; if (string.IsNullOrEmpty(queryModel.SettlementCode)) { sql = string.Format(sql, queryModel.SettlementStatus > -1 ? " AND soi.SettlementStatus = @SettlementStatus " : "", !string.IsNullOrEmpty(queryModel.OrderCode) ? " AND (soi.OrderCode = @OrderCode OR soi.RefundCode = @OrderCode) " : "", !string.IsNullOrEmpty(queryModel.CompanyName) ? " AND s.CompanyName LIKE '%' +@CompanyName+ '%' " : "", queryModel.StartTime.HasValue ? " AND soi.CreateTime >= @StartTime" : "", queryModel.EndTime.HasValue ? " AND soi.CreateTime <=@EndTime" : "", "", pModel.IsPaging ? " AND a.RowNumber >(@PageIndex-1)*@PageSize AND a.RowNumber <= @PageIndex*@PageSize" : ""); } else { sql = string.Format(sql, " AND soi.SettlementCode = @SettlementCode ", "", "", "", "", "", pModel.IsPaging ? " AND a.RowNumber >(@PageIndex-1)*@PageSize AND a.RowNumber <= @PageIndex*@PageSize" : ""); } var db = DbSFO2ORead; var parameters = db.CreateParameterCollection(); parameters.Append("@SettlementCode", queryModel.SettlementCode); parameters.Append("@SettlementStatus", queryModel.SettlementStatus); parameters.Append("@OrderCode", queryModel.OrderCode); parameters.Append("@CompanyName", queryModel.CompanyName); parameters.Append("@StartTime", queryModel.StartTime.Value); parameters.Append("@EndTime", queryModel.EndTime.Value.AddDays(1)); parameters.Append("@PageIndex", pModel.PageIndex); parameters.Append("@PageSize", pModel.PageSize); var ds = db.ExecuteSqlDataSet(sql, parameters); PagedList <SettlementOrderInfo> result = new PagedList <SettlementOrderInfo>(); result.ContentList = ds.Tables[0].ToList <SettlementOrderInfo>(); result.CurrentIndex = pModel.PageIndex; result.PageSize = pModel.PageSize; result.TotalObject = ds.Tables[1].ToList <SettlementOrderInfo>().First(); result.RecordCount = result.TotalObject.TotalCount; return(result); }
/// <summary> /// 获取结算单列表 /// </summary> /// <param name="queryInfo"></param> /// <param name="pageDTO"></param> /// <returns></returns> public PageOf <SettlementOrderInfo> getSettlementList(SettlementQueryModel queryModel, PageDTO pageDTO) { var db = DbSFO2ORead; var parameters = db.CreateParameterCollection(); var sql = @"SELECT soi.SettlementCode, soi.CreateTime, OrderCode = ISNULL(soi.RefundCode,soi.OrderCode), soi.RmbProductTotalAmount, CASE WHEN soi.RmbProductRefundAmount>0 THEN soi.RmbProductTotalAmount ELSE 0 END RmbProductRefundAmount, soi.RmbSupplierBearDutyAmount, soi.RmbBearDutyAmount, soi.RmbOtherAmount, soi.RmbSettlementAmount, soi.ProductTotalAmount, CASE WHEN soi.ProductRefundAmount>0 THEN soi.ProductTotalAmount ELSE 0 END ProductRefundAmount, soi.SupplierBearDutyAmount, soi.BearDutyAmount, soi.OtherAmount, soi.SettlementAmount, soi.ExchangeRate, soi.SettlementStatus, ISNULL(t.PromotionAmount,0) PromotionAmount FROM SettlementOrderInfo soi(NOLOCK) LEFT JOIN ( SELECT sp.SettlementCode,SUM(ISNULL((OriginalPrice - PromotionPrice)*sp.Quantity*(p.PromotionCost/100.0),0)) PromotionAmount FROM SettlementOrderProducts sp(NOLOCK) INNER JOIN SettlementOrderInfo s(NOLOCK) ON sp.SettlementCode = s.SettlementCode INNER JOIN OrderPromotions op ON op.OrderCode=s.OrderCode AND op.Sku=sp.Sku INNER JOIN Promotions p ON p.Id=op.PromotionId WHERE sp.Quantity>0 GROUP BY sp.SettlementCode ) t ON t.SettlementCode = soi.SettlementCode WHERE 1=1 and soi.SupplierId=@SupplierId {0} order by soi.CreateTime desc; SELECT TotalCount = Count(soi.SettlementCode), ProductTotalAmount = ISNULL(SUM(ISNULL(soi.ProductTotalAmount,0)),0), ProductRefundAmount = ISNULL(SUM(ISNULL(CASE WHEN soi.ProductRefundAmount>0 THEN soi.ProductTotalAmount ELSE 0 END,0)),0), SupplierBearDutyAmount = ISNULL(SUM(ISNULL(soi.SupplierBearDutyAmount,0)),0), BearDutyAmount = ISNULL(SUM(ISNULL(soi.BearDutyAmount,0)),0), OtherAmount = ISNULL(SUM(ISNULL(soi.OtherAmount,0)),0), SettlementAmount = ISNULL(SUM(ISNULL(soi.SettlementAmount,0)),0), RmbProductTotalAmount = ISNULL(SUM(ISNULL(soi.RmbProductTotalAmount,0)),0), RmbProductRefundAmount = ISNULL(SUM(ISNULL(CASE WHEN soi.RmbProductRefundAmount>0 THEN soi.RmbProductTotalAmount ELSE 0 END,0)),0), RmbSupplierBearDutyAmount = ISNULL(SUM(ISNULL(soi.RmbSupplierBearDutyAmount,0)),0), RmbBearDutyAmount = ISNULL(SUM(ISNULL(soi.RmbBearDutyAmount,0)),0), RmbOtherAmount = ISNULL(SUM(ISNULL(soi.RmbOtherAmount,0)),0), RmbSettlementAmount = ISNULL(SUM(ISNULL(soi.RmbSettlementAmount,0)),0), PromotionAmount = ISNULL(SUM(ISNULL(t.PromotionAmount,0)),0) FROM SettlementOrderInfo soi(NOLOCK) INNER JOIN Supplier s(NOLOCK) ON s.SupplierID = soi.SupplierId LEFT JOIN ( SELECT sp.SettlementCode,SUM(ISNULL((OriginalPrice - PromotionPrice)*sp.Quantity*(p.PromotionCost/100.0),0)) PromotionAmount FROM SettlementOrderProducts sp(NOLOCK) INNER JOIN SettlementOrderInfo s(NOLOCK) ON sp.SettlementCode = s.SettlementCode INNER JOIN OrderPromotions op ON op.OrderCode=s.OrderCode AND op.Sku=sp.Sku INNER JOIN Promotions p ON p.Id=op.PromotionId WHERE sp.Quantity>0 GROUP BY sp.SettlementCode ) t ON t.SettlementCode = soi.SettlementCode WHERE 1=1 and soi.SupplierId=@SupplierId {1}" ; var query = BindQuery(queryModel, parameters); sql = string.Format(sql, query, query); parameters.Append("@SupplierId", queryModel.SupplierId); parameters.Append("@PageIndex", pageDTO.PageIndex); parameters.Append("@PageSize", pageDTO.PageSize); var ds = db.ExecuteSqlDataSet(sql, parameters); PageOf <SettlementOrderInfo> result = new PageOf <SettlementOrderInfo>(); result.Items = ds.Tables[0].ToList <SettlementOrderInfo>(); result.PageIndex = pageDTO.PageIndex; result.PageSize = pageDTO.PageSize; result.TotalObject = ds.Tables[1].ToList <SettlementOrderInfo>().First(); result.Total = result.TotalObject.TotalCount; return(result); }
/// <summary> /// 获取结算单列表 /// </summary> /// <param name="queryInfo"></param> /// <param name="pageDTO"></param> /// <returns></returns> public PageOf <SettlementOrderInfo> getSettlementList(SettlementQueryModel queryInfo, PageDTO pageDTO) { return(dal.getSettlementList(queryInfo, pageDTO)); }
public ActionResult ExportSettlementList(DateTime startTime, DateTime endTime, string companyName, string settlementCode, string orderCode, int settlementStatus) { var dtNow = DateTime.Now; PagedList <SettlementOrderInfo> list = null; var qm = new SettlementQueryModel() { CompanyName = string.IsNullOrEmpty(companyName) ? "" : companyName, SettlementCode = string.IsNullOrEmpty(settlementCode) ? "" : settlementCode, OrderCode = string.IsNullOrEmpty(orderCode) ? "" : orderCode, EndTime = endTime == null ? dtNow : endTime, StartTime = startTime == null?dtNow.AddMonths(-6) : startTime, SettlementStatus = settlementStatus == 0 ? -1 : settlementStatus }; try { list = settlement.getOrderList(qm, new Models.PagingModel { IsPaging = false, PageIndex = this.PageNo, PageSize = this.PageSize }); var dt = new DataTable(); dt.Columns.Add("結算單號"); dt.Columns.Add("生成時間"); dt.Columns.Add("訂單號/結算單號"); dt.Columns.Add("結算方"); dt.Columns.Add("商品總金額"); dt.Columns.Add("商品退款金額"); dt.Columns.Add("商家承擔關稅"); dt.Columns.Add("平台承擔關稅"); dt.Columns.Add("商家承擔商品促銷費用"); dt.Columns.Add("平台承擔商品促銷費用"); dt.Columns.Add("其他費用"); dt.Columns.Add("結算金額"); dt.Columns.Add("狀態"); list.ContentList.ForEach(l => { dt.Rows.Add(new object[] { l.SettlementCode, l.CreateTime.ToString("yyyy-MM-dd"), l.OrderCode, l.CompanyName, "$" + l.ProductTotalAmount, "$" + l.ProductRefundAmount, "$" + l.SupplierBearDutyAmount, "$" + l.BearDutyAmount, "$" + l.SupplierPromotionDutyAmount.ToString("f2"), "$" + l.PromotionDutyAmount, "$" + l.OtherAmount, "$" + (l.SettlementAmount - l.SupplierPromotionDutyAmount).ToString("f2"), SFO2O.Admin.Common.EnumUtils.GetEnumDescription((SFO2O.Admin.Models.SettlementStatus)l.SettlementStatus) }); }); ExcelHelper.ExportByWeb(dt, "結算單列表", "settlementlist" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"); } catch (Exception ext) { LogHelper.Error(ext); } return(View()); }
/// <summary> /// 获取结算单列表 /// </summary> /// <param name="queryModel"></param> /// <param name="pModel"></param> /// <returns></returns> public PagedList <SettlementOrderInfo> getOrderList(SettlementQueryModel queryModel, PagingModel pModel) { return(Dao.getOrderList(queryModel, pModel)); }