/// <summary>
///
/// </summary>
/// <param name="sellerUserName"></param>
/// <param name="orderNumber"></param>
/// <param name="buyUserName"></param>
/// <param name="orderState">0表示全部</param>
/// <param name="myBeginCreateTime"></param>
/// <param name="myEndCreateTime"></param>
/// <param name="myBeginBuyTime"></param>
/// <param name="myEndBuyTime"></param>
/// <param name="pageItemCount"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
public BuyStonesOrder[] GetBuyStonesOrderList(string sellerUserName, string orderNumber, string buyUserName, int orderState, MyDateTime myBeginCreateTime, MyDateTime myEndCreateTime, MyDateTime myBeginBuyTime, MyDateTime myEndBuyTime, int pageItemCount, int pageIndex)
{
BuyStonesOrder[] orders = null;
MySqlConnection myconn = null;
try
{
myconn = MyDBHelper.Instance.CreateConnection();
myconn.Open();
MySqlCommand mycmd = myconn.CreateCommand();
string sqlTextA = "select b.*, s.*, f.CreditValue as SellerCreditValue, f.Exp as SellerExpValue " +
"from buystonesrecord b " +
"left join sellstonesorder s on s.OrderNumber = b.OrderNumber " +
"left join playerfortuneinfo f on f.userId = (select u.id from playersimpleinfo u where u.UserName = s.SellerUserName)";
StringBuilder builder = new StringBuilder();
if (!string.IsNullOrEmpty(sellerUserName))
{
builder.Append(" s.SellerUserName = @SellerUserName ");
string encryptSellerUserName = DESEncrypt.EncryptDES(sellerUserName);
mycmd.Parameters.AddWithValue("@SellerUserName", encryptSellerUserName);
}
if (!string.IsNullOrEmpty(orderNumber))
{
if (builder.Length > 0)
{
builder.Append(" and ");
}
builder.Append(" s.OrderNumber = @OrderNumber ");
mycmd.Parameters.AddWithValue("@OrderNumber", orderNumber);
}
if (!string.IsNullOrEmpty(buyUserName))
{
if (builder.Length > 0)
{
builder.Append(" and ");
}
builder.Append(" b.BuyerUserName = @BuyerUserName ");
string encryptUserName = DESEncrypt.EncryptDES(buyUserName);
mycmd.Parameters.AddWithValue("@BuyerUserName", encryptUserName);
}
if (orderState > 0)
{
if (builder.Length > 0)
{
builder.Append(" and ");
}
builder.Append(" s.OrderState = @OrderState ");
mycmd.Parameters.AddWithValue("@OrderState", orderState);
}
if (myBeginCreateTime != null && !myBeginCreateTime.IsNull && myEndCreateTime != null && !myEndCreateTime.IsNull)
{
if (builder.Length != 0)
{
builder.Append(" and ");
}
DateTime beginCreateTime = myBeginCreateTime.ToDateTime();
DateTime endCreateTime = myEndCreateTime.ToDateTime();
if (beginCreateTime >= endCreateTime)
{
return(null);
}
builder.Append(" s.SellTime >= @beginCreateTime and s.SellTime < @endCreateTime ");
mycmd.Parameters.AddWithValue("@beginCreateTime", beginCreateTime);
mycmd.Parameters.AddWithValue("@endCreateTime", endCreateTime);
}
if (myBeginBuyTime != null && !myBeginBuyTime.IsNull && myEndBuyTime != null && !myEndBuyTime.IsNull)
{
if (builder.Length != 0)
{
builder.Append(" and ");
}
DateTime beginBuyTime = myBeginBuyTime.ToDateTime();
DateTime endBuyTime = myEndBuyTime.ToDateTime();
if (beginBuyTime >= endBuyTime)
{
return(null);
}
builder.Append(" b.BuyTime >= @beginBuyTime and b.BuyTime < @endBuyTime ");
mycmd.Parameters.AddWithValue("@beginBuyTime", beginBuyTime);
mycmd.Parameters.AddWithValue("@endBuyTime", endBuyTime);
}
string sqlWhere = "";
if (builder.Length > 0)
{
sqlWhere = " where " + builder.ToString();
}
string sqlOrderLimit = " order by b.id desc ";
if (pageItemCount > 0)
{
int start = pageIndex <= 0 ? 0 : (pageIndex - 1) * pageItemCount;
sqlOrderLimit += " limit " + start.ToString() + ", " + pageItemCount;
}
string sqlAllText = sqlTextA + sqlWhere + sqlOrderLimit;
mycmd.CommandText = sqlAllText;
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(table);
if (table != null)
{
orders = MetaDBAdapter <BuyStonesOrder> .GetBuyStonesOrderFromDataTable(table);
}
table.Clear();
table.Dispose();
adapter.Dispose();
mycmd.Dispose();
return(orders);
}
finally
{
MyDBHelper.Instance.DisposeConnection(myconn);
}
}