private IEnumerable <ProductItemModel> GetProductsModel()
{
var sb = new StringBuilder();
sb.AppendLine("select distinct p.*,isnull(s.Stock,0) [Stock]");
sb.AppendLine("from Product p");
sb.AppendLine("left join Stock s");
sb.AppendLine(" on s.ProductId = p.Id");
var result = db.GetModelFromQuery <ProductItemModel>(sb.ToString(), new { });
return(result);
}
public JsonResult SellerComission(DateTime startDate, DateTime endDate)
{
try
{
startDate = startDate.Date;
endDate = endDate.Date.AddDays(1);
var userId = this.User.Identity.GetUserId();
var sb = new StringBuilder();
sb.AppendLine("select concat(c.FirstName,' ',c.LastName) [Client],");
sb.AppendLine(" i.Id [InvoiceId],");
sb.AppendLine(" i.Created [Date],");
sb.AppendLine(" i.Subtotal - i.Discount [SellPrice],");
sb.AppendLine(" (i.Subtotal - i.Discount)*u.Comission [Comission]");
sb.AppendLine("from Invoice i");
sb.AppendLine("join [dbo].[AspNetUsers] u");
sb.AppendLine(" on i.UserId = u.Id");
sb.AppendLine("join Client c");
sb.AppendLine(" on c.Id = i.ClientId");
sb.AppendLine("where i.Created >= @startDate");
sb.AppendLine("and i.Created < @endDate");
sb.AppendLine("and i.UserId = @userId");
var result = db.GetModelFromQuery <SellerComissionModel>(sb.ToString(), new { startDate = startDate, endDate = endDate, userId = userId });
var data = new
{
StartDate = startDate.ToLongDateString(),
EndDate = endDate.ToLongDateString(),
Detail = result,
TotalSellPrice = result.Sum(r => r.SellPrice),
TotalComission = result.Sum(r => r.Comission)
};
return(Json(new { Response = true, Data = data }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json(new { Response = false, Message = ex.Message }, JsonRequestBehavior.AllowGet));
throw;
}
}