executeTable(string cmd,
MySqlParameter[] parameters = null)
{
DataTable result = null;
try
{
result = new DataTable();
MySqlDataReader reader = executeReader(cmd,
parameters);
result.Load(reader);
reader?.Close();
}
catch (Exception)
{
}
return(result);
}
public List <string> FetchDatabases()
{
VerifyConnection();
MySqlDataReader reader = null;
var result = new List <string>();
var cmd = new MySqlCommand("SHOW DATABASES", _connection);
try
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
result.Add(reader.GetString(0));
}
}
finally
{
reader?.Close();
}
return(result);
}
/// <summary>
/// Check if the Expense exists in DB
/// </summary>
/// <param name="id">ID of the Expense to be updated</param>
/// <returns>
/// <para>TRUE: Expense exists</para>
/// <para>FALSE: Expense does not exist</para>
/// </returns>
public static bool HasExpense(string id)
{
DbConnect db = new DbConnect();
// Check if connection is opened
if (!db.IsConnectionOpen())
{
return(false);
}
const string query = "SELECT id FROM despesas_isi.despesas WHERE id = @id;";
List <MySqlParameter> parameters = new List <MySqlParameter> {
new MySqlParameter("@id", id)
};
MySqlDataReader reader = db.ExecSqlWithData(query, parameters);
try
{
if (reader == null)
{
return(false);
}
if (reader.HasRows)
{
reader.Close();
return(true);
}
reader.Close();
return(false);
}
catch (Exception)
{
reader?.Close();
return(false);
}
}
public Dictionary <int, float> GetAllPrices()
{
Dictionary <int, float> result = new Dictionary <int, float>();
MySqlDataReader mysql_datareader = null;
MySqlCommand command = null;
try {
string query = "SELECT id, price FROM products";
command = m_dbConnection.CreateCommand();
command.Connection.Open();
command.CommandText = query;
mysql_datareader = command.ExecuteReader();
while (mysql_datareader.Read())
{
int id = mysql_datareader.GetInt32(0);
float price = mysql_datareader.GetFloat(1);
result[id] = price;
}
}
catch (Exception e) {
Console.Out.WriteLine("\n\n**********************************************************************");
Console.Out.WriteLine(e.Message);
Console.Out.WriteLine(e.InnerException);
Console.Out.WriteLine(e.Source);
Console.Out.WriteLine("**********************************************************************\n\n");
}
finally {
mysql_datareader?.Close();
command?.Connection?.Close();
}
return(result);
}
public override DbDataReader ExecuteReader(string sqlString, params DbParameter[] dbParameter)
{
MySqlConnection conn = new MySqlConnection();
conn.ConnectionString = ConnectionString;
MySqlCommand cmd = new MySqlCommand();
MySqlDataReader rdr = null;
try
{
//Prepare the command to execute
PrepareCommand(cmd, conn, sqlString, null, CommandType.Text, dbParameter);
rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return(rdr);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex);
rdr?.Close();
cmd.Dispose();
conn.Close();
}
return(null);
}
protected override void ProcessResultSet(MySqlDataReader reader)
{
try
{
while (reader.Read())
{
_dt.Rows.Add(reader.IsDBNull(0) ? 0 : reader.GetInt32(0),
reader.IsDBNull(1) ? "" : reader.GetString(1),
reader.IsDBNull(2) ? 0 : reader.GetInt32(2),
reader.IsDBNull(3) ? 0 : reader.GetInt32(3),
reader.IsDBNull(4) ? "" : reader.GetString(4),
reader.IsDBNull(5) ? "" : reader.GetString(5)
);
}
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
reader?.Close();
}
}
protected override void ProcessResultSet(MySqlDataReader reader)
{
try
{
while (reader.Read())
{
DataRow dr = _dt.NewRow();
dr["CheckDate"] = reader.IsDBNull(0) ? "" : reader.GetString(0);
dr["LocId"] = reader.IsDBNull(1) ? "" : reader.GetString(1);
dr["Plant"] = reader.IsDBNull(2) ? "" : reader.GetString(2);
dr["Stock_Id"] = reader.IsDBNull(3) ? "" : reader.GetString(3);
dr["Status"] = reader.IsDBNull(4) ? 0 : reader.GetInt32(4);
_dt.Rows.Add(dr);
}
}
catch (System.Exception ex)
{
throw new System.Exception(ex.Message);
}
finally
{
reader?.Close();
}
}
public string PageOpinionLoadData(string sType, int nItemNum)
{
string sResult = null;
string sdatas = null;
MySqlConnection conn = new MySqlConnection(sConn);
conn.Open();
string query = "SELECT * from dballwithme.opinioninfo ";
string qConditon = " where opiniontag = " + "'" + sType + "'";
query = query + qConditon;
MySqlCommand cmd = new MySqlCommand(query, conn);
MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
ArrayList dataarry = new ArrayList();
ArrayList Resultdataarry = new ArrayList();
try
{
if (reader.HasRows) //查询到结果
{
while (reader.Read())
{
string str = reader[1].ToString() + "#"; //title
str = str + reader[2].ToString() + "#"; //abstract
str = str + reader[3].ToString() + "#"; //img
str = str + reader[4].ToString() + "#"; //time
str = str + reader[0].ToString() + "$"; //Id
str = str + reader[9].ToString(); //content
dataarry.Add(str);
}
for (int i = nItemNum; i < dataarry.Count; i++)
{
if (i <= nItemNum + 2)
{
Resultdataarry.Add(dataarry[i]);
}
else
{
break;
}
}
if (Resultdataarry.Count <= 0)
{
sResult = "LoadAll!";
sdatas = JavaScriptConvert.SerializeObject(Resultdataarry);
}
else
{
sResult = "LoadOk!";
sdatas = JavaScriptConvert.SerializeObject(Resultdataarry);
}
}
}
catch (System.Exception ex)
{
reader.Close();
conn.Close();
}
reader.Close();
conn.Close();
return(JavaScriptConvert.SerializeObject(new JsonState {
sState = sResult, sdata = sdatas
}));
}
private void button1_Click(object sender, EventArgs e)
{
int a1 = int.Parse(skinTextBox3.Text);
if (skinTextBox1.Text == "")
{
MessageBox.Show("请输入设置的分");
}
else if (skinTextBox2.Text == "")
{
MessageBox.Show("请输入设置的秒");
}
else if (skinTextBox3.Text == "")
{
MessageBox.Show("请输入设置的毫秒");
}
else if (a1 % 100 != 0)
{
MessageBox.Show("请将毫秒设置为100的整数倍");
}
else
{
string sql4 = "select workid from work where workname =" + "'" + comboBox3.Text + "'";
string workbzid = "";
MySqlDataReader rd4 = msc.getDataFromTable(sql4);
while (rd4.Read())
{
workbzid = rd4["workid"].ToString();
}
rd4.Close();
msc.Close();
if (comboBox2.Text == "所有" && comboBox4.Text == "所有")
{
string ss = skinTextBox1.Text + "分" + skinTextBox2.Text + "秒" + skinTextBox3.Text + "毫秒";
//MessageBox.Show("skinTextBox2.Text是" + bz, "提示");
string sql = "update equipment set shangchuan = '" + ss + "' where workid = " + workbzid + "";
MySqlConn ms2 = new MySqlConn();
int n = ms2.nonSelect(sql);
ms2.Close();
string sql1 = "update dsensor set shangchuan = '" + ss + "' where workid = " + workbzid + "";
int m = ms2.nonSelect(sql1);
ms2.Close();
}
else if (comboBox1.Text == "所有" && comboBox4.Text != "所有")
{
string ss = skinTextBox1.Text + "分" + skinTextBox2.Text + "秒" + skinTextBox3.Text + "毫秒";
MySqlConn ms2 = new MySqlConn();
//MessageBox.Show("skinTextBox2.Text是" + bz, "提示");
string sql = "update equipment set shangchuan = '" + ss + "' where Eid =" + comboBox4.Text + " AND workid = " + workbzid + "";
int n = ms2.nonSelect(sql);
ms2.Close();
string sql1 = "update dsensor set shangchuan = '" + ss + "' where Eid =" + comboBox4.Text + " AND workid = " + workbzid + "";
int m = ms2.nonSelect(sql1);
ms2.Close();
}
else if (comboBox1.Text != "所有" && comboBox4.Text != "所有")
{
string ss = skinTextBox1.Text + "分" + skinTextBox2.Text + "秒" + skinTextBox3.Text + "毫秒";
//MessageBox.Show("skinTextBox2.Text是" + bz, "提示");
MySqlConn ms2 = new MySqlConn();
string sql1 = "update dsensor set shangchuan = '" + ss + "' where Eid =" + comboBox4.Text + " AND Sid =" + comboBox1.Text + " AND workid = " + workbzid + "";
int m = ms2.nonSelect(sql1);
ms2.Close();
}
string bz2 = Ten2Hex(workbzid);
if (bz2.Length == 2)
{
data = Ten2Hex(workbzid);
}
else
{
data = "0" + Ten2Hex(workbzid);
}
data = data + " 0B 00 06";
if (comboBox2.Text.Equals("所有"))
{
data = data + " " + "FF";
}
else if (comboBox2.Text.Equals("压力计"))
{
data = data + " " + "01";
}
else if (comboBox2.Text.Equals("减速机"))
{
data = data + " " + "02";
}
else if (comboBox2.Text.Equals("中继"))
{
data = " " + "03";
}
if (comboBox4.Text.Equals("所有"))
{
data = data + " FF";
}
else
{
string bz = Ten2Hex(comboBox4.Text);
if (bz.Length == 2)
{
data = data + " " + Ten2Hex(comboBox4.Text);
}
else
{
data = data + " 0" + Ten2Hex(comboBox4.Text);
}
}
if (comboBox1.Text.Equals("所有"))
{
data = data + " FF";
}
else
{
string bz1 = Ten2Hex(comboBox1.Text);
if (bz1.Length == 2)
{
data = data + " " + Ten2Hex(comboBox1.Text);
}
else
{
data = data + " 0" + Ten2Hex(comboBox1.Text);
}
}
string bz3 = Ten2Hex(skinTextBox1.Text);
if (bz3.Length == 2)
{
data = data + " " + Ten2Hex(skinTextBox1.Text);
}
else if (bz3.Length == 1)
{
data = data + " 0" + Ten2Hex(skinTextBox1.Text);
}
else
{
MessageBox.Show("请检查分的格式");
}
string bz4 = Ten2Hex(skinTextBox2.Text);
if (bz4.Length == 2)
{
data = data + " " + Ten2Hex(skinTextBox2.Text);
}
else if (bz4.Length == 1)
{
data = data + " 0" + Ten2Hex(skinTextBox2.Text);
}
else
{
MessageBox.Show("请检查秒的格式");
}
a1 = int.Parse(skinTextBox3.Text);
a1 = a1 / 100;
string s = a1.ToString();
string bz5 = Ten2Hex(s);
if (bz5.Length == 2)
{
data = data + " " + Ten2Hex(s);
}
else if (bz5.Length == 1)
{
data = data + " 0" + Ten2Hex(s);
}
else
{
MessageBox.Show("请检查毫秒的格式");
}
this.addOne.Invoke(data.ToString());
MessageBox.Show("设置成功");
}
}
private async void Received_Message(string Origin_message, string name) //이벤트
{
NetworkStream stream = null;
int count = 0;
if (Origin_message.Equals(Packet.LogoutSignal))
{
await Task.Run(() =>
{
foreach (string processID in List_of_client.Values)
{
if (processID.Equals(name))
{
count++;
}
}
if (count >= 2)
{
return; //중복로그인이면 status 변경x
}
try
{
connection.Open();
connection2.Open();
command.CommandText = "update userlist set status='red' where ID='" + name + "'"; //userlist의 해당 아이디상태 레드로 업데이트
command.ExecuteNonQuery();
command.CommandText = "select ID from userlist"; //그리고 유저리스트의 id모두 찾아서
reader = command.ExecuteReader();
while (reader.Read())
{
command2.CommandText = "update " + reader["ID"].ToString() + "_friends set status='red' where ID='" + name + "'"; //그 아이디의 친구목록에서 해당 아이디상태 변경
command2.ExecuteNonQuery();
}
}
catch (Exception err) { }
finally
{
reader.Close();
connection.Close();
connection2.Close();
}
return;
});
}
else if (Origin_message.StartsWith("[&") && Origin_message.EndsWith("&]"))
{
await Task.Run(() =>
{
statusBox.Invoke((MethodInvoker) delegate()
{
statusBox.AppendText("Receive Data Format '" + Origin_message + "'\n");
});
string[] userList = Origin_message.Split('&');
List <string> uList = new List <string>();
for (int i = 2; i < userList.Length - 1; i++)
{
uList.Add(userList[i]);
}
foreach (var User in List_of_client)
{
foreach (string user in uList)
{
if (user.Equals(User.Value))
{
TcpClient client = User.Key;
stream = client.GetStream();
byte[] Stream_data_ = Encoding.Default.GetBytes(Origin_message); //해당 id에게 전송
stream.Write(Stream_data_, 0, Stream_data_.Length);
stream.Flush();
}
}
}
});
}
else if (Origin_message.StartsWith("^#") && Origin_message.EndsWith("#^"))
{
string devideCharp = Origin_message.Split('#')[1];
string Clicker = devideCharp.Split('*')[1]; //채팅시작을 클릭한 사람
statusBox.Invoke((MethodInvoker) delegate()
{
statusBox.AppendText("Receive Data Format '" + Origin_message + "'\n");
statusBox.AppendText("Clicker : '" + Clicker + "'\n");
});
foreach (var User in List_of_client)
{
if (User.Value.Equals(Clicker))
{
TcpClient client = User.Key;
stream = client.GetStream();
byte[] Stream_data_ = Encoding.Default.GetBytes(Origin_message); //해당 id에게 전송
stream.Write(Stream_data_, 0, Stream_data_.Length);
stream.Flush();
}
}
}
}
//Bracket
public void registerBracket(MySqlConnection conn, Tournament t, ulong tourn_id)
{
setTournamentBracketGeneratedStatus(conn, tourn_id, true);
setTournamentRoundNumber(conn, tourn_id, t.bracket.Rounds.Count);
for (int i = t.bracket.Rounds.Count - 1; i >= 0; i--)
{
Round r = t.bracket.Rounds[i];
for (int j = 0; j < r.Matchups.Count; j++)
{
Matchup m = r.Matchups[j];
//Register Matchup to DB
string q = "INSERT INTO matchups (tournament_id, team1_id, team2_id, next_matchup_id, round_id, team1_reported_winner_id, team2_reported_winner_id) VALUES (@tournament_id, @team1_id, @team2_id, @next_matchup_id, @round_id, @team1_reported_winner_id, @team2_reported_winner_id)";
var cmd = GenerateCommand(conn, q);
cmd.Parameters.AddWithValue("@tournament_id", tourn_id);
cmd.Parameters.AddWithValue("@round_id", i);
//Team1
if (m.Team1 != null)
{
if (m.Team1.IsDummy)
{
cmd.Parameters.AddWithValue("@team1_id", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@team1_id", m.Team1.UID);
}
}
else
{
cmd.Parameters.AddWithValue("@team1_id", DBNull.Value);
}
//Team2
if (m.Team2 != null)
{
if (m.Team2.IsDummy)
{
cmd.Parameters.AddWithValue("@team2_id", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@team2_id", m.Team2.UID);
}
}
else
{
cmd.Parameters.AddWithValue("@team2_id", DBNull.Value);
}
//Team1 reported winner
if (m.Team1ReportedWinner != null)
{
if (m.Team1ReportedWinner.IsDummy)
{
cmd.Parameters.AddWithValue("@team1_reported_winner_id", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@team1_reported_winner_id", m.Team1ReportedWinner.UID);
}
}
else
{
cmd.Parameters.AddWithValue("@team1_reported_winner_id", DBNull.Value);
}
//Team2 reported winner
if (m.Team2ReportedWinner != null)
{
if (m.Team2ReportedWinner.IsDummy)
{
cmd.Parameters.AddWithValue("@team2_reported_winner_id", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@team2_reported_winner_id", m.Team2ReportedWinner.UID);
}
}
else
{
cmd.Parameters.AddWithValue("@team2_reported_winner_id", DBNull.Value);
}
//Next Match
if (m.Next != null)
{
cmd.Parameters.AddWithValue("@next_matchup_id", m.Next.UID);
}
else
{
cmd.Parameters.AddWithValue("@next_matchup_id", DBNull.Value);
}
MySqlDataReader rdr = cmd.ExecuteReader();
long matchup_uid = cmd.LastInsertedId;
m.UID = matchup_uid;
rdr?.Close();
}
}
}
//*****************************************************************************************
//*****************************************************************************************
public void ReduceItemQuantity(ProductUpdate productUpdate)
{
int ProductId = productUpdate.ProductId;
int Quantity = productUpdate.QuantityToBeRemoved;
MySqlDataReader mysql_datareader = null;
MySqlCommand command = null;
int orgQuantity = -1;
try
{
// Get Quantity of item in database
string query1 = "SELECT quantity FROM products WHERE product_id = (@id) LIMIT 1;";
command = m_dbConnection.CreateCommand();
command.Connection.Open();
command.CommandText = query1;
command.Parameters.Add("@id", MySqlDbType.Int32).Value = ProductId;
mysql_datareader = command.ExecuteReader();
while (mysql_datareader.Read())
{
orgQuantity = mysql_datareader.GetInt32(0);
}
// Make sure the is enough inventory
if (orgQuantity < Quantity)
{
// throw new ArgumentException("Purchased qantity is greater than stored quantity", "Quantity");
}
command.Connection.Close();
// Reduce the quantity of the item by n
string query2 = "UPDATE products SET quantity = (@newQuantity) WHERE product_id = (@id);";
command = m_dbConnection.CreateCommand();
command.Connection.Open();
command.CommandText = query2;
command.Parameters.Add("@id", MySqlDbType.Int32).Value = ProductId;
command.Parameters.Add("@newQuantity", MySqlDbType.Int32).Value = orgQuantity - Quantity;
int rows_affected = command.ExecuteNonQuery();
//Console.Out.WriteLine("Rows Affected:\t" + rows_affected);
}
catch (Exception e)
{
Console.Out.WriteLine("\n\n**********************************************************************");
Console.Out.WriteLine(e.Message);
Console.Out.WriteLine(e.InnerException);
Console.Out.WriteLine(e.Source);
Console.Out.WriteLine("**********************************************************************\n\n");
}
finally
{
mysql_datareader?.Close();
command?.Connection?.Close();
}
}
public IEnumerable <VoucherTypeClass> insertGiftVoucher(int Amount, string Quantity, string Email, string Address, int TotalAmount, int UserId, int Status, int CreatedBy, string Description, string PaymentStatus, int IsActive, string PaymentType)
{
con.Open();
MySqlTransaction tran = con.BeginTransaction();
try
{
MySqlCommand cmd3 = new MySqlCommand("select VoucherTypeId from tblVoucherType order by VoucherTypeId DESC limit 1", con, tran);
MySqlDataReader dr3 = cmd3.ExecuteReader();
dr3.Read();
int Number = Convert.ToInt32(dr3["VoucherTypeId"]);
dr3.Close();
Random generator = new Random();
int RandomNumber = generator.Next(100000, 1000000);
string currentYear = DateTime.Now.Year.ToString();
string Giftcard = "" + RandomNumber + Number;
string CouponName1 = "Giftcard";
string FromDate1 = DateTime.Now.ToString("yyyy-MM-dd");
string dateInString = FromDate1;
DateTime startDate = DateTime.Parse(dateInString);
DateTime expiryDate = startDate.AddDays(30);
string ToDate1 = Convert.ToDateTime(expiryDate).ToString("yyyy-MM-dd");
// endDate = endDate.AddDays(addedDays);
// string ToDate1 = FromDate1.AddDays(30);
MySqlCommand cmd = new MySqlCommand("insert into tblVoucherType(Amount,Quantity,Email,Address,TotalAmount,UserId,CouponName,CouponCode,Status,CreatedBy,CreatedDate,Description,FromDate,ToDate)values(" + Amount + ",'" + Quantity + "','" + Email + "','" + Address + "'," + TotalAmount + "," + UserId + ",'" + CouponName1 + "','" + Giftcard + "'," + Status + "," + CreatedBy + ",'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" + Description + "','" + FromDate1 + "','" + ToDate1 + "')", con, tran);
cmd.ExecuteNonQuery();
int giftcardId = Convert.ToInt32(cmd.LastInsertedId);
MySqlCommand cmd1 = new MySqlCommand("insert into tblPayments(UserId,PaymentType,PaymentStatus,IsActive,CreatedBy,CreatedDate,GiftcardId) values('" + UserId + "','" + PaymentType + "','" + PaymentStatus + "','" + IsActive + "','" + CreatedBy + "','" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "','" + giftcardId + "' )", con, tran);
cmd1.ExecuteNonQuery();
int Paymenid = Convert.ToInt32(cmd1.LastInsertedId);
obj.Add(new VoucherTypeClass {
Message = "Success", VoucherTypeId = giftcardId, PaymentsId = Paymenid, GiftCoupon = Giftcard
});
tran.Commit();
return(obj);
}
catch (MySqlException ex)
{
tran.Rollback();
if (ex.Message.Contains("UNIQUE"))
{
obj.Add(new VoucherTypeClass {
Message = "UniqueConstraint", ErrorMessage = ex.Message
});
return(obj);
}
else
{
obj.Add(new VoucherTypeClass {
Message = "Error", ErrorMessage = ex.Message
});
return(obj);
}
}
catch (Exception ex)
{
obj.Add(new VoucherTypeClass {
Message = "Error", ErrorMessage = ex.Message
});
return(obj);
}
finally
{
con.Close();
}
}
public IEnumerable <VoucherTypeClass> GiftCouponApplicable(string CouponCode, string Email)
{
string URL = "http://www.google.com";
System.Net.HttpWebRequest rq2 = (System.Net.HttpWebRequest)System.Net.WebRequest.Create(URL);
System.Net.HttpWebResponse res2 = (System.Net.HttpWebResponse)rq2.GetResponse();
DateTime date = DateTime.Parse(res2.Headers["Date"]);
String Date = date.ToString("yyyy-MM-dd");
try
{
MySqlCommand cmd = new MySqlCommand("select * from tblVoucherType v where v.CouponCode='" + CouponCode + "' AND '" + Date + "' between v.FromDate AND v.ToDate AND v.Status=1 AND v.Email='" + Email + "'", con);
// con.Open();
MySqlDataReader dr2 = cmd.ExecuteReader();
// dr.Close();
while (dr2.Read())
{
obj.Add(new VoucherTypeClass
{
// TotalAmount = dr2["TotalAmount"].ToString(),
VoucherTypeId = Convert.ToInt32(dr2["VoucherTypeId"]),
DiscountAmount = Convert.ToInt32(dr2["TotalAmount"]),
GiftCoupon = dr2["CouponCode"].ToString(),
CouponName = dr2["CouponName"].ToString(),
// Salons = dr["Salons"].ToString(),
Message = "Coupon Code Applied Successfully",
});
}
if (!dr2.HasRows)
{
dr2.Close();
obj.Add(new VoucherTypeClass
{
Message = "Coupon Code Expired or Invalid",
// DiscountAmount = 0,
});
return(obj);
}
//else
//{
// int Voucherid = Convert.ToInt32(dr2["VoucherTypeId"]);
// dr2.Close();
// MySqlCommand cmd1 = new MySqlCommand("update tblVoucherType set Status=2 where VoucherTypeId='" + Voucherid + "'", con);
// cmd1.ExecuteNonQuery();
//}
return(obj);
}
catch (MySqlException ex)
{
if (ex.Message.Contains("duplicate entry"))
{
obj.Add(new VoucherTypeClass {
Message = "UniqueConstraint", ErrorMessage = ex.Message
});
return(obj);
}
else
{
obj.Add(new VoucherTypeClass {
Message = "Error", ErrorMessage = ex.Message
});
return(obj);
}
}
catch (Exception ex)
{
obj.Add(new VoucherTypeClass {
Message = "Error", ErrorMessage = ex.Message
});
return(obj);
}
finally
{
con.Close();
}
}
//
public IEnumerable <VoucherTypeClass> CoupenApplicable(string CouponCode, string Salons, string Email)
{
//string URL = "http://www.google.com";
//System.Net.HttpWebRequest rq2 = (System.Net.HttpWebRequest)System.Net.WebRequest.Create(URL);
//System.Net.HttpWebResponse res2 = (System.Net.HttpWebResponse)rq2.GetResponse();
//DateTime date = DateTime.Parse(res2.Headers["Date"]);
//String Date = date.ToString("yyyy-MM-dd");
string Date = DateTime.Now.AddMinutes(750).ToString("yyyy-MM-dd");
//if (CouponCode.StartWith("hr") == true) {
//}
try
{
MySqlCommand cmd9 = new MySqlCommand("select * from tblVoucherType v where v.CouponCode='" + CouponCode + "'", con);
con.Open();
MySqlDataReader dr9 = cmd9.ExecuteReader();
while (dr9.Read())
{
//obj.Add(new VoucherTypeClass
//{
// // DiscountAmount = dr["DiscountAmount"].ToString(),
// // VoucherTypeId = Convert.ToInt32(dr["VoucherTypeId"]),
// // Salons = dr["Salons"].ToString(),
// // Message = "Success",
// CouponName = dr9["CouponName"].ToString(),
// CouponCode = dr9["CouponName"].ToString(),
//});
string CouponName = dr9["CouponName"].ToString();
string CouponCode1 = dr9["CouponCode"].ToString();
if (CouponName == "Giftcard")
{
dr9.Close();
GiftCouponApplicable(CouponCode1, Email);
return(obj);
}
}
if (!dr9.IsClosed)
{
dr9.Close();
}
///////
MySqlCommand cmd = new MySqlCommand("select * from tblVoucherType v where v.CouponCode='" + CouponCode + "' AND '" + Date + "' between v.FromDate AND v.ToDate AND v.IsActive=1", con);
// con.Open();
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
//obj.Add(new VoucherTypeClass
//{
// DiscountAmount = dr["DiscountAmount"].ToString(),
// VoucherTypeId = Convert.ToInt32(dr["VoucherTypeId"]),
// Salons = dr["Salons"].ToString(),
// Message = "Success",
// CouponName = dr["CouponName"].ToString();
//});
}
if (!dr.HasRows)
{
dr.Close();
obj.Add(new VoucherTypeClass
{
Message = "Coupon Code Expired or Invalid",
DiscountAmount = 0,
});
return(obj);
}
else
{
// int amount = Convert.ToInt32(dr["MinimumAmount"]);
string CouponName = dr["CouponName"].ToString();
string Salonsids = dr["Salons"].ToString();
int discountAmount = Convert.ToInt32(dr["DiscountAmount"]);
string couponcode = dr["couponcode"].ToString();
int voucherid = Convert.ToInt32(dr["VoucherTypeId"]);
//if (CouponName == "Giftcard")
//{
//}
dr.Close();
// MySqlCommand cmd2 = new MySqlCommand("select count(*) from tblVoucherType v where v.CouponCode='" + CouponCode + "' AND v.MinimumAmount<='" + MinimumAmount + "'", con);
// int j = Convert.ToInt32(cmd2.ExecuteScalar());
////if(j==0)
// {
// obj.Add(new VoucherTypeClass
// {
// Message = "Minimum Total Transaction Value Is " + amount,
// DiscountAmount = 0,
// });
// return obj;
// }
//else
// {
string value = Salonsids;
char delimiter = ',';
string[] substrings = value.Split(delimiter);
int k = 0;
for (int i = 0; i < substrings.Length; i++)
{
if (substrings[i] == Salons)
{
k = 1;
}
}
if (k == 1)
{
obj.Add(new VoucherTypeClass
{
Message = "Coupon Code Applied Successfully",
DiscountAmount = discountAmount,
// MinimumAmount= amount,
VoucherTypeId = voucherid,
GiftCoupon = couponcode,
CouponName = CouponName,
Salons = Salonsids,
// MinimumAmount= Convert.ToInt32(dr["MinimumAmount"]),
// VoucherTypeId = Convert.ToInt32(dr["VoucherTypeId"]),
// CouponCode = dr["CouponCode"].ToString(),
});
return(obj);
}
else
{
obj.Add(new VoucherTypeClass
{
Message = "Coupon Not Applicable For This Salon ",
});
return(obj);
}
// }
}
// return obj;
}
catch (MySqlException ex)
{
if (ex.Message.Contains("duplicate entry"))
{
obj.Add(new VoucherTypeClass {
Message = "UniqueConstraint", ErrorMessage = ex.Message
});
return(obj);
}
else
{
obj.Add(new VoucherTypeClass {
Message = "Error", ErrorMessage = ex.Message
});
return(obj);
}
}
catch (Exception ex)
{
obj.Add(new VoucherTypeClass {
Message = "Error", ErrorMessage = ex.Message
});
return(obj);
}
finally
{
con.Close();
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
lstDocuments.Items.Clear();
rtbSearchBody.Clear();
string username = txtUsername.Text;
if (username == "<Not logged in>")
{
username = "******";
}
string[] searchTerms = Generate_Search_Index(txtSearch.Text, true).Split(' ');
try
{
MySqlConnection conn = new MySqlConnection(connString);
MySqlCommand command = conn.CreateCommand();
conn.Open();
command.CommandText = "SELECT COUNT(*) FROM documents";
MySqlDataReader dr = command.ExecuteReader();
dr.Read();
Document[] documents = new Document[Convert.ToInt32(dr["COUNT(*)"])];
dr.Close();
command.CommandText = "SELECT * FROM documents ORDER BY date_created DESC";
dr = command.ExecuteReader();
int j = 0;
while (dr.Read())
{
bool isAllowed = Get_Permissions(username, dr);
if (isAllowed)
{
documents[j] = new Document(Convert.ToInt32(dr["id"]), dr["sender"].ToString(), dr["recipients"].ToString(), dr["subject"].ToString(), dr["body"].ToString(), dr["search_index"].ToString(), dr["date_created"].ToString(), Convert.ToInt32(dr["encrypted"]));
if (string.IsNullOrEmpty(documents[j].Subject))
{
documents[j].Subject = "<No Subject>";
}
j++;
}
}
conn.Close();
if (!string.IsNullOrEmpty(searchTerms[0]))
{
for (int i = 0; i < documents.Length; i++)
{
bool loopAgain = true;
foreach (string searchTerm in searchTerms)
{
if (loopAgain)
{
foreach (string word in documents[i].SearchIndex.Split(' '))
{
if (word == searchTerm)
{
Append_Document(username, documents, i);
loopAgain = false;
break;
}
}
}
else
{
break;
}
}
}
}
else
{
for (int i = 0; i < documents.Length; i++)
{
if (documents[i].Id != 0)
{
Append_Document(username, documents, i);
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
txtSearch.Clear();
}
private async void RequestingClient_Acceptor()
{
client = default(TcpClient); //클라이언트 생성
string name = null; //해당 연결된 클라이언트의 id를 저장
byte[] message = null; //패킷을 담을 공간
bool isDuple = false;
await Task.Run(() => //비동기적 실행(await)
{
while (true)
{
client = server.AcceptTcpClient();
statusBox.Invoke((MethodInvoker) delegate()
{
statusBox.AppendText("Try Approve Client Access...\n");
});
NetworkStream stream = client.GetStream(); //해당 클라이언트와 네트워크 스트림 생성
byte[] Stream_data = new byte[1024];
int Len = stream.Read(Stream_data, 0, Stream_data.Length); //연결되면 클라이언트는 id를 보낸다.
name = Encoding.Default.GetString(Stream_data, 0, Len); //string형식으로 인코딩합니다.
stream.Flush();
message = Encoding.Default.GetBytes(Packet.Approve); //연결 허가 패킷제작
stream.Write(message, 0, message.Length); //위 패킷 전송
stream.Flush();
List_of_client.Add(client, name); //클라이언트 객체와 id저장
HomeClientAction Branch_client = new HomeClientAction(client, name); //서버사이드 쓰레드의 생성
Branch_client.Receive_Action += new HomeClientAction.MessageReceive_Throw(Received_Message); //이벤트 함수추가1
Branch_client.Shutdown_Action += new HomeClientAction.ShutdownCode_Throw(ExitServer); //이벤트 함수추가2
ClientThreadHandler RunningMachine = new ClientThreadHandler(Branch_client.Start_Thread_of_Client);
BeginInvoke(RunningMachine);
statusBox.Invoke((MethodInvoker) delegate()
{
statusBox.AppendText($"{name} Login...\n");
});
try
{
connection.Open();
connection2.Open();
command.CommandText = "update userlist set status='green' where ID='" + name + "'"; //userlist의 해당 아이디상태 그린으로 업데이트
command.ExecuteNonQuery(); //위 쿼리문 실행
command.CommandText = "select ID from userlist"; //그리고 유저리스트의 id모두 찾아서
reader = command.ExecuteReader();
while (reader.Read())
{
command2.CommandText = "update " + reader["ID"].ToString() + "_friends set status='green' where ID='" + name + "'"; //그 아이디의 친구목록에서 해당 아이디상태 변경
command2.ExecuteNonQuery();
}
Task.Run(() => {
foreach (var User in List_of_client)
{
TcpClient client = User.Key;
stream = client.GetStream();
byte[] Stream_data_ = Encoding.Default.GetBytes(Packet.ChangeSignal); //테이블의 상태변화 되었음을 알림
stream.Write(Stream_data_, 0, Stream_data_.Length);
stream.Flush();
}
});
}
catch (Exception err) { }
finally
{
reader.Close();
connection.Close();
connection2.Close();
}
}
});
}
public object ExecuteQuery(EQueryType queryType, string query, params MySqlParameter[] parameters)
{
object result = null;
MySqlDataReader reader = null;
using (var connection = CreateConnection())
{
try
{
var command = connection.CreateCommand();
command.CommandText = query;
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
connection.Open();
switch (queryType)
{
case EQueryType.Reader:
var readerResult = new List <Row>();
reader = command.ExecuteReader();
while (reader.Read())
{
try
{
var values = new Dictionary <string, object>();
for (var i = 0; i < reader.FieldCount; i++)
{
var columnName = reader.GetName(i);
values.Add(columnName, reader[columnName]);
}
readerResult.Add(new Row {
Values = values
});
}
catch (Exception ex)
{
Logger.LogError(
$"The following query threw an error during reader execution:\nQuery: \"{query}\"\nError: {ex.Message}");
}
}
result = readerResult;
break;
case EQueryType.Scalar:
result = command.ExecuteScalar();
break;
case EQueryType.NonQuery:
result = command.ExecuteNonQuery();
break;
default:
throw new ArgumentOutOfRangeException(nameof(queryType), queryType, null);
}
}
catch (Exception ex)
{
Logger.LogError("[RFGarage] DbError: " + ex);
}
finally
{
reader?.Close();
connection.Close();
}
}
return(result);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!User.Identity.IsAuthenticated) // if the user is already logged in
{
Response.Redirect("~/Account/Login.aspx");
}
else
{
airport_name = "";
String id_string = Request.QueryString["id"];
Int32.TryParse(id_string, out airport_id);
this.lblAirport.Text = airport_id.ToString();
MySqlConnection conn = new MySqlConnection(ConnStr);
conn.Open();
String query = @"SELECT name FROM RateMyAirline.iata_airport_info " + "where id = " + airport_id.ToString();
MySqlCommand cmd = new MySqlCommand(query, conn);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
airport_name = reader.GetString(0);
}
reader.Close();
this.lblAirport.Text = airport_name;
String user_name = System.Web.HttpContext.Current.User.Identity.Name;
String FindCmd = "select id,airport_id,user_name,facilities,customer_service,parking,amenities,wait_time,comments,date_time from RateMyAirline.ratings where airport_id = " + airport_id.ToString() + " order by date_time desc";
cmd = new MySqlCommand(FindCmd, conn);
reader = cmd.ExecuteReader();
int review_id = -1;
int count_of = 0;
String result = "";
result = "<table border=\"1\" style = \"margin-left: auto; margin-right: auto; \">";
result += "<tr><th>Date</th><th>Name</th><th>Customer Service</th><th>Wait time</th><th>Amenities</th><th>Cleanliness</th><th>Parking</th></tr>";
while (reader.Read())
{
review_id = reader.GetInt32(0);
int review_airport_id = reader.GetInt32(1);
string review_user_name = reader.GetString(2);
int review_facilities = reader.GetInt32(3);
int review_cust_service = reader.GetInt32(4);
int review_parking = reader.GetInt32(5);
int review_amenities = reader.GetInt32(6);
int review_waittime = reader.GetInt32(7);
string review_comments = reader.GetString(8);
int time_of = reader.GetInt32(9);
string item = "<tr><td>" + time_of.ToString() + "</td><td>" + review_user_name + "</td><td>" + review_cust_service.ToString() + "</td><td>"
+ review_waittime.ToString() + "</td><td>" + review_amenities.ToString() + "</td><td>" + review_facilities.ToString() + "</td><td>"
+ review_parking.ToString() + "</td></tr>";
result += item;
++count_of;
}
result += "</table>";
reader.Close();
conn.Close();
this.lblRatings.Text = result;
}
}
/// <summary>
/// MySqlDataReader转换成DataTable
/// </summary>
/// <param name="dataReader"></param>
/// <returns></returns>
public static DataTable GetNewDataTable(MySqlDataReader dataReader)
{
DataTable datatable = new DataTable();
DataTable schemaTable = dataReader.GetSchemaTable();
//动态添加列
try
{
foreach (DataRow myRow in schemaTable.Rows)
{
DataColumn myDataColumn = new DataColumn();
myDataColumn.DataType = myRow.GetType();
myDataColumn.ColumnName = myRow[0].ToString();
datatable.Columns.Add(myDataColumn);
}
//添加数据
while (dataReader.Read())
{
DataRow myDataRow = datatable.NewRow();
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
myDataRow[i] = dataReader[i].ToString();
}
datatable.Rows.Add(myDataRow);
myDataRow = null;
}
schemaTable = null;
dataReader.Close();
return datatable;
}
catch (Exception ex)
{
throw new Exception("转换出错出错!", ex);
}
}
private void button3_Click(object sender, EventArgs e)
{
int exists = 0;
bool result = false;
string menu = "Entrees";
if (radioButton1.Checked)
{
menu = "Entrees";
}
if (radioButton2.Checked)
{
menu = "SideDishes";
}
if (radioButton3.Checked)
{
menu = "Deserts";
}
string cmdText = "SET TRANSACTION READ ONLY;";
cmd = new MySqlCommand(cmdText, conn);
cmd.ExecuteNonQuery();
cmdText = "SET TRANSACTION ISOLATION LEVEL READ COMMITTED;";
cmd = new MySqlCommand(cmdText, conn);
cmd.ExecuteNonQuery();
cmdText = "START TRANSACTION;";
cmd = new MySqlCommand(cmdText, conn);
cmd.ExecuteNonQuery();
sql = "SELECT COUNT(name) FROM " + menu + " WHERE name = \'" + textBox1.Text + " \';";
cmd = new MySqlCommand(sql, conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
result = Int32.TryParse((rdr[0].ToString()), out exists);
}
rdr.Close();
cmdText = "COMMIT;";
cmd = new MySqlCommand(cmdText, conn);
cmd.ExecuteNonQuery();
if (exists > 0)
{
MessageBox.Show("This item is already on the menu");
//make most controls invisible
makeVisible(false);
textBox1.Text = "";
return;
}
if (!result)
{
MessageBox.Show("There was an error in trying to test the item. \nPlease try again.");
//make most controls invisible
makeVisible(false);
textBox1.Text = "";
return;
}
//make everything visible if we can add to the menu items
makeVisible(true);
}
public void ApplySingleMatchupUpdate(MySqlConnection conn, Matchup m, ref Tournament t)
{
//Update Matchup to DB
string q = "UPDATE matchups SET status_in_progress = @status_in_progress, team1_id=@team1_id, team2_id=@team2_id, winner_id=@winner_id, team1_reported_winner_id=@team1_reported_winner_id, team2_reported_winner_id=@team2_reported_winner_id, lobby_name=@lobby_name, lobby_pass=@lobby_pass WHERE tournament_id = @tournament_id AND ID = @uid";
var cmd = GenerateCommand(conn, q);
cmd.Parameters.AddWithValue("@tournament_id", t.info.UID);
cmd.Parameters.AddWithValue("@uid", m.UID);
cmd.Parameters.AddWithValue("@status_in_progress", m.InProgress);
cmd.Parameters.AddWithValue("@lobby_name", m.LobbyName);
cmd.Parameters.AddWithValue("@lobby_pass", m.LobbyPass);
//Team1
if (m.Team1 != null)
{
if (m.Team1.IsDummy)
{
cmd.Parameters.AddWithValue("@team1_id", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@team1_id", m.Team1.UID);
}
}
else
{
cmd.Parameters.AddWithValue("@team1_id", DBNull.Value);
}
//Team2
if (m.Team2 != null)
{
if (m.Team2.IsDummy)
{
cmd.Parameters.AddWithValue("@team2_id", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@team2_id", m.Team2.UID);
}
}
else
{
cmd.Parameters.AddWithValue("@team2_id", DBNull.Value);
}
//Team1 reported winner
if (m.Team1ReportedWinner != null)
{
if (m.Team1ReportedWinner.IsDummy)
{
cmd.Parameters.AddWithValue("@team1_reported_winner_id", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@team1_reported_winner_id", m.Team1ReportedWinner.UID);
}
}
else
{
cmd.Parameters.AddWithValue("@team1_reported_winner_id", DBNull.Value);
}
//Team2 reported winner
if (m.Team2ReportedWinner != null)
{
if (m.Team2ReportedWinner.IsDummy)
{
cmd.Parameters.AddWithValue("@team2_reported_winner_id", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@team2_reported_winner_id", m.Team2ReportedWinner.UID);
}
}
else
{
cmd.Parameters.AddWithValue("@team2_reported_winner_id", DBNull.Value);
}
//Winner
if (m.Winner != null)
{
if (m.Team2.IsDummy)
{
cmd.Parameters.AddWithValue("@winner_id", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@winner_id", m.Winner.UID);
}
}
else
{
cmd.Parameters.AddWithValue("@winner_id", DBNull.Value);
}
//Next Match
if (m.Next != null)
{
cmd.Parameters.AddWithValue("@next_matchup_id", m.Next.UID);
}
else
{
cmd.Parameters.AddWithValue("@next_matchup_id", DBNull.Value);
}
//Lobby Info
MySqlDataReader rdr = cmd.ExecuteReader();
rdr?.Close();
}
private void bouton_crediter_Click(object sender, RoutedEventArgs e)
{
#region Connexion
MySqlConnection maConnexion = null;
try
{
string connexionString = "SERVER=localhost;PORT=3306;" +
"DATABASE=cooking;" +
"UID=root;PASSWORD=Jessyann1808*";
maConnexion = new MySqlConnection(connexionString);
maConnexion.Open();
}
catch (MySqlException e1)
{
Console.WriteLine(" ErreurConnexion : " + e1.ToString());
return;
}
#endregion
#region Récupération du compte
string requete_compte = "Select compte from client where id_client = '" + textblock_recup_id.Text + "';";
MySqlCommand command_compte = maConnexion.CreateCommand();
command_compte.CommandText = requete_compte;
MySqlDataReader reader_compte = command_compte.ExecuteReader();
string chaine_compte = "";
while (reader_compte.Read())
{
chaine_compte += reader_compte["compte"];
}
reader_compte.Close();
command_compte.Dispose();
#endregion
if (chaine_compte == "")
{
chaine_compte = "0";
}
int resultat = Convert.ToInt32(chaine_compte) + Convert.ToInt32(combobox_crediter.SelectedItem);
#region Update du compte du client
string requete = "Update client set compte = " + resultat + " where id_client = '" + textblock_recup_id.Text + "';";
MySqlCommand command = maConnexion.CreateCommand();
command.CommandText = requete;
MySqlDataReader reader = command.ExecuteReader();
reader.Close();
command.Dispose();
#endregion
maConnexion.Close();
MessageBox.Show("Votre compte à bien été créditer. Quittez puis accédez à votre compte pour voir votre solde actualisé");
}
public PlayerData QueryPlayerData(string acct, string pass)
{
bool isNew = true;
PlayerData playerData = null;
MySqlDataReader reader = null;
try
{
MySqlCommand command = new MySqlCommand("select * from drakgod where acct=@acct", connection);
command.Parameters.AddWithValue("acct", acct);
reader = command.ExecuteReader();
if (reader.Read())
{
isNew = false;
string _pass = reader.GetString("pass");
if (_pass.Equals(pass))
{
////密码正确，返回玩家数据
playerData = new PlayerData
{
Id = reader.GetInt32("id"),
Name = reader.GetString("name"),
Level = reader.GetInt32("level"),
Exp = reader.GetInt32("exp"),
Power = reader.GetInt32("power"),
Vip = reader.GetInt32("vip"),
Coin = reader.GetInt32("coin"),
Diamond = reader.GetInt32("diamond"),
Crystal = reader.GetInt32("crystal"),
Hp = reader.GetInt32("hp"),
Ad = reader.GetInt32("ad"),
Ap = reader.GetInt32("ap"),
AdDefense = reader.GetInt32("addefense"),
ApDefense = reader.GetInt32("apdefense"),
Dodge = reader.GetInt32("dodge"),
Pierce = reader.GetInt32("pierce"),
Critical = reader.GetInt32("critical"),
GuideId = reader.GetInt32("guideid"),
Time = reader.GetInt64("time"),
Battle = reader.GetInt32("battle"),
//TODO
};
#region Strong
string[] strongArray = reader.GetString("strong").Split('#');
int[] strongArr = new int[6];
for (int i = 0; i < strongArray.Length; i++)
{
if (strongArray[i] == "")
{
continue;
}
if (int.TryParse(strongArray[i], out int starLevel))
{
strongArr[i] = starLevel;
}
else
{
PeRoot.Log("Parse Strong Data Error", LogType.LogError);
}
}
playerData.StrongArray = strongArr;
#endregion
#region Task
string[] taskArray = reader.GetString("task").Split('#');
playerData.TaskArray = new string[6];
for (int i = 0; i < taskArray.Length; i++)
{
if (taskArray[i] == "")
{
continue;
}
else if (taskArray[i].Length >= 5)
{
playerData.TaskArray[i] = taskArray[i];
}
else
{
throw new Exception("DataError.");
}
}
#endregion
}
}
}
catch (Exception e)
{
PeRoot.Log(e.ToString(), LogType.LogError);
}
finally
{
reader?.Close();
if (isNew)
{
//不存在账号数据，创建新的默认账号数据，并返回
playerData = new PlayerData
{
Id = -1,
Name = "",
Level = 1,
Exp = 0,
Power = 150,
Vip = 0,
Coin = 5000,
Diamond = 500,
Crystal = 500,
Hp = 2000,
Ad = 100,
Ap = 100,
AdDefense = 40,
ApDefense = 40,
Dodge = 5, //闪避概率
Pierce = 5, //穿透比率
Critical = 5, //暴击概率
GuideId = 1001,
StrongArray = new int[6],
Time = TimeSvc.Instance.GetNowTime(),
TaskArray = new string[6],
Battle = 10001,
//TODO
};
for (int i = 0; i < playerData.TaskArray.Length; i++)
{
playerData.TaskArray[i] = (i + 1) + "|0|0";
}
playerData.Id = InsertNewAcctData(acct, pass, playerData);
}
}
return(playerData);
}
public StudentAddResponse Post([FromBody] StudentAddRequest req)
{
StudentAddResponse resp = new StudentAddResponse();
try
{
MySqlConnection conn = SQLManager.getConn(); //连接数据库
conn.Open(); //打开数据库连接
MySqlCommand test = null; //创建查询指令
test = new MySqlCommand("select* from student where student_ID=?id;", conn);
test.Parameters.AddWithValue("?id", req.id);
MySqlDataReader mdr = test.ExecuteReader();
if (!mdr.HasRows) //id不存在
{
resp.result = "wrong id";
mdr.Close();
}
else
{
mdr.Close();
MySqlCommand cmd = null; //创建查询指令
if (req.password != null) //修改密码
{
cmd = new MySqlCommand("update student set password = @password where student_ID = @id;", conn);
cmd.Parameters.AddWithValue("@id", req.id);
cmd.Parameters.AddWithValue("@password", req.password);
cmd.Prepare();
cmd.ExecuteNonQuery(); //执行update语句
}
if (req.department != null) //修改学院
{
cmd = new MySqlCommand("update student set department = @department where student_ID = @id;", conn);
cmd.Parameters.AddWithValue("@id", req.id);
cmd.Parameters.AddWithValue("@department", req.department);
cmd.Prepare();
cmd.ExecuteNonQuery(); //执行update语句
}
if (req.classnum != null) //修改班级
{
cmd = new MySqlCommand("update student set class_num = @classnum where student_ID = @id;", conn);
cmd.Parameters.AddWithValue("@id", req.id);
cmd.Parameters.AddWithValue("@classnum", req.classnum);
cmd.Prepare();
cmd.ExecuteNonQuery(); //执行update语句
}
if (req.dorm != null) //修改宿舍
{
cmd = new MySqlCommand("update student set dorm_num = @dorm where student_ID = @id;", conn);
cmd.Parameters.AddWithValue("@id", req.id);
cmd.Parameters.AddWithValue("@dorm", req.dorm);
cmd.Prepare();
cmd.ExecuteNonQuery(); //执行update语句
}
if (req.healthcodecolor != null) //修改健康码颜色
{
string color = "";
if (req.healthcodecolor == "绿色")
{
color = "green";
}
else if (req.healthcodecolor == "红色")
{
color = "red";
}
else if (req.healthcodecolor == "黄色")
{
color = "yellow";
}
cmd = new MySqlCommand("update student set healthcode_color = @healthcodecolor where student_ID = @id;", conn);
cmd.Parameters.AddWithValue("@id", req.id);
cmd.Parameters.AddWithValue("@healthcodecolor", color);
cmd.Prepare();
cmd.ExecuteNonQuery(); //执行update语句
}
if (req.telephone != null) //修改联系方式
{
cmd = new MySqlCommand("update student set contact_way = @telephone where student_ID = @id;", conn);
cmd.Parameters.AddWithValue("@id", req.id);
cmd.Parameters.AddWithValue("@telephone", req.telephone);
cmd.Prepare();
cmd.ExecuteNonQuery(); //执行update语句
}
if (req.emergencyphone != null) //修改紧急联系方式
{
cmd = new MySqlCommand("update student set emergency_contact = @emergencyphone where student_ID = @id;", conn);
cmd.Parameters.AddWithValue("@id", req.id);
cmd.Parameters.AddWithValue("@emergencyphone", req.emergencyphone);
cmd.Prepare();
cmd.ExecuteNonQuery(); //执行update语句
}
if (req.healthstatus != null) //修改健康状态
{
cmd = new MySqlCommand("update student set currenthealth_status = @healthstatus where student_ID = @id;", conn);
cmd.Parameters.AddWithValue("@id", req.id);
int health;
switch (req.healthstatus)
{
case "健康":
health = 0;
break;
case "发热":
health = 1;
break;
case "疑似":
health = 2;
break;
case "确诊":
health = 3;
break;
default: health = -1; break;
}
cmd.Parameters.AddWithValue("@healthstatus", health);
cmd.Prepare();
cmd.ExecuteNonQuery();//执行update语句
}
resp.result = "success";
}
conn.Close();//关闭连接
}
catch (Exception ex)
{
Console.WriteLine(ex);
resp.result = "fail";
}
return(resp);
}
public void CloseConnections(MySqlDataReader rdr, MySqlConnection conn)
{
rdr?.Close();
conn?.Close();
}
public bool CreateOrder(Order order)
{
if (order == null || order.listticket == null || order.listticket.Count == 0)
{
return(false);
}
bool result = false;
MySqlConnection connection = DBHelper.OpenConnection();
MySqlCommand command = connection.CreateCommand();
command.Connection = connection;
try
{
command.CommandText = "lock tables orders write,orderdetails write,tickets write";
command.ExecuteNonQuery();
trans = connection.BeginTransaction();
command.Transaction = trans;
MySqlDataReader reader = null;
command.CommandText = "insert into orders(cus_id,order_date,order_status) values(@customerId,@orderDate,@orderStatus)";
command.Parameters.Clear();
command.Parameters.AddWithValue("@customerId", order.Customer.Id);
command.Parameters.AddWithValue("@orderDate", order.OrderDate);
command.Parameters.AddWithValue("@orderStatus", order.OrderStatus);
command.ExecuteNonQuery();
command.CommandText = "select LAST_INSERT_ID() as order_id";
reader = command.ExecuteReader();
if (reader.Read())
{
order.OrderID = reader.GetInt32("order_id");
}
reader.Close();
foreach (var item in order.listticket)
{
if (item.TicketID == 0 || item.Amount <= 0)
{
throw new Exception("Not exits item");
}
command.CommandText = "select tkt.tickettype_price from tickets t inner join typeoftickets tkt on t.ticket_type = tkt.tickettype_name where ticket_id = @TicketID";
command.Parameters.Clear();
command.Parameters.AddWithValue("@TicketID", item.TicketID);
reader = command.ExecuteReader();
if (!reader.Read())
{
throw new Exception("Not exits item");
}
item.TicketPrice = reader.GetDouble("tickettype_price");
reader.Close();
command.CommandText = @"insert into orderdetails(order_id,ticket_id,amount,unit_price) values
(" + order.OrderID + "," + item.TicketID + "," + item.Amount + "," + item.TicketPrice + ")";
command.ExecuteNonQuery();
command.CommandText = @"update tickets set ticket_amount = ticket_amount -@quantity
where ticket_id = " + item.TicketID + ";";
command.Parameters.Clear();
command.Parameters.AddWithValue("@quantity", item.Amount);
command.ExecuteNonQuery();
}
trans.Commit();
result = true;
}
catch (Exception)
{
// Console.WriteLine(e.Message);
result = false;
try
{
trans.Rollback();
}
catch (System.Exception)
{
}
}
finally
{
command.CommandText = "unlock tables;";
command.ExecuteNonQuery();
connection.Close();
// DBHelper.CloseConnection();
}
return(result);
}
public Cart PurchaseCart(string email, bool purchase)
{
List <ProductUpdate> updates = new List <ProductUpdate>();
MySqlCommand command1 = null;
MySqlCommand command2 = null;
MySqlDataReader mysql_datareader = null;
try
{
// Get Quantity of item in database
string query1 = "SELECT product_id, quantity FROM cart WHERE user_id = (@user_id) AND purchased = 0;";
command1 = m_dbConnection.CreateCommand();
command1.Connection.Open();
command1.CommandText = query1;
command1.Parameters.Add("@user_id", MySqlDbType.String).Value = email;
mysql_datareader = command1.ExecuteReader();
ProductUpdate temp = new ProductUpdate();
while (mysql_datareader.Read())
{
temp.ProductId = mysql_datareader.GetInt32(0);
temp.QuantityToBeRemoved = mysql_datareader.GetInt32(1);
//*************************
//* Debug Code *
//*************************
//Console.WriteLine("\n\n\n\n****************************************************************************");
//Console.WriteLine(temp);
//*************************
updates.Add(temp);
temp = new ProductUpdate();
}
if (purchase)
{
command1.Connection?.Close();
string query2 = "UPDATE cart SET purchased = 1 WHERE user_id = (@user_id) AND purchased = 0;";
command2 = m_dbConnection.CreateCommand();
command2.Connection.Open();
command2.CommandText = query2;
command2.Parameters.Add("@user_id", MySqlDbType.String).Value = email;
int row_affected = command2.ExecuteNonQuery();
}
// Console.Out.WriteLine("Rows affected:\t" + row_affected);
}
catch (Exception e)
{
Console.Out.WriteLine("\n\n**********************************************************************");
Console.Out.WriteLine(e.Message);
Console.Out.WriteLine(e.InnerException);
Console.Out.WriteLine(e.Source);
Console.Out.WriteLine("**********************************************************************\n\n");
}
finally
{
mysql_datareader?.Close();
command1?.Connection?.Close();
command2?.Connection?.Close();
}
return(new Cart(email, updates));
}
//*****************************************************************************************
public void AddProductToCart(string email, ProductUpdate productUpdate)
{
int ProductId = productUpdate.ProductId;
int Quantity = productUpdate.QuantityToBeRemoved;
MySqlCommand command = null;
MySqlDataReader mysql_datareader = null;
try
{
// set if item exits
command = m_dbConnection.CreateCommand();
command.Connection.Open();
string query0 = "SELECT * FROM cart WHERE user_id = (@userID) and product_id = (@productID) and purchased = false;";
command.CommandText = query0;
command.Parameters.Add("@userID", MySqlDbType.String).Value = email;
command.Parameters.Add("@productID", MySqlDbType.Int32).Value = ProductId;
mysql_datareader = command.ExecuteReader();
ProductUpdate temp = new ProductUpdate();
temp.ProductId = -1;
temp.QuantityToBeRemoved = -1;
while (mysql_datareader.Read())
{
temp.ProductId = mysql_datareader.GetInt32(2);
temp.QuantityToBeRemoved = mysql_datareader.GetInt32(3);
}
command.Connection.Close();
command = null;
if (temp.ProductId == -1)
{
// Get Quantity of item in database
string query1 = "INSERT into cart( user_id, product_id, quantity, purchased ) VALUES ( (@user_id), (@product_id), (@quantity), (@purchased) );";
command = m_dbConnection.CreateCommand();
command.Connection.Open();
command.CommandText = query1;
command.Parameters.Add("@user_id", MySqlDbType.String).Value = email;
command.Parameters.Add("@product_id", MySqlDbType.Int32).Value = ProductId;
command.Parameters.Add("@quantity", MySqlDbType.Int32).Value = Quantity;
command.Parameters.Add("@purchased", MySqlDbType.Int32).Value = false;
int rows_affected = command.ExecuteNonQuery();
}
else
{
string query1 = "UPDATE cart SET quantity = (@newQuantity) WHERE product_id = (@productID) AND user_id = (@userID) ;";
command = m_dbConnection.CreateCommand();
command.Connection.Open();
command.CommandText = query1;
command.Parameters.Add("@productID", MySqlDbType.Int32).Value = ProductId;
command.Parameters.Add("@newQuantity", MySqlDbType.Int32).Value = Quantity + temp.QuantityToBeRemoved;
command.Parameters.Add("@userID", MySqlDbType.String).Value = email;
int rows_affected = command.ExecuteNonQuery();
}
}
catch (Exception e)
{
Console.Out.WriteLine("\n\n**********************************************************************");
Console.Out.WriteLine(e.Message);
Console.Out.WriteLine(e.InnerException);
Console.Out.WriteLine(e.Source);
Console.Out.WriteLine("**********************************************************************\n\n");
}
finally
{
command?.Connection?.Close();
mysql_datareader?.Close();
}
}
public void Process(Job job)
{
_logger.LogInformation($"Execution : {job.Name}");
if (!job.Scheduled)
{
job.Scheduled = true;
}
var optionsBuilder = new DbContextOptionsBuilder <SQLServerContext>();
var connectionSQLServer = "Server=localhost;Database=worker-db;User Id=sa;Password=SqlExpress123;";
optionsBuilder.UseSqlServer(connectionSQLServer);
using (var db = new SQLServerContext(optionsBuilder.Options))
{
try
{
db.Jobs.Update(job);
db.SaveChanges();
}
catch (Exception ex)
{
_logger.LogError($"Erro ao atualizar registro na tabela JOB - Erro: {ex.Message}");
}
}
var jobResult = new JobResult {
JobName = job.Name
};
var connectionMySql = "Server=localhost;Database=hubcommerce-db;User=root;Password=123;";
using (MySqlConnection conn = new MySqlConnection(connectionMySql))
{
var query = job.SqlQuery.ToLower();
conn.Open();
using (MySqlCommand cmd = conn.CreateCommand())
{
MySqlDataReader reader = null;
try
{
if (job.TimeoutSeconds.HasValue)
{
cmd.CommandTimeout = job.TimeoutSeconds.Value;
}
cmd.CommandText = query;
Stopwatch sw = new Stopwatch();
sw.Start();
reader = cmd.ExecuteReader();
sw.Stop();
var r = Serialize(reader);
var json = JsonConvert.SerializeObject(r);
_logger.LogInformation($"Query executado com sucesso em {sw.Elapsed}");
}
catch (MySqlException ex)
{
switch (ex.Number)
{
case -1:
_logger.LogError($"O tempo limite do comando expirou, o tempo limite configurado para {job.TimeoutSeconds ?? 0} segundos.");
break;
default:
_logger.LogError($"Erro ao consultar a query: {query} - Erro {ex.Message}");
break;
}
}
catch (Exception ex)
{
_logger.LogError($"Erro ao consultar a query: {query} - Erro {ex.Message}");
}
finally
{
reader?.Close();
conn?.Close();
}
}
}
using (MySqlConnection conn = new MySqlConnection(connectionMySql))
{
var query = job.SqlQuery.ToLower();
Stopwatch sw = new Stopwatch();
sw.Start();
_logger.LogInformation($"Executar query: {query}");
try
{
var result = conn.Query(query) as IEnumerable <IDictionary <string, object> >;
result = result.Select(r => r.Distinct().ToDictionary(d => d.Key, d => d.Value));
if (result.Count() > 0)
{
var json = JsonConvert.SerializeObject(result);
_logger.LogInformation($"JSON: {json}");
Thread.Sleep(5000);
sw.Stop();
var now = DateTime.Now;
jobResult.ResultJson = json;
jobResult.DateExecuted = now;
jobResult.Runtime = sw.Elapsed;
jobResult.DateExpires = now.AddMinutes(10);
}
else
{
sw.Stop();
_logger.LogInformation("Nenhum registro encontrado");
}
}
catch (Exception ex)
{
sw.Stop();
_logger.LogError($"Erro ao consultar a query: {query} - Erro {ex.Message}");
}
}
using (var db = new SQLServerContext(optionsBuilder.Options))
{
try
{
db.JobsResult.Add(jobResult);
db.SaveChanges();
}
catch (Exception ex)
{
_logger.LogError($"Erro ao inserir registro na tabela JOBRESULT - Erro: {ex.Message}");
}
}
}
private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
string sql4 = "select workid from work where workname =" + "'" + comboBox3.Text + "'";
int workbzid = 5;
MySqlDataReader rd4 = msc.getDataFromTable(sql4);
while (rd4.Read())
{
workbzid = int.Parse(rd4["workid"].ToString());
}
rd4.Close();
if (comboBox2.Text.Equals("所有"))
{
comboBox1.Items.Clear();
comboBox4.Items.Clear();
comboBox4.Items.Add("所有");
string sql2 = "select equipment.Eid from equipment where workid = " + "'" + workbzid + "'";
MySqlDataReader rd2 = msc.getDataFromTable(sql2);
while (rd2.Read())
{
comboBox4.Items.Add(rd2["Eid"].ToString());
}
rd2.Close();
if (comboBox4.Items.Count > 0)
{
comboBox4.Text = comboBox4.Items[0].ToString();
}
comboBox1.Items.Clear();
comboBox1.Items.Add("所有");
string sql3 = "select Sid from dsensor where workid = " + "'" + workbzid + "'";
MySqlDataReader rd3 = msc.getDataFromTable(sql3);
while (rd3.Read())
{
comboBox1.Items.Add(rd3["Sid"].ToString());
}
rd3.Close();
if (comboBox1.Items.Count > 0)
{
comboBox1.Text = comboBox1.Items[0].ToString();
}
}
else
{
string sql6 = "select Etype from equiptype where Ename =" + "'" + comboBox2.Text + "'";
int ebzid = 3;
MySqlDataReader rd6 = msc.getDataFromTable(sql6);
while (rd6.Read())
{
ebzid = int.Parse(rd6["Etype"].ToString());
}
rd6.Close();
comboBox4.Items.Clear();
comboBox4.Items.Add("所有");
string sql5 = "select equipment.Eid from equipment where equipment.Etype = " + "'" + ebzid + "' AND workid = " + "'" + workbzid + "'";
MySqlDataReader rd5 = msc.getDataFromTable(sql5);
while (rd5.Read())
{
comboBox4.Items.Add(rd5["Eid"].ToString());
}
rd5.Close();
if (comboBox4.Items.Count > 0)
{
comboBox4.Text = comboBox4.Items[0].ToString();
}
comboBox1.Items.Clear();
comboBox1.Items.Add("所有");
if (comboBox1.Items.Count > 0)
{
comboBox1.Text = comboBox1.Items[0].ToString();
}
}
}
catch (Exception ex)
{
}
}