public void UpdatePost(string livePageID, int qtyOfOrders, decimal amount, DateTime endTime, int maxViews)
{
using (conn = new SqlConnection(connectionString))
{
var live_repo = new LivePostsRepository();
int liveid = live_repo.Select(livePageID);
string sql = "UPDATE LivePosts SET endTime=@endTime,QtyOfOrders=@qtyOfOrders,Amount=@amount,MaxViews=@maxViews WHERE ID=@liveid ";
conn.Execute(sql, new { endTime, qtyOfOrders, amount, maxViews, liveid });
}
}
public int GetQtyOfOrders(string livePageId)
{
using (conn = new SqlConnection(connectionString))
{
LivePostsRepository live_repo = new LivePostsRepository();
var liveId = live_repo.Select(livePageId);
string sql =
"select COUNT(OrderID) as Count from Orders o WHERE o.LiveID = @liveId";
int result = conn.QueryFirstOrDefault <int>(sql, new { liveId });
return(result);
}
}
public List <Order> SelectOrdersByLivePageId(string livePageId)
{
LivePostsRepository live_repo = new LivePostsRepository();
int liveId = live_repo.Select(livePageId);
using (conn = new SqlConnection(connectionString))
{
string sql = "SELECT OrderID,ProductID,CustomerID,Keyword,OrderDateTime,Quantity,LiveID From Orders Where LiveID = @liveId order by OrderDateTime";
List <Order> orders = conn.Query <Order>(sql, new { liveId }).ToList();
return(orders);
}
}
public decimal GetAmount(string livePageId)
{
using (conn = new SqlConnection(connectionString))
{
LivePostsRepository live_repo = new LivePostsRepository();
var liveId = live_repo.Select(livePageId);
string sql =
"select SUM(p.UnitPrice*o.Quantity) as Amount from Products p inner join Orders o on o.ProductID = p.ProductID where o.LiveID = @liveId";
decimal?amount = conn.QueryFirstOrDefault <decimal?>(sql, new { liveId });
decimal result = 0;
if (amount != null)
{
result = (decimal)amount;
}
return(result);
}
}