public DataTable getGameNameList(string gameName, string typeName, string limit, string page)
{
DataTable dt = new DataTable();
try
{
string sql = string.Format("SELECT gamename.id,gameName,typeName from gamename INNER JOIN type on gamename.gameType=type.ID where 1=1 ");
if (!string.IsNullOrEmpty(gameName))
{
sql += string.Format(" and gameName='{0}'", gameName);
}
if (!string.IsNullOrEmpty(typeName))
{
sql += string.Format(" and typeName='{0}'", typeName);
}
int offset = (Convert.ToInt32(page) - 1) * Convert.ToInt32(limit);
sql += string.Format(" limit {0},{1}", offset, limit);
dt = MySqlHelper.GetDataTable(CommandType.Text, sql);
return(dt);
}
catch
{
return(dt);
}
}
public DataTable getMachine(string macNum, string address, string limit, string page)
{
DataTable dt = new DataTable();
try
{
string sql = string.Format("SELECT id,machineNum,address,date_format(createtime, '%Y-%m-%d %H:%i:%s')time,state,description,biaoNum from machine where 1=1 ");
if (!string.IsNullOrEmpty(macNum))
{
sql += string.Format(" machineNum='{0}'", macNum);
}
if (!string.IsNullOrEmpty(address))
{
sql += string.Format(" and address like '%{0}%'", macNum);
}
if (!string.IsNullOrEmpty(limit) && !string.IsNullOrEmpty(page))
{
int offset = (Convert.ToInt32(page) - 1) * Convert.ToInt32(limit);
sql += string.Format(" limit {0},{1}", offset, limit);
}
dt = MySqlHelper.GetDataTable(CommandType.Text, sql);
return(dt);
}
catch
{
return(dt);
}
}
public DataTable getUserList(string id, string username, string limit, string page)
{
DataTable dt = new DataTable();
try
{
string sql = string.Format("SELECT id,username,date_format(datetime, '%Y-%m-%d %H:%i:%s')time,flag FROM `users` where 1=1 ");
if (!string.IsNullOrEmpty(id))
{
sql += string.Format(" and id={0}", id);
}
if (!string.IsNullOrEmpty(username))
{
sql += string.Format(" and username='******'", username);
}
int offset = (Convert.ToInt32(page) - 1) * Convert.ToInt32(limit);
sql += string.Format(" limit {0},{1}", offset, limit);
dt = MySqlHelper.GetDataTable(CommandType.Text, sql);
return(dt);
}
catch
{
return(dt);
}
}
public DataTable dtTypebyid(string id)
{
string sql = string.Format("select * from `gamename` where gameType = " + id);
DataTable count = MySqlHelper.GetDataTable(CommandType.Text, sql);
return(count);
}
public DataTable getChooseList(string gamenameid, string flag, string username, string page, string limit)
{
DataTable dt = new DataTable();
try
{
string sql = string.Format("SELECT cg.id,userid,gameName,cg.flag,users.username,users.nickname,date_format(cg.datetime, '%Y-%m-%d %H:%i:%s')time,mac.machineNum,mac.address,IFNULL(cg.gameResult,2) gameResult FROM `userchoosegame` cg inner JOIN gamename gn on cg.gameid=gn.id INNER JOIN users on cg.userid=users.id INNER JOIN machine mac on mac.id=cg.machineid where 1=1 ");
if (!string.IsNullOrEmpty(gamenameid))
{
sql += string.Format(" AND cg.gameid={0}", gamenameid);
}
if (!string.IsNullOrEmpty(flag))
{
sql += string.Format(" AND cg.flag={0}", flag);
}
if (!string.IsNullOrEmpty(username))
{
sql += string.Format(" AND users.username='******'", username);
}
sql += " ORDER BY cg.id DESC";
int offset = (Convert.ToInt32(page) - 1) * Convert.ToInt32(limit);
sql += string.Format(" limit {0},{1}", offset, limit);
dt = MySqlHelper.GetDataTable(CommandType.Text, sql);
return(dt);
}
catch
{
return(dt);
}
}
public DataTable dtType()
{
string sql = string.Format("select * from `type`");
DataTable count = MySqlHelper.GetDataTable(CommandType.Text, sql);
return(count);
}
public DataTable getDebugInfo(string macNum)
{
string sql = string.Format("SELECT de.command,de.id from debug_command de INNER JOIN machine mac on de.macid=mac.id where de.flag=0 and mac.machineNum='{0}'", macNum);
DataTable dt = MySqlHelper.GetDataTable(CommandType.Text, sql);
if (dt.Rows.Count > 0)
{
return(dt);
}
else
{
return(null);
}
}
public DataTable getDeposit()
{
string sql = string.Format("SELECT biaoMoney,gameMoney from game_setting ORDER BY id desc LIMIT 1");
DataTable dt = MySqlHelper.GetDataTable(CommandType.Text, sql);
if (dt.Rows.Count > 0)
{
return(dt);
}
else
{
return(null);
}
}
public DataTable getUserRefundState(string username)
{
string sql = string.Format("SELECT IFNULL(payState,-1) payState,IFNULL(refundState,-1) refundState,choose.flag,IFNULL(backBiaoNum,0)backBiaoNum,IFNULL(biaoState,0)biaoState from userchoosegame choose INNER JOIN users u on choose.userid=u.id where (IFNULL(payState,-1)<>0 or IFNULL(refundState,-1)<>0 ) and username='******' ORDER BY choose.id desc", username);
DataTable dt = MySqlHelper.GetDataTable(CommandType.Text, sql);
if (dt.Rows.Count > 0)
{
return(dt);
}
else
{
return(null);
}
}
/// <summary>
/// 开始游戏的方法
/// </summary>
/// <param name="ids"></param>
/// <param name="openid"></param>
/// <returns></returns>
public int starGame(string ids, string openid)
{
try
{
//检查用户选择的游戏是否是同一种游戏，并且必须选择了自己
//string selGameTypeSql = string.Format("SELECT gameid FROM `userchoosegame` WHERE id in({0}) and flag=0 GROUP BY gameid ", userIds);
string selGameTypeSql = string.Format("SELECT gameid,gn.peopleNum num FROM `userchoosegame` game INNER JOIN users u on game.userid=u.id INNER JOIN gamename gn ON game.gameid=gn.id WHERE game.id in({0}) and game.flag=0 and u.username='******' GROUP BY game.gameid ", ids, openid);
DataTable dtType = MySqlHelper.GetDataTable(CommandType.Text, selGameTypeSql);
if (dtType.Rows.Count != 1)
{
return(-2);
}
int chooseCount = Convert.ToInt32(dtType.Rows[0]["num"]);
string sqlCount = string.Format("select count(*) from userchoosegame where id in({0})", ids);
int count = Convert.ToInt32(MySqlHelper.ExecuteScalar(CommandType.Text, sqlCount));
if (chooseCount != count)
{
return(-2);
}
//用户选择的机器只能在一台或两台机上
string selectSql = string.Format("SELECT gameid FROM `userchoosegame` WHERE id in({0}) and flag=0 GROUP BY gameid,machineid ", ids);
DataTable dt = MySqlHelper.GetDataTable(CommandType.Text, selectSql);
if (dt.Rows.Count != 1 && dt.Rows.Count != 2)
{
return(-2);
}
//如果是联网类的游戏，必须是两台机
string gameid = dt.Rows[0][0].ToString();
if (gameid != "6" && gameid != "7" && gameid != "8" && gameid != "9")
{
if (dt.Rows.Count == 2)
{
return(-2);
}
}
string sql = string.Format("UPDATE userchoosegame set isadmin=0 where id in({2}) and userid=(SELECT id from users where username='******');update userchoosegame set flag=0 where id in({2});INSERT into gameresult(gameid,juid,flag,datetime) SELECT {0},MAX(juid)+1,0,'{1}' from gameresult", gameid, DateTime.Now, ids, openid);
int result = MySqlHelper.ExecuteNonQuery(CommandType.Text, sql);
return(result);
}
catch
{
return(-1);
}
}
public DataTable selectBiaoNum(string gameid, string juid)
{
DataTable dt = new DataTable();
try
{
string sql = string.Format("SELECT userid,biaoNum from matchinfo where gameid={0} and juid={1} ORDER BY id desc limit 1", gameid, juid);
dt = MySqlHelper.GetDataTable(CommandType.Text, sql);
return(dt);
}
catch
{
return(dt);
}
}
public DataTable select01GameScore(string gameid, string juid)
{
DataTable dt = new DataTable();
try
{
string sql = string.Format("SELECT IFNULL(SUM(score),0) score,userid FROM `matchInfo` where gameid={0} and juid={1} and flag=0 GROUP BY userid", gameid, juid);
dt = MySqlHelper.GetDataTable(CommandType.Text, sql);
return(dt);
}
catch
{
return(dt);
}
}
public DataTable selectStarInfo(string machineNum)
{
DataTable dt = new DataTable();
try
{
string sql = string.Format("SELECT gameid,IFNULL(jiebiaoNum,0) jiebiao,playTimes,orderno from userchoosegame choose INNER JOIN machine ma on choose.machineid=ma.id where choose.flag=1 and ma.machineNum='{0}' and payState=0 LIMIT 1", machineNum);
dt = MySqlHelper.GetDataTable(CommandType.Text, sql);
return(dt);
}
catch
{
return(dt);
}
}
public DataTable getGameInfo(string orderno)
{
try
{
string sql = string.Format("SELECT borrowBiaoNum from userchoosegame where orderno='{0}'", orderno);
DataTable dt = MySqlHelper.GetDataTable(CommandType.Text, sql);
if (dt.Rows.Count > 0)
{
return(dt);
}
else
{
return(null);
}
}
catch (Exception ex)
{
Helper.makeLog(ex.ToString());
return(null);
}
}
public DataTable selectOnce01GameScore(string gameid, string juid)
{
DataTable dt = new DataTable();
try
{
string sql = string.Format("SELECT id,userid,score,beinum,realscore,lunNum,biaoNum FROM `matchInfo` where gameid={0} and juid={1} and flag=0 LIMIT 1", gameid, juid);
dt = MySqlHelper.GetDataTable(CommandType.Text, sql);
if (dt.Rows.Count > 0)
{
string id = dt.Rows[0]["id"].ToString();
sql = string.Format("update matchinfo set flag=1 where id={0}", id);
int result = MySqlHelper.ExecuteNonQuery(CommandType.Text, sql);
}
return(dt);
}
catch
{
return(dt);
}
}
public DataTable getPayList(string nickname, string limit, string page)
{
string sql = string.Format("SELECT pay.id,date_format(pay.datetime, '%Y-%m-%d %H:%i:%s')time,pay.price,choose.orderno,u.nickname,gn.gameName,IFNULL(choose.refundState,1)refundState from pay_result pay INNER JOIN users u on pay.username=u.username INNER JOIN userchoosegame choose on pay.order_no=choose.orderno INNER JOIN gamename gn on choose.gameid=gn.id GROUP BY pay.order_no");
if (!string.IsNullOrEmpty(nickname))
{
sql += string.Format(" and u.nickname='{0}'", nickname);
}
int offset = (Convert.ToInt32(page) - 1) * Convert.ToInt32(limit);
sql += string.Format(" limit {0},{1}", offset, limit);
DataTable dt = MySqlHelper.GetDataTable(CommandType.Text, sql);
if (dt.Rows.Count > 0)
{
return(dt);
}
else
{
return(null);
}
}
/// <summary>
///
/// </summary>
/// <param name="backBiaoNum"></param>
/// <param name="payno"></param>
/// <returns></returns>
public refundInfo getRefundInfo(string backBiaoNum, string payno)
{
try
{
//在这个地方处理退款
string selectNo = string.Format("SELECT borrowBiaoNum,choose.orderno,result.price from userchoosegame choose INNER JOIN pay_result result on result.order_no=choose.orderno where choose.orderno='{0}' GROUP BY choose.orderno", payno);
makeLog("getRefundInfo:" + selectNo);
DataTable dt = MySqlHelper.GetDataTable(CommandType.Text, selectNo);
string orderno = "";
string totalPrice = "";
int borrowNum = 0;
if (dt != null && dt.Rows.Count > 0)
{
orderno = dt.Rows[0]["orderno"].ToString();
totalPrice = dt.Rows[0]["price"].ToString();
if (dt.Rows[0]["borrowBiaoNum"] != DBNull.Value)
{
borrowNum = Convert.ToInt32(dt.Rows[0]["borrowBiaoNum"]);
}
}
if (borrowNum == 0)
{
borrowNum = Convert.ToInt32(backBiaoNum);
}
if (borrowNum == 0)
{
borrowNum = 1;
backBiaoNum = "1";
}
//计算退款的金额
DataTable dtMoney = getDeposit();
int sinlgeBiaoMoney = Convert.ToInt32(dtMoney.Rows[0]["biaoMoney"]);
//int money = sinlgeBiaoMoney * (borrowNum - Convert.ToInt32(backBiaoNum)); //押金按比例退
//if (money > sinlgeBiaoMoney * borrowNum)
//{
// money = sinlgeBiaoMoney * borrowNum;
//}
int money = sinlgeBiaoMoney * (Convert.ToInt32(backBiaoNum)); //押金按比例退
if (money > sinlgeBiaoMoney * borrowNum)
{
money = sinlgeBiaoMoney * borrowNum;
}
money = (money > 0) ? money : 0;
//money = deposit * Convert.ToInt32(backBiaoNum) / borrowNum;
makeLog("订单号：" + orderno + "总额：" + totalPrice + "还了：" + backBiaoNum + "借了：" + borrowNum + "退还金额：" + money);
if (!string.IsNullOrEmpty(orderno))
{
refundInfo info = new refundInfo();
info.orderno = orderno;
info.totalPrice = totalPrice;
info.refundPrice = money.ToString();
return(info);
}
else
{
return(null);
}
}
catch (Exception ex)
{
makeLog("getRefundInfo()异常：" + ex.ToString());
return(null);
}
}
