string TradingPagingQuery(string where, TcpMsg.TradingSortObject type, Int32 pageNum, Int32 itemCountPerPage, TcpMsg.SortingType sort)
{
string qry = string.Empty;
string orderby = string.Empty;
string sorttype = "asc";
if (sort == TcpMsg.SortingType.Desc)
{
sorttype = "desc";
}
switch (type)
{
case TcpMsg.TradingSortObject.CardType:
orderby = $"order by cardtype {sorttype}, tokenidx {sorttype}";
break;
case TcpMsg.TradingSortObject.Index:
orderby = $"order by tokenidxnum {sorttype}";
break;
case TcpMsg.TradingSortObject.Grade:
orderby = $"order by grade {sorttype}, cardid {sorttype}, tokenidx {sorttype}";
break;
case TcpMsg.TradingSortObject.RegDate:
orderby = $"order by regDate {sorttype}, cardid {sorttype}, tokenidx {sorttype}";
break;
case TcpMsg.TradingSortObject.SalePrice:
orderby = $"order by sale_price {sorttype}, cardid {sorttype}, tokenidx {sorttype}";
break;
default:
return(qry);
}
string limit = $"limit {pageNum * itemCountPerPage}, {itemCountPerPage}";
qry = $"select * from {Table.TblTrade.Name} where {where} {orderby} {limit};";
return(qry);
}
public (Int32, Int32, List <Table.TblTrade>) GetTblTrade(Int16 jobType, Int32 cardId, Int32 tokenIdxMin, Int32 tokenIdxMax, TcpMsg.TradingSortObject obj, Int32 pageNum, Int32 itemCountPerPage, TcpMsg.SortingType sort)
{
var totalCount = 0;
List <Table.TblTrade> ret = null;
using (IDbConnection conn = Database.GetDbConnection())
{
string where = string.Empty;
if (jobType > 0)
{
where = $"jobtype={jobType} and ";
}
if (cardId > 0)
{
where += $"cardid={cardId} and ";
}
if (tokenIdxMin > 0 && tokenIdxMax > 0)
{
where += $"tokenidxnum between {tokenIdxMin} and {tokenIdxMax} and ";
}
else if (tokenIdxMin < 0 && tokenIdxMax > 0)
{
where += $"tokenidxnum <= {tokenIdxMax} and ";
}
else if (tokenIdxMin > 0 && tokenIdxMax < 0)
{
where += $"tokenidxnum >= {tokenIdxMin} and ";
}
where += $"status={(Int32)TcpMsg.TradeStatus.Onsale} and regdate > (now() - interval {Define.EXPIRY_DATE} day)";
string qry = $"select count(*) from {Table.TblTrade.Name} where {where};";
totalCount = Dapper.SqlMapper.Query <int>(conn, qry).FirstOrDefault();
if (totalCount > 0)
{
if (totalCount < pageNum * itemCountPerPage)
{
pageNum = 0;
}
qry = TradingPagingQuery(where, obj, pageNum, itemCountPerPage, sort);
ret = Dapper.SqlMapper.Query <Table.TblTrade>(conn, qry).ToList();
}
}
return(pageNum, totalCount, ret);
}