public StoneStackDailyRecordInfo[] GetAllStoneStackDailyRecordInfo()
{
MySqlConnection myconn = MyDBHelper.Instance.CreateConnection();
MySqlCommand mycmd = null;
try
{
string sqlText = "SELECT * FROM stonestackdailyrecordinfo order by id;";
mycmd = myconn.CreateCommand();
mycmd.CommandText = sqlText;
myconn.Open();
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(table);
var lists = MetaDBAdapter <StoneStackDailyRecordInfo> .GetStoneStackDailyRecordInfoFromDataTable(table);
table.Clear();
table.Dispose();
adapter.Dispose();
return(lists);
}
finally
{
if (mycmd != null)
{
mycmd.Dispose();
}
MyDBHelper.Instance.DisposeConnection(myconn);
}
}
public WaitToReferAwardRecord[] GetWaitToAwardExpRecord(string newRegisterUserName)
{
WaitToReferAwardRecord[] records = null;
MySqlConnection myconn = null;
try
{
DataTable dt = new DataTable();
myconn = MyDBHelper.Instance.CreateConnection();
myconn.Open();
string cmdText = "select * from waittoawardexprecord where NewRegisterUserNme = @NewRegisterUserNme ";
MySqlCommand mycmd = new MySqlCommand(cmdText, myconn);
mycmd.Parameters.AddWithValue("@NewRegisterUserNme", DESEncrypt.EncryptDES(newRegisterUserName));
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(dt);
records = MetaDBAdapter <WaitToReferAwardRecord> .GetWaitToAwardExpRecordListFromDataTable(dt);
dt.Clear();
dt.Dispose();
adapter.Dispose();
mycmd.Dispose();
return(records);
}
catch (Exception exc)
{
throw exc;
}
finally
{
MyDBHelper.Instance.DisposeConnection(myconn);
}
}
/// <summary>
/// 包括Waiting和Splited
/// </summary>
/// <returns></returns>
public StoneDelegateSellOrderInfo[] GetAllNotFinishedStoneDelegateSellOrderInfo()
{
MySqlConnection myconn = MyDBHelper.Instance.CreateConnection();
MySqlCommand mycmd = null;
try
{
string sqlText = "SELECT n.* , p.UserName FROM notfinishedstonedelegatesellorderinfo n left join playersimpleinfo p on n.UserID = p.id; ";
mycmd = myconn.CreateCommand();
mycmd.CommandText = sqlText;
myconn.Open();
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(table);
var lists = MetaDBAdapter <StoneDelegateSellOrderInfo> .GetStoneDelegateSellOrderInfoFromDataTable(table);
table.Clear();
table.Dispose();
adapter.Dispose();
return(lists);
}
finally
{
if (mycmd != null)
{
mycmd.Dispose();
}
MyDBHelper.Instance.DisposeConnection(myconn);
}
}
public MinesBuyRecord[] GetAllTempMineTradeRecords()
{
MySqlConnection myconn = MyDBHelper.Instance.CreateConnection();
MySqlCommand mycmd = null;
try
{
DataTable table = new DataTable();
myconn.Open();
mycmd = myconn.CreateCommand();
string cmdText = "select a.*, b.UserName from tempminesbuyrecord a left join playersimpleinfo b on a.UserID=b.id ";
mycmd.CommandText = cmdText;
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(table);
var lists = MetaDBAdapter <MinesBuyRecord> .GetMinesBuyRecordFromDataTable(table);
table.Clear();
table.Dispose();
adapter.Dispose();
return(lists);
}
finally
{
if (mycmd != null)
{
mycmd.Dispose();
}
MyDBHelper.Instance.DisposeConnection(myconn);
}
}
//public StoneFactoryStackChangeRecord[] GetFactoryStackChangedRecord(int userID, MyDateTime beginTime, MyDateTime endTime, int pageItemCount, int pageIndex)
//{
// return null;
//}
//public StoneFactoryOneGroupSlave[] GetFactorySlaveGroupInfos(int userID, MyDateTime beginTime, MyDateTime endTime, int pageItemCount, int pageIndex)
//{
// return null;
//}
public StoneFactorySystemDailyProfit[] GetFactorySystemDailyProfitRecords(int pageItemCount, int pageIndex)
{
StoneFactorySystemDailyProfit[] items = null;
bool isOK = MyDBHelper.Instance.ConnectionCommandSelect(mycmd =>
{
string sqlTextA = "SELECT n.* FROM stonefactorysystemdailyprofit n ";
StringBuilder builder = new StringBuilder();
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 = sqlTextA + sqlOrderLimit;
mycmd.CommandText = sqlAllText;
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(table);
items = MetaDBAdapter <StoneFactorySystemDailyProfit> .GetStoneFactorySystemDailyProfitItemFromDataTable(table);
table.Dispose();
adapter.Dispose();
});
return(items);
}
public AlipayRechargeRecord[] GetAllExceptionAlipayRechargeRecords()
{
AlipayRechargeRecord[] records = null;
MySqlConnection myconn = null;
try
{
DataTable dt = new DataTable();
myconn = MyDBHelper.Instance.CreateConnection();
myconn.Open();
string cmdText = "select * from alipayrecharge_exception_record ";
MySqlCommand mycmd = new MySqlCommand(cmdText, myconn);
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(dt);
records = MetaDBAdapter <AlipayRechargeRecord> .GetAlipayRechargeRecordListFromDataTable(dt);
dt.Clear();
dt.Dispose();
adapter.Dispose();
mycmd.Dispose();
return(records);
}
catch (Exception exc)
{
throw exc;
}
finally
{
MyDBHelper.Instance.DisposeConnection(myconn);
}
}
public RouletteWinnerRecord GetPayWinAwardRecord(int UserID, int RouletteAwardItemID, DateTime WinTime)
{
RouletteWinnerRecord record = null;
MySqlConnection myconn = null;
MySqlCommand mycmd = null;
try
{
myconn = MyDBHelper.Instance.CreateConnection();
myconn.Open();
DataTable table = new DataTable();
string sqlTextB = "select r.* from roulettewinnerrecord r " +
" where r.UserID = @UserID and r.AwardItemID = @AwardItemID order by r.id desc limit 1 ";// and r.WinTime >= @WinTime";
mycmd = myconn.CreateCommand();
mycmd.Parameters.AddWithValue("@UserID", UserID);
mycmd.Parameters.AddWithValue("@AwardItemID", RouletteAwardItemID);
//mycmd.Parameters.AddWithValue("@WinTime", WinTime);
string sqlAllText = "select ttt.*, s.UserName as UserName from " +
" ( " + sqlTextB +
" ) ttt " +
" left join playersimpleinfo s on ttt.UserID = s.id ";
mycmd.CommandText = sqlAllText;
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(table);
var records = MetaDBAdapter <RouletteWinnerRecord> .GetRouletteWinnerRecordFromDataTable(table);
table.Clear();
table.Dispose();
adapter.Dispose();
if (records != null && records.Length != 0)
{
record = records[records.Length - 1];
if (record.RouletteAwardItemID != RouletteAwardItemID)
{
return(null);
}
}
return(record);
}
finally
{
if (mycmd != null)
{
mycmd.Dispose();
}
if (myconn != null)
{
myconn.Close();
myconn.Dispose();
}
}
}
public StoneDelegateBuyOrderInfo[] GetNotFinishedStoneDelegateBuyOrderInfoByPlayer(int userID)
{
MySqlConnection myconn = MyDBHelper.Instance.CreateConnection();
MySqlCommand mycmd = null;
try
{
string sqlText = "SELECT n.* , p.UserName FROM notfinishedstonedelegatebuyorderinfo n left join playersimpleinfo p on n.UserID = p.id where n.UserID = @UserID; ";
mycmd = myconn.CreateCommand();
mycmd.CommandText = sqlText;
mycmd.Parameters.AddWithValue("@UserID", userID);
myconn.Open();
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(table);
var lists = MetaDBAdapter <StoneDelegateBuyOrderInfo> .GetStoneDelegateBuyOrderInfoFromDataTable(table, false);
table.Clear();
table.Dispose();
adapter.Dispose();
return(lists);
}
finally
{
if (mycmd != null)
{
mycmd.Dispose();
}
MyDBHelper.Instance.DisposeConnection(myconn);
}
}
private void SumUserAccountProfitRMBCount(PlayerStoneFactoryAccountInfo account, MySqlCommand mycmd)
{
string sqlText = "select * from stonefactoryprofitrmbchangedrecord where `UserID`=@UserID ";
mycmd.CommandText = sqlText;
mycmd.Parameters.Clear();
mycmd.Parameters.AddWithValue("@UserID", account.UserID);
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(table);
StoneFactoryProfitRMBChangedRecord[] items = MetaDBAdapter <StoneFactoryProfitRMBChangedRecord> .GetStoneFactoryProfitRMBChangedRecordItemFromDataTable(table);
table.Dispose();
adapter.Dispose();
decimal sumProfitRMB = 0;
decimal sumWithdrawableProfitRMB = 0;
//已经提现的收益灵币（该值为负数）
decimal sumWithdrawedProfitRMB = 0;
decimal sumYesterdayProfitRMB = 0;
//按时间顺序
if (items != null && items.Length != 0)
{
DateTime timeNow = DateTime.Now;
foreach (var item in items)
{
if (item.ProfitType == FactoryProfitOperType.WithdrawRMB)
{
sumWithdrawedProfitRMB += item.OperRMB;
}
else
{
sumProfitRMB += item.OperRMB;
DateTime itemOperTime = item.OperTime.ToDateTime();
if ((timeNow.Date - itemOperTime.Date).Days >= StoneFactoryConfig.ProfitRMBWithdrawLimitDays)
{
sumWithdrawableProfitRMB += item.OperRMB;
}
else
{
if ((timeNow - itemOperTime).TotalHours <= 24)
{
sumYesterdayProfitRMB += item.OperRMB;
}
}
}
}
}
account.YesterdayTotalProfitRMB = sumYesterdayProfitRMB;
account.TotalProfitRMB = sumProfitRMB;
account.WithdrawableProfitRMB = sumWithdrawableProfitRMB + sumWithdrawedProfitRMB;
}
public StoneFactoryProfitRMBChangedRecord[] GetProfitRecords(int userID, MyDateTime beginTime, MyDateTime endTime, int pageItemCount, int pageIndex)
{
StoneFactoryProfitRMBChangedRecord[] items = null;
bool isOK = MyDBHelper.Instance.ConnectionCommandSelect(mycmd =>
{
string sqlTextA = "SELECT n.* FROM stonefactoryprofitrmbchangedrecord n ";
StringBuilder builder = new StringBuilder();
if (userID > 0)
{
builder.Append(" n.UserID = @UserID ");
mycmd.Parameters.AddWithValue("@UserID", userID);
}
if (beginTime != null && !beginTime.IsNull && endTime != null && !endTime.IsNull)
{
if (builder.Length != 0)
{
builder.Append(" and ");
}
DateTime bTime = beginTime.ToDateTime();
DateTime eTime = endTime.ToDateTime();
if (bTime >= eTime)
{
return;
}
builder.Append(" n.OperTime >= @beginTime and n.OperTime < @endTime ");
mycmd.Parameters.AddWithValue("@beginTime", bTime);
mycmd.Parameters.AddWithValue("@endTime", eTime);
}
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 = sqlTextA + sqlWhere + sqlOrderLimit;
mycmd.CommandText = sqlAllText;
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(table);
items = MetaDBAdapter <StoneFactoryProfitRMBChangedRecord> .GetStoneFactoryProfitRMBChangedRecordItemFromDataTable(table);
table.Dispose();
adapter.Dispose();
});
return(items);
}
public UserRemoteServerItem[] GetUserRemoteServerItems()
{
UserRemoteServerItem[] items = null;
MyDBHelper.Instance.ConnectionCommandSelect(mycmd =>
{
string sqlText = "select * from userremoteserveritem ";
mycmd.CommandText = sqlText;
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
DataTable table = new DataTable();
adapter.Fill(table);
items = MetaDBAdapter <UserRemoteServerItem> .GetUserRemoteServerItemFromDataTable(table);
table.Clear();
table.Dispose();
adapter.Dispose();
if (items == null || items.Length == 0)
{
string insertText = "insert into userremoteserveritem (`ServerType`,`PayMoneyYuan`,`ShopName`,`Description`) values (@Type1,@Money1,@ShopName1,@Description1), (@Type2,@Money2,@ShopName2,@Description2), (@Type3,@Money3,@ShopName3,@Description3), (@Type4,@Money4,@ShopName4,@Description4)";
mycmd.CommandText = insertText;
mycmd.Parameters.AddWithValue("@Type1", (int)RemoteServerType.Once);
mycmd.Parameters.AddWithValue("@Type2", (int)RemoteServerType.OneMonth);
mycmd.Parameters.AddWithValue("@Type3", (int)RemoteServerType.ThreeMonth);
mycmd.Parameters.AddWithValue("@Type4", (int)RemoteServerType.OneYear);
mycmd.Parameters.AddWithValue("@Money1", 50);
mycmd.Parameters.AddWithValue("@Money2", 300);
mycmd.Parameters.AddWithValue("@Money3", 2000);
mycmd.Parameters.AddWithValue("@Money4", 5000);
mycmd.Parameters.AddWithValue("@ShopName1", "远程协助服务一次");
mycmd.Parameters.AddWithValue("@ShopName2", "远程协助服务一月");
mycmd.Parameters.AddWithValue("@ShopName3", "远程协助服务一季度");
mycmd.Parameters.AddWithValue("@ShopName4", "远程协助服务一年");
mycmd.Parameters.AddWithValue("@Description1", "远程协助服务一次");
mycmd.Parameters.AddWithValue("@Description2", "远程协助服务一月");
mycmd.Parameters.AddWithValue("@Description3", "远程协助服务一季度");
mycmd.Parameters.AddWithValue("@Description4", "远程协助服务一年");
mycmd.ExecuteNonQuery();
mycmd.CommandText = sqlText;
adapter = new MySqlDataAdapter(mycmd);
table = new DataTable();
adapter.Fill(table);
items = MetaDBAdapter <UserRemoteServerItem> .GetUserRemoteServerItemFromDataTable(table);
table.Clear();
table.Dispose();
adapter.Dispose();
}
});
return(items);
}
public MinesBuyRecord GetMineTradeRecord(string userName, string orderNumber)
{
MySqlConnection myconn = MyDBHelper.Instance.CreateConnection();
MySqlCommand mycmd = null;
try
{
DataTable table = new DataTable();
myconn.Open();
mycmd = myconn.CreateCommand();
string cmdText = "select a.*, b.UserName from minesbuyrecord a left join playersimpleinfo b on a.UserID=b.id ";
StringBuilder builder = new StringBuilder();
if (!string.IsNullOrEmpty(orderNumber))
{
builder.Append(" a.OrderNumber = @OrderNumber ");
mycmd.Parameters.AddWithValue("@OrderNumber", orderNumber);
}
if (!string.IsNullOrEmpty(userName))
{
builder.Append(" b.UserName = @UserName ");
mycmd.Parameters.AddWithValue("@UserName", DESEncrypt.EncryptDES(userName));
}
mycmd.CommandText = cmdText;
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(table);
var lists = MetaDBAdapter <MinesBuyRecord> .GetMinesBuyRecordFromDataTable(table);
table.Clear();
table.Dispose();
adapter.Dispose();
if (lists == null || lists.Length == 0)
{
return(null);
}
return(lists[0]);
}
finally
{
if (mycmd != null)
{
mycmd.Dispose();
}
if (myconn != null)
{
myconn.Close();
myconn.Dispose();
}
}
}
public AgentUserInfo[] GetAllAgentUsers()
{
MySqlConnection myconn = null;
MySqlCommand mycmd = null;
try
{
myconn = MyDBHelper.Instance.CreateConnection();
string sqlTextA = "select * from agentuserinfo; ";
mycmd = myconn.CreateCommand();
mycmd.CommandText = sqlTextA;
myconn.Open();
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
DataTable tableAgents = new DataTable();
adapter.Fill(tableAgents);
if (tableAgents.Rows.Count == 0)
{
return(null);
}
DataTable tablePlayers = new DataTable();
string sqlTextB = "select a.*, c.UserName as ReferrerUserName, b.* , g.Gravel, g.FirstGetGravelTime from playersimpleinfo a left join playersimpleinfo c on a.ReferrerUserID = c.id left join playerfortuneinfo b on a.id = b.userId left join playergravelinfo g on a.id = g.UserID where a.GroupType = @GroupType";
mycmd = new MySqlCommand(sqlTextB, myconn);
mycmd.Parameters.AddWithValue("@GroupType", (int)PlayerGroupType.AgentPlayer);
adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(tablePlayers);
var players = MetaDBAdapter <PlayerInfo> .GetPlayerInfoFromDataTable(tablePlayers);
if (players == null)
{
return(null);
}
var lists = MetaDBAdapter <AgentUserInfo> .GetAgentUserInfoFromDataTable(tableAgents, players);
tablePlayers.Clear();
tablePlayers.Dispose();
adapter.Dispose();
return(lists);
}
finally
{
if (mycmd != null)
{
mycmd.Dispose();
}
if (myconn != null)
{
myconn.Close();
myconn.Dispose();
}
}
}
private void SumUserAccountStoneStackCount(PlayerStoneFactoryAccountInfo account, MySqlCommand mycmd)
{
string sqlText = "select * from stonefactorystackchangerecord where `UserID`=@UserID ";
mycmd.CommandText = sqlText;
mycmd.Parameters.Clear();
mycmd.Parameters.AddWithValue("@UserID", account.UserID);
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(table);
StoneFactoryStackChangeRecord[] items = MetaDBAdapter <StoneFactoryStackChangeRecord> .GetStoneFactoryStackChangeRecordItemFromDataTable(table);
table.Dispose();
adapter.Dispose();
int sumEnableStoneStack = 0;
int sumFreezingStoneStack = 0;
int sumWithdrawableStoneStack = 0;
int sumWithdrawedStoneStack = 0;
if (items != null && items.Length != 0)
{
DateTime timeNow = DateTime.Now;
foreach (var item in items)
{
if ((timeNow.Date - item.Time.ToDateTime().Date).Days >= StoneFactoryConfig.StoneFactoryStoneFreezingDays)
{
//可用矿石
sumEnableStoneStack += item.JoinStoneStackCount;
}
else
{
sumFreezingStoneStack += item.JoinStoneStackCount;
}
if (item.JoinStoneStackCount > 0 && (timeNow.Date - item.Time.ToDateTime().Date).Days >= StoneFactoryConfig.StoneStackWithdrawLimitDays)
{
//可提现的矿石（没有减去已经提走的灵币）
sumWithdrawableStoneStack += item.JoinStoneStackCount;
}
if (item.JoinStoneStackCount < 0)
{
//已提取的灵币
sumWithdrawedStoneStack += item.JoinStoneStackCount;
}
}
}
account.TotalStackCount = sumEnableStoneStack;
account.WithdrawableStackCount = sumWithdrawableStoneStack + sumWithdrawedStoneStack;
account.FreezingStackCount = sumFreezingStoneStack;
}
public PlayerRaiderRoundHistoryRecordInfo[] GetPlayerRaiderRoundHistoryRecordInfo(int userID, int pageItemCount, int pageIndex)
{
MySqlConnection myconn = null;
MySqlCommand mycmd = null;
try
{
myconn = MyDBHelper.Instance.CreateConnection();
string sqlOrderLimit = " order by b.RaiderRoundID desc ";
if (pageItemCount > 0)
{
int start = pageIndex <= 0 ? 0 : (pageIndex - 1) * pageItemCount;
sqlOrderLimit += " limit " + start.ToString() + ", " + pageItemCount;
}
string sqlTextA = "select ttt.*, r.* from " +
" (SELECT b.RaiderRoundID, sum(b.BetStones) as AllBetStones " +
" FROM raiderplayerbetinfo b " +
" where b.UserID = @UserID " +
" group by b.RaiderRoundID " + sqlOrderLimit + " ) ttt " +
" left join raiderroundmetadatainfo r on ttt.RaiderRoundID = r.id ";
mycmd = myconn.CreateCommand();
mycmd.CommandText = sqlTextA;
mycmd.Parameters.AddWithValue("@UserID", userID);
myconn.Open();
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(table);
var lists = MetaDBAdapter <PlayerRaiderRoundHistoryRecordInfo> .GetPlayerRaiderRoundHistoryRecordInfoFromDataTable(table);
table.Clear();
table.Dispose();
adapter.Dispose();
return(lists);
}
finally
{
if (mycmd != null)
{
mycmd.Dispose();
}
if (myconn != null)
{
myconn.Close();
myconn.Dispose();
}
}
}
public UserRemoteServerBuyRecord GetUserLastBuyOnceRemoteServiceRecord(string playerUserName)
{
UserRemoteServerBuyRecord[] records = null;
MySqlConnection myconn = null;
try
{
DataTable dt = new DataTable();
myconn = MyDBHelper.Instance.CreateConnection();
myconn.Open();
MySqlCommand mycmd = myconn.CreateCommand();
string sqlTextA = "select a.* from userremoteserverbuyrecord a " +
" where a.UserID = ( select id from playersimpleinfo where UserName = @UserName ) " +
" and a.`ServerType`=@ServerType order by a.`ID` desc limit 1 ";
string encryptUserName = DESEncrypt.EncryptDES(playerUserName);
mycmd.Parameters.AddWithValue("@UserName", encryptUserName);
mycmd.Parameters.AddWithValue("@ServerType", (int)RemoteServerType.Once);
string sqlAllText = "select ttt.*, s.UserName as UserName from " +
" ( " + sqlTextA +
" ) ttt " +
" left join playersimpleinfo s on ttt.UserID = s.id ";
mycmd.CommandText = sqlAllText;
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(dt);
if (dt != null || dt.Rows.Count != 0)
{
records = MetaDBAdapter <UserRemoteServerBuyRecord> .GetUserRemoteServerBuyRecordListFromDataTable(dt);
}
dt.Clear();
dt.Dispose();
adapter.Dispose();
mycmd.Dispose();
if (records == null || records.Length == 0)
{
return(null);
}
return(records[0]);
}
finally
{
MyDBHelper.Instance.DisposeConnection(myconn);
}
}
public WithdrawRMBRecord GetWithdrawRMBRecord(int state, string playerUserName, int withdrawRMB, DateTime createTime)
{
WithdrawRMBRecord record = null;
MySqlConnection myconn = null;
try
{
DataTable dt = new DataTable();
myconn = MyDBHelper.Instance.CreateConnection();
myconn.Open();
MySqlCommand mycmd = myconn.CreateCommand();
string sqlTextA = "select ttt.*, s.UserName as PlayerUserName from " +
" (SELECT w.* " +
" FROM withdrawrmbrecord w " +
" where w.PlayerUserID = (select id from playersimpleinfo where UserName=@UserName ) and w.RMBWithdrawState = @RMBWithdrawState and w.WidthdrawRMB = @WidthdrawRMB and w.CreateTime = @CreateTime " +
" ) ttt " +
" left join playersimpleinfo s on ttt.PlayerUserID = s.id ";
string encrypedUserName = DESEncrypt.EncryptDES(playerUserName);
mycmd.Parameters.AddWithValue("@UserName", encrypedUserName);
mycmd.Parameters.AddWithValue("@RMBWithdrawState", state);
mycmd.Parameters.AddWithValue("@WidthdrawRMB", withdrawRMB);
mycmd.Parameters.AddWithValue("@CreateTime", createTime);
mycmd.CommandText = sqlTextA;
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(dt);
if (dt != null)
{
var records = MetaDBAdapter <WithdrawRMBRecord> .GetWithdrawRMBRecordListFromDataTable(dt);
if (records != null && records.Length != 0)
{
record = records[0];
}
}
dt.Clear();
dt.Dispose();
adapter.Dispose();
mycmd.Dispose();
return(record);
}
finally
{
MyDBHelper.Instance.DisposeConnection(myconn);
}
}
public RaiderRoundMetaDataInfo AddNewRaiderRoundMetaDataInfo(RaiderRoundMetaDataInfo roundInfo)
{
MySqlConnection myconn = null;
MySqlCommand mycmd = null;
try
{
myconn = MyDBHelper.Instance.CreateConnection();
myconn.Open();
//1. Save to DB
string sqlTextA = "insert into raiderroundmetadatainfo (`State`) values ( @State) ;";
mycmd = myconn.CreateCommand();
mycmd.CommandText = sqlTextA;
mycmd.Parameters.AddWithValue("@State", (int)roundInfo.State);
mycmd.ExecuteNonQuery();
mycmd.Dispose();
//2. Select from DB
string sqlTextB = "SELECT * FROM raiderroundmetadatainfo order by id desc limit 1; ";
mycmd = myconn.CreateCommand();
mycmd.CommandText = sqlTextB;
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(table);
var roundInfos = MetaDBAdapter <RaiderRoundMetaDataInfo> .GetRaiderRoundMetaDataInfoFromDataTable(table);
table.Clear();
table.Dispose();
adapter.Dispose();
if (roundInfos == null || roundInfos.Length == 0)
{
return(null);
}
return(roundInfos[0]);
}
finally
{
if (mycmd != null)
{
mycmd.Dispose();
}
MyDBHelper.Instance.DisposeConnection(myconn);
}
}
public OldPlayerTransferRegisterInfo[] GetAllPlayerTransferRecords()
{
OldPlayerTransferRegisterInfo[] records = null;
MyDBHelper.Instance.ConnectionCommandSelect(mycmd =>
{
string sqlText = "select * from oldplayertransferregisterinfo ";
mycmd.CommandText = sqlText;
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(table);
records = MetaDBAdapter <OldPlayerTransferRegisterInfo> .GetOldPlayerTransferRegisterInfo(table);
});
return(records);
}
public RaiderRoundMetaDataInfo[] GetHistoryRaiderRoundRecords(int pageItemCount, int pageIndex)
{
MySqlConnection myconn = null;
MySqlCommand mycmd = null;
try
{
myconn = MyDBHelper.Instance.CreateConnection();
string sqlTextA = "SELECT * FROM raiderroundmetadatainfo where State = @State ";
string sqlOrderLimit = " order by id desc ";
if (pageItemCount > 0)
{
int start = pageIndex <= 0 ? 0 : (pageIndex - 1) * pageItemCount;
sqlOrderLimit += " limit " + start.ToString() + ", " + pageItemCount;
}
string sqlAllText = sqlTextA + sqlOrderLimit;
mycmd = myconn.CreateCommand();
mycmd.CommandText = sqlAllText;
mycmd.Parameters.AddWithValue("@State", (int)RaiderRoundState.Finished);
myconn.Open();
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(table);
var lists = MetaDBAdapter <RaiderRoundMetaDataInfo> .GetRaiderRoundMetaDataInfoFromDataTable(table);
table.Clear();
table.Dispose();
adapter.Dispose();
return(lists);
}
finally
{
if (mycmd != null)
{
mycmd.Dispose();
}
if (myconn != null)
{
myconn.Close();
myconn.Dispose();
}
}
}
/// <summary>
/// 没有填充AwardItem属性
/// </summary>
/// <returns></returns>
public RouletteWinnerRecord[] GetNotPayWinAwardRecords()
{
RouletteWinnerRecord[] records = null;
MySqlConnection myconn = null;
MySqlCommand mycmd = null;
try
{
myconn = MyDBHelper.Instance.CreateConnection();
myconn.Open();
string sqlText = "select r.*, m.AwardName, m.RouletteAwardType " +
" from roulettewinnerrecord r " +
" left join rouletteawarditem m on r.AwardItemID = m.id " +
" where m.RouletteAwardType = @RouletteAwardType and r.IsPay = @IsPay ";
string sqlAllText = "select ttt.*, s.UserName as UserName from " +
" ( " + sqlText +
" ) ttt " +
" left join playersimpleinfo s on ttt.UserID = s.id ";
mycmd = myconn.CreateCommand();
mycmd.CommandText = sqlAllText;
mycmd.Parameters.AddWithValue("@RouletteAwardType", RouletteAwardType.RealAward);
mycmd.Parameters.AddWithValue("@IsPay", false);
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
DataTable table = new DataTable();
adapter.Fill(table);
records = MetaDBAdapter <RouletteWinnerRecord> .GetRouletteWinnerRecordFromDataTable(table);
table.Clear();
table.Dispose();
adapter.Dispose();
return(records);
}
finally
{
if (mycmd != null)
{
mycmd.Dispose();
}
if (myconn != null)
{
myconn.Close();
myconn.Dispose();
}
}
}
public PlayerGravelRequsetRecordInfo[] GetLastDayPlayerGravelRequsetRecords(MyDateTime date, int userID)
{
MySqlConnection myconn = null;
MySqlCommand mycmd = null;
try
{
myconn = MyDBHelper.Instance.CreateConnection();
mycmd = myconn.CreateCommand();
string sqlInnerSelect = " select * from playergravelrequsetrecordinfo ";
string sqlWhere = " where ";
if (userID > 0)
{
sqlWhere += " UserID=@userID and ";
}
sqlWhere += " @beginDate <= RequestDate and RequestDate < @endDate ";
string sqlText = "SELECT r.*, s.UserName FROM (" + sqlInnerSelect + sqlWhere + ") r left join playersimpleinfo s on r.UserID = s.id ";
mycmd.CommandText = sqlText;
mycmd.Parameters.AddWithValue("@beginDate", new DateTime(date.Year, date.Month, date.Day, 0, 0, 0));
mycmd.Parameters.AddWithValue("@endDate", new DateTime(date.Year, date.Month, date.Day + 1, 0, 0, 0));
mycmd.Parameters.AddWithValue("@userID", userID);
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(table);
var lists = MetaDBAdapter <PlayerGravelRequsetRecordInfo> .GetPlayerGravelRequsetRecordInfoFromDataTable(table);
table.Clear();
table.Dispose();
adapter.Dispose();
return(lists);
}
finally
{
if (mycmd != null)
{
mycmd.Dispose();
}
if (myconn != null)
{
myconn.Close();
myconn.Dispose();
}
}
}
public WithdrawRMBRecord GetWithdrawRMBRecordByID(int id)
{
WithdrawRMBRecord record = null;
MySqlConnection myconn = null;
try
{
DataTable dt = new DataTable();
myconn = MyDBHelper.Instance.CreateConnection();
myconn.Open();
MySqlCommand mycmd = myconn.CreateCommand();
string sqlTextA = "select ttt.*, s.UserName as PlayerUserName from " +
" (SELECT w.* " +
" FROM withdrawrmbrecord w " +
" where w.id = @id " +
" ) ttt " +
" left join playersimpleinfo s on ttt.PlayerUserID = s.id ";
mycmd.Parameters.AddWithValue("@id", id);
mycmd.CommandText = sqlTextA;
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(dt);
if (dt != null)
{
var records = MetaDBAdapter <WithdrawRMBRecord> .GetWithdrawRMBRecordListFromDataTable(dt);
if (records != null && records.Length != 0)
{
record = records[0];
}
}
dt.Clear();
dt.Dispose();
adapter.Dispose();
mycmd.Dispose();
return(record);
}
finally
{
MyDBHelper.Instance.DisposeConnection(myconn);
}
}
public PlayerGravelRequsetRecordInfo GetLastDayPlayerGravelRequsetRecord(int userID)
{
MySqlConnection myconn = null;
MySqlCommand mycmd = null;
try
{
myconn = MyDBHelper.Instance.CreateConnection();
mycmd = myconn.CreateCommand();
string sqlInnerSelect = " select * from playergravelrequsetrecordinfo where UserID=@userID order by id desc limit 1 ";
string sqlText = "SELECT r.*, s.UserName FROM (" + sqlInnerSelect + ") r left join playersimpleinfo s on r.UserID = s.id ";
mycmd.CommandText = sqlText;
mycmd.Parameters.AddWithValue("@userID", userID);
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(table);
var records = MetaDBAdapter <PlayerGravelRequsetRecordInfo> .GetPlayerGravelRequsetRecordInfoFromDataTable(table);
table.Clear();
table.Dispose();
adapter.Dispose();
if (records == null || records.Length == 0)
{
return(null);
}
return(records[0]);
}
finally
{
if (mycmd != null)
{
mycmd.Dispose();
}
if (myconn != null)
{
myconn.Close();
myconn.Dispose();
}
}
}
public RaiderRoundMetaDataInfo GetLastRaiderRoundMetaDataInfo()
{
MySqlConnection myconn = null;
MySqlCommand mycmd = null;
try
{
myconn = MyDBHelper.Instance.CreateConnection();
string sqlText = "SELECT * FROM raiderroundmetadatainfo order by id desc limit 1; ";
mycmd = myconn.CreateCommand();
mycmd.CommandText = sqlText;
myconn.Open();
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(table);
var roundInfos = MetaDBAdapter <RaiderRoundMetaDataInfo> .GetRaiderRoundMetaDataInfoFromDataTable(table);
table.Clear();
table.Dispose();
adapter.Dispose();
if (roundInfos == null || roundInfos.Length == 0)
{
return(null);
}
return(roundInfos[0]);
}
finally
{
if (mycmd != null)
{
mycmd.Dispose();
}
if (myconn != null)
{
myconn.Close();
myconn.Dispose();
}
}
}
public TestUserLogState GetTestUserLogStateByMac(string mac)
{
MySqlConnection myconn = MyDBHelper.Instance.CreateConnection();
MySqlCommand mycmd = null;
try
{
DataTable table = new DataTable();
myconn.Open();
mycmd = myconn.CreateCommand();
string cmdText = "select * from testuserlogstate where Mac = @Mac ";
mycmd.Parameters.AddWithValue("@Mac", mac);
mycmd.CommandText = cmdText;
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(table);
var lists = MetaDBAdapter <TestUserLogState> .GetTestUserLogStateFromDataTable(table);
table.Clear();
table.Dispose();
adapter.Dispose();
if (lists == null || lists.Length == 0)
{
return(null);
}
return(lists[0]);
}
finally
{
if (mycmd != null)
{
mycmd.Dispose();
}
if (myconn != null)
{
myconn.Close();
myconn.Dispose();
}
}
}
public PlayerLockedInfo GetPlayerLockedInfo(int userID)
{
MySqlConnection myconn = null;
MySqlCommand mycmd = null;
try
{
myconn = MyDBHelper.Instance.CreateConnection();
mycmd = myconn.CreateCommand();
string sqlText = "select * from playerlockedinfo where `UserID` = @UserID;";
mycmd.CommandText = sqlText;
mycmd.Parameters.AddWithValue("@UserID", userID);
myconn.Open();
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(table);
var items = MetaDBAdapter <PlayerLockedInfo> .GetPlayerLockedInfoFromDataTable(table);
table.Clear();
table.Dispose();
adapter.Dispose();
if (items != null && items.Length > 0)
{
return(items[0]);
}
return(null);
}
finally
{
if (mycmd != null)
{
mycmd.Dispose();
}
if (myconn != null)
{
myconn.Close();
myconn.Dispose();
}
}
}
public AdminInfo GetAdmin(string userName)
{
AdminInfo admin = null;
MySqlConnection myconn = null;
MySqlCommand mycmd = null;
try
{
DataTable dt = new DataTable();
myconn = MyDBHelper.Instance.CreateConnection();
myconn.Open();
string cmdText = "select * from AdminInfo where `UserName` = @UserName";
mycmd = new MySqlCommand(cmdText, myconn);
mycmd.Parameters.AddWithValue("@UserName", DESEncrypt.EncryptDES(userName));
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(dt);
if (dt.Rows.Count == 0)
{
return(null);
}
admin = MetaDBAdapter <AdminInfo> .GetAdminInfoListFromDataTable(dt)[0];
dt.Clear();
dt.Dispose();
adapter.Dispose();
return(admin);
}
catch (Exception exc)
{
throw exc;
}
finally
{
MyDBHelper.Instance.DisposeConnection(myconn);
if (mycmd != null)
{
mycmd.Dispose();
}
}
}
public RouletteRoundInfo GetLastRouletteRoundInfo()
{
MySqlConnection myconn = null;
MySqlCommand mycmd = null;
try
{
myconn = MyDBHelper.Instance.CreateConnection();
myconn.Open();
string sqlText = "select * from rouletteroundinfo order by id desc limit 1 ;";
mycmd = myconn.CreateCommand();
mycmd.CommandText = sqlText;
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
DataTable table = new DataTable();
adapter.Fill(table);
var items = MetaDBAdapter <RouletteRoundInfo> .GetRouletteRoundInfoFromDataTable(table);
table.Clear();
table.Dispose();
adapter.Dispose();
if (items == null || items.Length == 0)
{
return(null);
}
return(items[0]);
}
finally
{
if (mycmd != null)
{
mycmd.Dispose();
}
if (myconn != null)
{
myconn.Close();
myconn.Dispose();
}
}
}
public AlipayRechargeRecord SearchExceptionAlipayRechargeRecord(string orderNumber)
{
AlipayRechargeRecord[] records = null;
MySqlConnection myconn = null;
MySqlCommand mycmd = null;
try
{
DataTable dt = new DataTable();
myconn = MyDBHelper.Instance.CreateConnection();
myconn.Open();
string cmdText = "select * from alipayrecharge_exception_record where out_trade_no = @out_trade_no ";
mycmd = new MySqlCommand(cmdText, myconn);
mycmd.Parameters.AddWithValue("@out_trade_no", orderNumber);
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(dt);
records = MetaDBAdapter <AlipayRechargeRecord> .GetAlipayRechargeRecordListFromDataTable(dt);
dt.Clear();
dt.Dispose();
adapter.Dispose();
if (records == null || records.Length == 0)
{
return(null);
}
return(records[0]);
}
catch (Exception exc)
{
throw exc;
}
finally
{
if (mycmd != null)
{
mycmd.Dispose();
}
MyDBHelper.Instance.DisposeConnection(myconn);
}
}
