public PlayerBuyVirtualShoppingItemRecord[] GetPlayerBuyVirtualShoppingItemRecordByID(int userID, int itemID, MyDateTime beginBuyTime, MyDateTime endBuyTime, int pageItemCount, int pageIndex)
{
PlayerBuyVirtualShoppingItemRecord[] items = null;
bool isOK = MyDBHelper.Instance.ConnectionCommandSelect(mycmd =>
{
string sqlTextA = "SELECT n.* FROM playerbuyvirtualshoppingitemrecord n ";
StringBuilder builder = new StringBuilder();
if (userID > 0)
{
builder.Append(" n.UserID = @UserID ");
mycmd.Parameters.AddWithValue("@UserID", userID);
}
if (itemID > 0)
{
if (builder.Length != 0)
{
builder.Append(" and ");
}
builder.Append(" n.VirtualShoppingItemID = @VirtualShoppingItemID ");
mycmd.Parameters.AddWithValue("@VirtualShoppingItemID", itemID);
}
if (beginBuyTime != null && !beginBuyTime.IsNull && endBuyTime != null && !endBuyTime.IsNull)
{
if (builder.Length != 0)
{
builder.Append(" and ");
}
DateTime beginTime = beginBuyTime.ToDateTime();
DateTime endTime = endBuyTime.ToDateTime();
if (beginTime >= endTime)
{
return;
}
builder.Append(" n.BuyTime >= @beginTime and n.BuyTime < @endTime ");
mycmd.Parameters.AddWithValue("@beginTime", beginTime);
mycmd.Parameters.AddWithValue("@endTime", endTime);
}
string sqlWhere = "";
if (builder.Length > 0)
{
sqlWhere = " where " + builder.ToString();
}
string sqlOrderLimit = " order by n.ID desc ";
if (pageItemCount > 0)
{
int start = pageIndex <= 0 ? 0 : (pageIndex - 1) * pageItemCount;
sqlOrderLimit += " limit " + start.ToString() + ", " + pageItemCount;
}
string sqlAllText = "select ttt.*, s.UserName as UserName, v.Name as VirtualShoppingItemName from " +
" ( " + sqlTextA + sqlWhere + sqlOrderLimit +
" ) ttt " +
" left join playersimpleinfo s on ttt.UserID = s.id " +
" left join virtualshoppingitem v on ttt.VirtualShoppingItemID=v.ID";
mycmd.CommandText = sqlAllText;
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(table);
items = MetaDBAdapter <PlayerBuyVirtualShoppingItemRecord> .GetPlayerBuyVirtualShoppingItemRecordFromDataTable(table);
table.Dispose();
adapter.Dispose();
});
return(items);
}
