public ActionResult Statistics(SaleFilterModel model)
{
if (Session["ItemsPerPage"] != null)
{
model.ItemsPerPage = (int)Session["ItemsPerPage"];
}
PagerData pager = new PagerData()
{
ItemsPerPage = model.ItemsPerPage, CurrentPage = model.page
};
var result = _salesManager.GetSales(model, null, pager);
if (Request.IsAjaxRequest())
{
return(Json(new { CurrentPage = pager.CurrentPage, TotalPages = pager.TotalPages, Result = result }, JsonRequestBehavior.AllowGet));
}
SalesListModel listModel = new SalesListModel()
{
Sales = result,
Pager = pager,
Filter = model
};
return(View("Statistics", listModel));
}
//public async Task<List<SalesListModel>> RegisteredSalesList(SalesListModel model)
//{
// using (salesRepository = new SalesRepository())
// {
// return await salesRepository.RegisteredSalesList(model);
// }
//}
public List <SalesListModel> RegisteredSalesList(SalesListModel model, out int RowCount)
{
using (salesRepository = new SalesRepository())
{
return(salesRepository.RegisteredSalesList(model, out RowCount));
}
}
//public async Task<List<SalesListModel>> RegisteredSalesList(SalesListModel model)
//{
// using (var connection = new SqlConnection(ConnectionString))
// {
// connection.Open();
// TranStatus transaction = new TranStatus();
// DynamicParameters parameter = new DynamicParameters();
// parameter.Add("@AdminID", model.UserId);
// var result = await connection.QueryAsync<SalesListModel>("RegisteredSalesList", parameter, commandType: CommandType.StoredProcedure);
// return result.ToList();
// }
//}
public List <SalesListModel> RegisteredSalesList(SalesListModel model, out int RowCount)
{
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
DynamicParameters parameter = new DynamicParameters();
parameter.Add("@AdminID", model.UserId);
parameter.Add("@pageIndex", model.pageIndex);
parameter.Add("@pageSize", model.pageSize);
parameter.Add("@Search", model.Search);
parameter.Add("@RowCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
var result = connection.Query <SalesListModel>("RegisteredSalesList", parameter, commandType: CommandType.StoredProcedure);
RowCount = parameter.Get <int>("@RowCount");
return(result.ToList());
}
}
public IActionResult RegisteredSalesList([FromBody] SalesListModel model)
{
TranStatus transaction = new TranStatus();
Dictionary <String, Object> dctData = new Dictionary <string, object>();
HttpStatusCode statusCode = HttpStatusCode.OK;
try
{
List <SalesListModel> RegisteredSalesList = new List <SalesListModel>();
int rowcount = 0;
RegisteredSalesList = isales.RegisteredSalesList(model, out rowcount);
dctData.Add("RegisteredSalesList", RegisteredSalesList);
dctData.Add("RowCount", rowcount);
}
catch (Exception ex)
{
transaction = CommonHelper.TransactionErrorHandler(ex);
statusCode = HttpStatusCode.BadRequest;
}
dctData.Add("Status", transaction);
return(this.StatusCode(Convert.ToInt32(statusCode), dctData));
}
/// <summary>
/// 获取销售一览表信息
/// </summary>
/// <param name="request"></param>
/// <param name="currentUser"></param>
/// <returns></returns>
public async Task <ResponseObject <SalesListModel> > GetSalesList(RequestGet request, CurrentUser currentUser)
{
try
{
List <SalesCountListModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TSSMSalesOrderDetailDbModel, TSSMSalesOrderMainDbModel,
TBMDictionaryDbModel, TBMCustomerFileDbModel, TSMUserAccountDbModel, TBMPackageDbModel>(
(t, t0, t1, t2, t3, t4) => new object[]
{
JoinType.Left, t.MainId == t0.ID,
JoinType.Left, t0.OrderTypeId == t1.ID,
JoinType.Left, t0.CustomerId == t2.ID,
JoinType.Left, t0.SalesmanId == t3.ID,
JoinType.Left, t.PackageId == t4.ID
})
.Where((t, t0, t1, t2, t3, t4) => t0.CompanyId == currentUser.CompanyID && t4.DicValue != null);
//.OrderBy((t, t0, t1, t2, t3, t4) => t0.OrderNo);
//查询条件
if (request.QueryConditions != null && request.QueryConditions.Count > 0)
{
var conditionals = SqlSugarUtil.GetConditionalModels(request.QueryConditions);
foreach (ConditionalModel item in conditionals)
{
if (item.FieldName.ToLower() == "orderdate" ||
item.FieldName.ToLower() == "customerid" ||
item.FieldName.ToLower() == "ordertypeid" ||
item.FieldName.ToLower() == "salesmanid")
{
item.FieldName = $"t0.{item.FieldName}";
continue;
}
}
query.Where(conditionals);
}
//排序条件
if (request.OrderByConditions != null && request.OrderByConditions.Count > 0)
{
foreach (var item in request.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TSSMSalesOrderMainDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"t0.{item.Column} {item.Condition}");
}
}
else
{
query.OrderBy((t, t0, t1, t2, t3, t4) => t0.OrderNo);
}
SalesListModel _list = new SalesListModel();
_list.Total_Number = query.Count();
#region 统计数量
var SalesNum = query.Select((t, t0, t1, t2, t3, t4) => SqlFunc.AggregateSum(t.SalesNum)).ToList();
_list.Total_Num = SalesNum.Count > 0 ? Convert.ToDecimal(SalesNum.ToList()[0]) : 0;
#endregion
#region 统计金额
var SalesAmount = query.Select((t, t0, t1, t2, t3, t4) => SqlFunc.AggregateSum(t.SalesAmount)).ToList();
_list.Total_Amount = SalesAmount.Count > 0 ? Convert.ToDecimal(SalesAmount.ToList()[0]) : 0;
#endregion
if (request.IsPaging)
{
int skipNum = request.PageSize * (request.PageIndex - 1);
queryData = await query
.Select((t, t0, t1, t2, t3, t4) => new SalesCountListModel
{
OrderNo = t0.OrderNo,
OrderTypeId = t0.OrderTypeId,
OrderTypeName = t1.DicValue,
CustomerId = t0.CustomerId,
CustomerName = t2.CustomerName,
SalesManId = t0.SalesmanId,
SalesManName = t3.AccountName,
PackageId = t.PackageId,
PackageName = t4.DicValue,
SalesNum = t.SalesNum,
SalesAmount = t.UnitPrice,
AuditStatus = (byte)t0.AuditStatus,
OrderDate = t0.OrderDate
}).Skip(skipNum).Take(request.PageSize).ToListAsync();
}
else
{
queryData = await query
.Select((t, t0, t1, t2, t3, t4) => new SalesCountListModel
{
OrderNo = t0.OrderNo,
OrderTypeId = t0.OrderTypeId,
OrderTypeName = t1.DicValue,
CustomerId = t0.CustomerId,
CustomerName = t2.CustomerName,
SalesManId = t0.SalesmanId,
SalesManName = t3.AccountName,
PackageId = t.PackageId,
PackageName = t4.DicValue,
SalesNum = t.SalesNum,
SalesAmount = t.UnitPrice,
AuditStatus = (byte)t0.AuditStatus,
OrderDate = t0.OrderDate
})
.ToListAsync();
}
_list.List = queryData;
return(ResponseUtil <SalesListModel> .SuccessResult(_list));
}
catch (Exception ex)
{
return(ResponseUtil <SalesListModel>
.FailResult(null, $"查询销售一览表发生异常{System.Environment.NewLine}{ex.Message}"));
}
}
