public int fjöldi_rada()
{
MySqlConnection connection;
string connectionString = "Server=10.0.105.33;Database=Leikur;Uid=first;Pwd=first;";
connection = new MySqlConnection(connectionString);
connection.Open();
string query = @"SELECT * FROM spilari";
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.ExecuteNonQuery();
MySqlDataReader queryCommandReader = cmd.ExecuteReader();
DataTable dataTable = new DataTable();
dataTable.Load(queryCommandReader);
MySqlDataAdapter adapter = new MySqlDataAdapter();
DataSet ds = new DataSet();
adapter.SelectCommand = cmd;
adapter.Fill(ds, "SQL Temp Table");
adapter.Dispose();
cmd.Dispose();
return ds.Tables[0].Rows.Count;
}
public void TestMultiPacket()
{
int len = 20000000;
st.suExecSQL("SET GLOBAL max_allowed_packet=64000000");
// currently do not test this with compression
if (st.conn.UseCompression) return;
using (MySqlConnection c = new MySqlConnection(st.GetConnectionString(true)))
{
c.Open();
byte[] dataIn = Utils.CreateBlob(len);
byte[] dataIn2 = Utils.CreateBlob(len);
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?id, NULL, ?blob, NULL )", c);
cmd.CommandTimeout = 0;
cmd.Parameters.Add(new MySqlParameter("?id", 1));
cmd.Parameters.Add(new MySqlParameter("?blob", dataIn));
cmd.ExecuteNonQuery();
cmd.Parameters[0].Value = 2;
cmd.Parameters[1].Value = dataIn2;
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM Test";
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
byte[] dataOut = new byte[len];
long count = reader.GetBytes(2, 0, dataOut, 0, len);
Assert.Equal(len, count);
int i = 0;
try
{
for (; i < len; i++)
Assert.Equal(dataIn[i], dataOut[i]);
}
catch (Exception)
{
int z = i;
}
reader.Read();
count = reader.GetBytes(2, 0, dataOut, 0, len);
Assert.Equal(len, count);
for (int x = 0; x < len; x++)
Assert.Equal(dataIn2[x], dataOut[x]);
}
}
}
public void BigIntAutoInc()
{
execSQL("DROP TABLE IF EXISTS test");
execSQL("CREATE TABLE test(ID bigint unsigned AUTO_INCREMENT NOT NULL PRIMARY KEY, name VARCHAR(20))");
MySqlCommand cmd = new MySqlCommand("INSERT INTO test VALUES (@id, 'boo')", conn);
ulong val = UInt64.MaxValue;
val -= 100;
cmd.Parameters.AddWithValue("@id", val);
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO test (name) VALUES ('boo2')";
cmd.ExecuteNonQuery();
}
public void InvalidCast()
{
MySqlConnection con = st.rootConn;
string sql = @"drop function if exists MyTwice; create function MyTwice( val int ) returns int begin return val * 2; end;";
MySqlCommand cmd = new MySqlCommand(sql, con);
cmd.ExecuteNonQuery();
cmd.CommandText = "drop procedure if exists spMyTwice; create procedure spMyTwice( out result int, val int ) begin set result = val * 2; end;";
cmd.ExecuteNonQuery();
try
{
cmd.CommandText = "drop user 'tester2'@'localhost'";
cmd.ExecuteNonQuery();
}
catch (Exception)
{
}
cmd.CommandText = "CREATE USER 'tester2'@'localhost' IDENTIFIED BY '123';";
cmd.ExecuteNonQuery();
cmd.CommandText = "grant execute on function `MyTwice` to 'tester2'@'localhost';";
cmd.ExecuteNonQuery();
cmd.CommandText = "grant execute on procedure `spMyTwice` to 'tester2'@'localhost'";
cmd.ExecuteNonQuery();
cmd.CommandText = "grant select on table mysql.proc to 'tester2'@'localhost'";
cmd.ExecuteNonQuery();
cmd.CommandText = "flush privileges";
cmd.ExecuteNonQuery();
MySqlConnection con2 = new MySqlConnection(st.rootConn.ConnectionString);
con2.Settings.UserID = "tester2";
con2.Settings.Password = "******";
// Invoke the function
cmd.Connection = con2;
con2.Open();
cmd.CommandText = "MyTwice";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new MySqlParameter("val", System.DBNull.Value));
cmd.Parameters.Add("@p", MySqlDbType.Int32);
cmd.Parameters[1].Direction = ParameterDirection.ReturnValue;
cmd.Parameters[0].Value = 20;
cmd.ExecuteNonQuery();
con2.Close();
Assert.Equal(cmd.Parameters[1].Value, 40);
con2.Open();
cmd.CommandText = "spMyTwice";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.Add(new MySqlParameter("result", System.DBNull.Value));
cmd.Parameters.Add("val", MySqlDbType.Int32);
cmd.Parameters[0].Direction = ParameterDirection.Output;
cmd.Parameters[1].Value = 20;
cmd.ExecuteNonQuery();
con2.Close();
Assert.Equal(cmd.Parameters[0].Value, 40);
}
private object CommandTypeSelector(RunType runSelector)
{
try
{
switch (runSelector)
{
case RunType.Insert:
Command?.ExecuteScalar();
return(Command?.LastInsertedId);
case RunType.Normal:
case RunType.Fast:
return(Command?.ExecuteNonQuery());
default:
return(null);
}
}
catch
{
Client?.GetConnectionHandler()?.SetOpened();
return(null);
}
}
private void button1_Click(object sender, EventArgs e)
{
string a = title.Text;
string t= dateTimePicker1.Text;
string b = content.Text;
MySqlConnection conn = null;
MySqlCommand cmd = null;
string sql = "insert into note()";
MySQLDataAdapter mda = new MySQLDataAdapter(sql,conn);
try
{
conn = base.GetConn();
conn.Open();
cmd = new MySqlCommand(sql, conn);
int i = cmd.ExecuteNonQuery();
return i;
conn.Close();
}
catch (Exception)
{
throw;
}
}
static void Main(string[] args)
{
string DataProviderString = ConfigurationManager.AppSettings["provider"];
DataProvider dp = DataProvider.None;
HardDriveAnalyzer hda = new HardDriveAnalyzer();
MySqlConnection Mcon = new MySqlConnection();
Mcon.Host = "localhost";
Mcon.Port = 3306;
Mcon.UserId = "root";
Mcon.Password = "******";
MySqlCommand Mcmd = new MySqlCommand();
Mcmd.CommandText = "INSERT INTO fenix.hosts(SystemType) VALUES('jjjj')";
Mcmd.Connection = Mcon;
Mcon.Open();
try
{
int aff = Mcmd.ExecuteNonQuery();
Console.WriteLine(aff + " rows were affected.");
}
catch
{
Console.WriteLine("Error encountered during INSERT operation.");
}
Console.WriteLine("mySQL version: {0}", Mcon.ServerVersion);
Console.WriteLine("mySQL ping: {0}", Mcon.PingInterval);
Console.ReadLine();
}
public static void insertPagamenti(Pagamenti item)
{
using (MySqlConnection cnMySql = new MySqlConnection(connectionString))
{
cnMySql.Open();
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("INSERT INTO antique.pagamenti(`id_pagamenti`, `data_ultimo_pagamento`, `totale_pagamento`, `numero_fattura`, `anno_fattura`, `tipo_fattura`, `numregpn`, `annocom`)");
sb.Append("VALUES ");
sb.Append("(?id_pagamenti, ?data_ultimo_pagamento, ?totale_pagamento, ?numero_fattura, ?anno_fattura, ?tipo_fattura, ?numregpn, ?annocom)");
using (MySqlCommand cmd = new MySqlCommand(sb.ToString(), cnMySql))
{
try
{
cmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
EventLogger.LogError("Errore durante l'inserimento dell'oggetto Pagamenti nel database.", ex);
}
}
cnMySql.Close();
}
}
public string AddUser(string ime, string ura, string minuta)
{
try
{
string myConnection = "SERVER=studsrv.uni-mb.si;" + "DATABASE=varnepoti;" + "UID=ronzyfonzy;" + "PASSWORD=snopy02;";
connect = new MySqlConnection(myConnection);
connect.Open();
/*maxInserts = connect.CreateCommand();
maxInserts.CommandText = "SELECT MAX(id) AS max FROM EXT_REMINDER;";
dataReader = maxInserts.ExecuteReader();
dataReader.Read();
int max = Convert.ToInt32(dataReader["max"].ToString());
dataReader.Close();*/
insertAlarm = connect.CreateCommand();
//INSERT INTO `EXT_REMINDER` (`ime`, `ura`, `minuta`) VALUES ('test1', '23', '12')
//insertAlarm.CommandText = "INSERT INTO EXT_REMINDER VALUES(" + 1 + ", '" + ime + "', '" + ura + "', '" + minuta + "');";
insertAlarm.CommandText = "INSERT INTO `EXT_REMINDER` (`ime`, `ura`, `minuta`) VALUES ('" + ime + "', '" + ura + "', '" + minuta + "');";
insertAlarm.ExecuteNonQuery();
connect.Close();
return "narejeno";
}
catch (Exception e)
{
return "ni_narejeno";
}
}
public void EscapedBackslash()
{
execSQL("CREATE TABLE Test(id INT, name VARCHAR(20))");
MySqlCommand cmd = new MySqlCommand(@"INSERT INTO Test VALUES (1, '\\=\\')", conn);
cmd.ExecuteNonQuery();
}
public void AutoIncrement()
{
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test (testID int(11) NOT NULL auto_increment, testName varchar(100) default '', " +
"PRIMARY KEY (testID)) ENGINE=InnoDB DEFAULT CHARSET=latin1");
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (NULL, 'test')", conn);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT @@IDENTITY as 'Identity'";
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
reader.Read();
int ident = Int32.Parse(reader.GetValue(0).ToString());
Assert.AreEqual(1, ident);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (reader != null)
reader.Close();
}
}
public void LastInsertid()
{
execSQL("DROP TABLE Test");
execSQL("CREATE TABLE Test(id int auto_increment, name varchar(20), primary key(id))");
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES(NULL, 'test')", conn);
cmd.ExecuteNonQuery();
Assert.AreEqual(1, cmd.LastInsertedId);
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
reader.Read();
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (reader != null)
reader.Close();
}
Assert.AreEqual(2, cmd.LastInsertedId);
cmd.CommandText = "SELECT id FROM Test";
cmd.ExecuteScalar();
Assert.AreEqual(-1, cmd.LastInsertedId);
}
public static void updatePagamenti_gruppo(Pagamenti_gruppo item)
{
using (MySqlConnection cnMySql = new MySqlConnection(connectionString))
{
cnMySql.Open();
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("UPDATE antique.pagamenti_gruppo ");
sb.Append("SET ");
sb.Append("`id_pagamenti` = ?id_pagamenti, ");
sb.Append("`transaction_id` = ?transaction_id, ");
sb.Append("`tipo_pagamento` = ?tipo_pagamento, ");
sb.Append("`nome_esecutore` = ?nome_esecutore, ");
sb.Append("`email_esecutore` = ?email_esecutore, ");
sb.Append("`data_pagamento` = ?data_pagamento, ");
sb.Append("`importo_pagamento` = ?importo_pagamento, ");
sb.Append("WHERE id=?id;");
using (MySqlCommand cmd = new MySqlCommand(sb.ToString(), cnMySql))
{
//lista parametri comando
try
{
cmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
EventLogger.LogError("Errore durante l'aggiornamento dell'oggetto Pagamenti_gruppo nel database.", ex);
}
}
cnMySql.Close();
}
}
public void TestSequence()
{
MySqlCommand cmd = new MySqlCommand("insert into Test (id, name) values (?id, 'test')", conn);
cmd.Parameters.Add(new MySqlParameter("?id", 1));
for (int i = 1; i <= 8000; i++)
{
cmd.Parameters[0].Value = i;
cmd.ExecuteNonQuery();
}
int i2 = 0;
cmd = new MySqlCommand("select * from Test", conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Assert.AreEqual(i2 + 1, reader.GetInt32(0), "Sequence out of order");
i2++;
}
reader.Close();
Assert.AreEqual(8000, i2);
cmd = new MySqlCommand("delete from Test where id >= 100", conn);
cmd.ExecuteNonQuery();
}
}
public static void insertPagamenti_gruppo(Pagamenti_gruppo item)
{
using (MySqlConnection cnMySql = new MySqlConnection(connectionString))
{
cnMySql.Open();
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("INSERT INTO antique.pagamenti_gruppo(`id_pagamenti`, `transaction_id`, `tipo_pagamento`, `nome_esecutore`, `email_esecutore`, `data_pagamento`, `importo_pagamento`)");
sb.Append("VALUES ");
sb.Append("(?id_pagamenti, ?transaction_id, ?tipo_pagamento, ?nome_esecutore, ?email_esecutore, ?data_pagamento, ?importo_pagamento)");
using (MySqlCommand cmd = new MySqlCommand(sb.ToString(), cnMySql))
{
try
{
cmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
EventLogger.LogError("Errore durante l'inserimento dell'oggetto Pagamenti_gruppo nel database.", ex);
}
}
cnMySql.Close();
}
}
public void BinaryAndVarBinaryParameters()
{
if (Version < new Version(5, 0)) return;
execSQL("CREATE PROCEDURE spTest(OUT out1 BINARY(20), OUT out2 VARBINARY(20)) " +
"BEGIN SET out1 = 'out1'; SET out2='out2'; END");
MySqlCommand cmd = new MySqlCommand("spTest", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("out1", MySqlDbType.Binary);
cmd.Parameters[0].Direction = ParameterDirection.Output;
cmd.Parameters.Add("out2", MySqlDbType.VarBinary);
cmd.Parameters[1].Direction = ParameterDirection.Output;
if (prepare) cmd.Prepare();
cmd.ExecuteNonQuery();
byte[] out1 = (byte[])cmd.Parameters[0].Value;
Assert.AreEqual('o', out1[0]);
Assert.AreEqual('u', out1[1]);
Assert.AreEqual('t', out1[2]);
Assert.AreEqual('1', out1[3]);
out1 = (byte[])cmd.Parameters[1].Value;
Assert.AreEqual('o', out1[0]);
Assert.AreEqual('u', out1[1]);
Assert.AreEqual('t', out1[2]);
Assert.AreEqual('2', out1[3]);
}
public void Unicode()
{
if (version < new Version(4, 1)) return;
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test (u2 varchar(255) CHARACTER SET ucs2)");
MySqlConnection c = new MySqlConnection(conn.ConnectionString + ";charset=utf8");
c.Open();
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES ( CONVERT('困巫忘否役' using ucs2))", c);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM Test";
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
reader.Read();
string s1 = reader.GetString(0);
Assert.AreEqual("困巫忘否役", s1);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (reader != null) reader.Close();
c.Close();
}
}
public void Simple()
{
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test (id INT, dec1 DECIMAL(5,2), name VARCHAR(100))");
execSQL("INSERT INTO Test VALUES (1, 345.12, 'abcd')");
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES(1,345.12,'abcd')", conn);
cmd.Prepare();
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM Test";
cmd.Prepare();
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
Assert.IsTrue(reader.Read());
Assert.AreEqual(1, reader.GetInt32(0));
Assert.AreEqual(345.12, reader.GetDecimal(1));
Assert.AreEqual("abcd", reader.GetString(2));
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (reader != null) reader.Close();
}
}
public void CommitAfterConnectionDead()
{
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test(id INT, name VARCHAR(20))");
string connStr = GetConnectionString(true) + ";pooling=false";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlTransaction trans = c.BeginTransaction();
using (MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (1, 'boo')", c))
{
cmd.ExecuteNonQuery();
}
KillConnection(c);
try
{
trans.Commit();
Assert.Fail("Should have thrown an exception");
}
catch (Exception ex)
{
}
Assert.AreEqual(ConnectionState.Closed, c.State);
c.Close(); // this should work even though we are closed
}
}
public void Blobs()
{
execSQL("CREATE TABLE Test (id INT, blob1 LONGBLOB, text1 LONGTEXT)");
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?id, ?blob1, ?text1)", conn);
byte[] bytes = Utils.CreateBlob(400000);
string inStr = "This is my text";
cmd.Parameters.AddWithValue("?id", 1);
cmd.Parameters.AddWithValue("?blob1", bytes);
cmd.Parameters.AddWithValue("?text1", inStr);
cmd.Prepare();
int count = cmd.ExecuteNonQuery();
Assert.AreEqual(1, count);
cmd.CommandText = "SELECT * FROM Test";
cmd.Prepare();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
Assert.IsTrue(reader.Read());
Assert.AreEqual(1, reader.GetInt32(0));
Assert.AreEqual(bytes.Length, reader.GetBytes(1, 0, null, 0, 0));
byte[] outBytes = new byte[bytes.Length];
reader.GetBytes(1, 0, outBytes, 0, bytes.Length);
for (int x = 0; x < bytes.Length; x++)
Assert.AreEqual(bytes[x], outBytes[x]);
Assert.AreEqual(inStr, reader.GetString(2));
}
}
void TransactionScopeInternal(bool commit)
{
MySqlConnection c = new MySqlConnection(GetConnectionString(true));
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES ('a', 'name', 'name2')", c);
try
{
using (TransactionScope ts = new TransactionScope())
{
c.Open();
cmd.ExecuteNonQuery();
if (commit)
ts.Complete();
}
cmd.CommandText = "SELECT COUNT(*) FROM Test";
object count = cmd.ExecuteScalar();
Assert.AreEqual(commit ? 1 : 0, count);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (c != null)
{
c.Close();
}
}
}
public void ProblemCharsInSQLUTF8()
{
if (Version < new Version(4, 1)) return;
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), mt MEDIUMTEXT, " +
"PRIMARY KEY(id)) CHAR SET utf8");
using (MySqlConnection c = new MySqlConnection(GetConnectionString(true) + ";charset=utf8"))
{
c.Open();
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?id, ?text, ?mt)", c);
cmd.Parameters.AddWithValue("?id", 1);
cmd.Parameters.AddWithValue("?text", "This is my;test ? string–’‘’“”…");
cmd.Parameters.AddWithValue("?mt", "My MT string: ?");
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM Test";
using (MySqlDataReader reader = cmd.ExecuteReader())
{
Assert.IsTrue(reader.Read());
Assert.AreEqual(1, reader.GetInt32(0));
Assert.AreEqual("This is my;test ? string–’‘’“”…", reader.GetString(1));
Assert.AreEqual("My MT string: ?", reader.GetString(2));
}
}
}
private int ejecutarComando(string sql)
{
MySqlCommand myCommand = new MySqlCommand(sql,this.myConnection);
int afectadas = myCommand.ExecuteNonQuery();
myCommand.Dispose();
myCommand = null;
return afectadas;
}
public bool ExecuteNonQuery(string myExecuteQuery)
{
MySqlCommand myCommand = new MySqlCommand(myExecuteQuery, OpenConnection());
myCommand.ExecuteNonQuery();
myCommand.Connection.Close();
return true;
}
public bool ExecuteNonQuery(string myExecuteQuery, MySqlParameter[] mySqlParameters)
{
MySqlCommand myCommand = new MySqlCommand(myExecuteQuery, OpenConnection());
myCommand.Parameters.AddRange(mySqlParameters);
myCommand.ExecuteNonQuery();
myCommand.Connection.Close();
return true;
}
public override void Execute(string query)
{
try {
using (MySqlCommand cmd = new MySqlCommand(query, connection, transaction)) {
cmd.ExecuteNonQuery();
}
} catch (Exception e) {
System.IO.File.AppendAllText("MySQL_error.log", DateTime.Now + " " + query + "\r\n");
Server.ErrorLog(e);
}
}
public void Simple()
{
MySqlCommand cmd = new MySqlCommand("SET @v=1", conn);
try
{
cmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
Assert.IsTrue(ex.Message.Contains("Replicated"));
}
}
public void ErrorData()
{
MySqlCommand cmd = new MySqlCommand("SELEDT 1", conn);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Assert.AreEqual(1064, ex.Data["Server Error Code"]);
}
}
internal static void execute(string queryString, bool createDB = false)
{
using (var conn = new MySqlConnection(connString)) {
conn.Open();
if (!createDB) {
conn.ChangeDatabase(Server.MySQLDatabaseName);
}
using (MySqlCommand cmd = new MySqlCommand(queryString, conn)) {
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
public void BitTypeAsOutParameter()
{
execSQL(@"CREATE PROCEDURE `spTest`(out x bit(1))
BEGIN
Set x = 1; -- Outparameter value is 49
Set x = 0; -- Outparameter value is 48
END");
MySqlCommand cmd = new MySqlCommand("spTest", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("x", MySqlDbType.Bit).Direction = ParameterDirection.Output;
if (prepare) cmd.Prepare();
cmd.ExecuteNonQuery();
Assert.AreEqual(0, cmd.Parameters[0].Value);
}
public void ProviderNormalizingQuery()
{
GenericListener listener = new GenericListener();
StringBuilder sql = new StringBuilder("SELECT '");
for (int i = 0; i < 400; i++)
sql.Append("a");
sql.Append("'");
MySqlCommand cmd = new MySqlCommand(sql.ToString(), conn);
cmd.ExecuteNonQuery();
Assert.AreEqual(5, listener.Strings.Count);
Assert.IsTrue(listener.Strings[1].EndsWith("SELECT ?"));
}
/// <summary>
/// コマンドの実行
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Execution_btn_Click(object sender, EventArgs e)
{
SyncBindingSourceWithDataGridView();
MySqlConnection connection = MySQLConnector.Connect();
for (var i = 0; i < bindingDataTable.Rows.Count; ++i)
{
MySqlCommand cmd = null;
var dataRow = bindingDataTable.Rows[i];
var deleteColumnIndex = DataTable_dgv.ColumnCount - 1;
//削除CheckBoxはbindingSource
if (Convert.ToBoolean(DataTable_dgv.Rows[i].Cells[deleteColumnIndex].Value))
{
cmd = new MySqlCommand(CreateDeleteRowCmd(dataRow), connection);
cmd?.ExecuteNonQuery();
continue;
}
switch (dataRow.RowState)
{
case DataRowState.Modified:
cmd = new MySqlCommand(CreateModifyRowCmd(dataRow), connection);
break;
case DataRowState.Added:
cmd = new MySqlCommand(CreateAddRowCmd(dataRow), connection);
break;
default:
break;
}
cmd?.ExecuteNonQuery();
}
SetAdvanceDataTable();
}
public ActionResult Get(string callData)
{
const string Format = "http://logs.stage.oski.io/_plugin/kibana/elasticsearch/_msearch";
var myHttpWebRequest = (HttpWebRequest)WebRequest.Create(string.Format(Format));
// Set the 'Method' property of the 'Webrequest' to 'POST'.
myHttpWebRequest.Method = "POST";
// Create a new string object to POST data to the Url.
var json = getJson();
//var encoding = new ASCIIEncoding();
var dataInBytes = Encoding.ASCII.GetBytes(json);
// Set the content type of the data being posted.
myHttpWebRequest.ContentType = "application/json";
myHttpWebRequest.ContentLength = dataInBytes.Length;
myHttpWebRequest.Headers["kbn-version"] = "5.1.1";
// Set the content length of the string being posted.
var stream = myHttpWebRequest.GetRequestStream();
stream.Write(dataInBytes, 0, dataInBytes.Length);
Console.WriteLine("The value of 'ContentLength' property after sending the data is {0}", myHttpWebRequest.ContentLength);
// Close the Stream object.
stream.Close();
var response = (HttpWebResponse)myHttpWebRequest.GetResponse();
var responseStream = response.GetResponseStream();
StreamReader readStream = new StreamReader(responseStream, Encoding.UTF8);
string valueFromResponse = readStream.ReadToEnd();
JObject newJson = JObject.Parse(valueFromResponse);
int count = Convert.ToInt32(newJson["responses"][0]["hits"]["total"]);
var values = newJson["responses"][0]["hits"]["hits"];
List <CallData> list = new List <CallData>();
for (int i = 0; i < count; i++)
{
var employee_id = values[i]["_source"]["employee_id"].ToString();
var call_action = values[i]["_source"]["call_action"].ToString();
list.Add(new CallData()
{
employee_id = employee_id, call_action = call_action
});
}
MySqlConnection mySqlConnection = new MySqlConnection(_connection);
MySqlCommand mySqlCommand = mySqlConnection.CreateCommand();
try
{
mySqlConnection.Open();
for (int i = 0; i < list.Count; i++)
{
int init = 0;
int ack = 0;
if (list[i].call_action == "initiated")
{
init = 1;
}
else if (list[i].call_action == "acknowledged")
{
ack = 1;
}
mySqlCommand.CommandText = "Insert into EmpInitAckData(EmployeeId,init,ack,date) values( '" + list[i].employee_id + "','" + init + "','" + ack + "','" + DateTime.Today.ToString("yyyy-MM-dd") + "')";
try
{
mySqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
catch (Exception ex)
{
// return Ok("dude, I did what you told me to do!!");
}
return(Ok());
}
/*UpdateUser - Atualização de algum dado do usuário
* Atualiza senha, nome e pontuãção do usuário.
* Retorno String, retorna algum erro caso exista
* Parametros
* User - Nome do usuário a ser alterado
* AtualContext - Conexto da tela, para execução do Toast
* User - Nome do usuário para encontrar no banco de dados
* Pontos - Nova quantidade de pontos do usuário
* Senha - Nova Senha do usuário
* Email - Novo Email do usuário
* Nome - Novo Nome do usuário
* Tipo conta - Altera o tipo da conta do usuário.
*/
public string UpdateUser(Context AtualContext, string User, string Pontos = "", string Senha = "", string Email = "", string Nome = "", string TipoConta = "")
{
string cLog = "";
string cQuery = "";
dbMngmt Database = new dbMngmt();
MySqlCommand Command;
cQuery += "USE Savar; ";
cQuery += "UPDATE cliente";
cQuery += " SET ";
if (Senha != "")
{
cQuery += " senha = '" + Senha + " ";
if ((Pontos + Email + Nome + TipoConta).Length > 0)
{
cQuery += " , ";
}
}
if (Nome != "")
{
cQuery += " nome = '" + Nome + "' ";
if ((Pontos + Email + TipoConta).Length > 0)
{
cQuery += " , ";
}
}
if (Pontos != "")
{
cQuery += " pontos = " + Pontos + " ";
if ((Email + TipoConta).Length > 0)
{
cQuery += " , ";
}
}
if (TipoConta != "")
{
cQuery += " Tipo_conta = '" + TipoConta + " ";
if (Email != "")
{
cQuery += " , ";
}
}
if (Email != "")
{
cQuery += " email = '" + Email + "' ";
}
cQuery += "Where usuario = '" + User.TrimEnd().TrimStart().ToUpper() + "' ;";
Command = new MySqlCommand(cQuery, Database.GetDataBase());
try
{
if (Database.ConectionTest())
{
if (VerificaUsuario(AtualContext, User).Length == 1)
{
Command.ExecuteNonQuery();
}
else
{
cLog = "Não foi possível encontrar o usuário " + User.ToUpper() + "!";
}
}
else
{
cLog = "Erro de conexão com o banco de dados";
}
}
catch (MySqlException ex)
{
cLog = ex.ToString();
}
return(cLog);
}
private void btn_search_Click(object sender, EventArgs e)
{
//searching either accession no, title , oldest , latest
String sqlconstring = "Data Source = LOCALHOST; Initial Catalog = it_2d; username = root; password = '';Convert Zero Datetime=True";
MySqlConnection sqlconnect = new MySqlConnection(sqlconstring);
MySqlCommand sqlcommand = new MySqlCommand();
MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter();
DataSet DS = new DataSet();
sqlconnect.Open();
if (cmb_search.SelectedIndex == 0)
{
//searching by accession no.
sqlcommand.CommandText = "SELECT `b_no` as 'Accession No.', `b_medium` as 'Medium', `b_title` as 'Title', `b_author` as 'Author', `b_category` as 'Category', `b_copies` as 'Copies', `b_copyright` as 'Copyright', `b_dateadd` as 'Date Added' FROM `tbl_itemlist` WHERE b_no like('" + txt_saccno.Text + "') ";
sqlcommand.CommandType = CommandType.Text;
sqlcommand.Connection = sqlconnect;
sqlcommand.ExecuteNonQuery();
sqlDataAdapter.SelectCommand = sqlcommand;
sqlDataAdapter.Fill(DS, "KAIBIGAN");
dataGridView1.DataSource = DS;
dataGridView1.DataMember = "KAIBIGAN";
sqlconnect.Close();
}
else if (cmb_search.SelectedIndex == 1)
{
//searching by title
sqlcommand.CommandText = "SELECT `b_no` as 'Accession No.', `b_medium` as 'Medium', `b_title` as 'Title', `b_author` as 'Author', `b_category` as 'Category', `b_copies` as 'Copies', `b_copyright` as 'Copyright', `b_dateadd` as 'Date Added' FROM `tbl_itemlist` WHERE b_title like '%" + txt_stitle.Text + "%'";
sqlcommand.CommandType = CommandType.Text;
sqlcommand.Connection = sqlconnect;
sqlcommand.ExecuteNonQuery();
sqlDataAdapter.SelectCommand = sqlcommand;
sqlDataAdapter.Fill(DS, "KAIBIGAN");
dataGridView1.DataSource = DS;
dataGridView1.DataMember = "KAIBIGAN";
sqlconnect.Close();
}
else if (cmb_search.SelectedIndex == 2)
{
//searching by medium
sqlcommand.CommandText = "SELECT `b_no` as 'Accession No.', `b_medium` as 'Medium', `b_title` as 'Title', `b_author` as 'Author', `b_category` as 'Category', `b_copies` as 'Copies', `b_copyright` as 'Copyright', `b_dateadd` as 'Date Added' FROM `tbl_itemlist` WHERE b_medium like('" + cmb_smedium.Text + "') ";
sqlcommand.CommandType = CommandType.Text;
sqlcommand.Connection = sqlconnect;
sqlcommand.ExecuteNonQuery();
sqlDataAdapter.SelectCommand = sqlcommand;
sqlDataAdapter.Fill(DS, "KAIBIGAN");
dataGridView1.DataSource = DS;
dataGridView1.DataMember = "KAIBIGAN";
sqlconnect.Close();
}
else if (cmb_search.SelectedIndex == 3)
{
//searching by categories
sqlcommand.CommandText = "SELECT `b_no` as 'Accession No.', `b_medium` as 'Medium', `b_title` as 'Title', `b_author` as 'Author', `b_category` as 'Category', `b_copies` as 'Copies', `b_copyright` as 'Copyright', `b_dateadd` as 'Date Added' FROM `tbl_itemlist` WHERE b_category like('" + cmb_scategory.Text + "') ";
sqlcommand.CommandType = CommandType.Text;
sqlcommand.Connection = sqlconnect;
sqlcommand.ExecuteNonQuery();
sqlDataAdapter.SelectCommand = sqlcommand;
sqlDataAdapter.Fill(DS, "KAIBIGAN");
dataGridView1.DataSource = DS;
dataGridView1.DataMember = "KAIBIGAN";
sqlconnect.Close();
}
else if (cmb_search.SelectedIndex == 4)
{
//searching from oldest to latest
sqlcommand.CommandText = "SELECT `b_no` as 'Accession No.', `b_medium` as 'Medium', `b_title` as 'Title', `b_author` as 'Author', `b_category` as 'Category', `b_copies` as 'Copies', `b_copyright` as 'Copyright', `b_dateadd` as 'Date Added' FROM `tbl_itemlist` order by b_dateadd ";
sqlcommand.CommandType = CommandType.Text;
sqlcommand.Connection = sqlconnect;
sqlcommand.ExecuteNonQuery();
sqlDataAdapter.SelectCommand = sqlcommand;
sqlDataAdapter.Fill(DS, "KAIBIGAN");
dataGridView1.DataSource = DS;
dataGridView1.DataMember = "KAIBIGAN";
sqlconnect.Close();
}
else if (cmb_search.SelectedIndex == 5)
{
//searching from latest to oldest
sqlcommand.CommandText = "SELECT `b_no` as 'Accession No.', `b_medium` as 'Medium', `b_title` as 'Title', `b_author` as 'Author', `b_category` as 'Category', `b_copies` as 'Copies', `b_copyright` as 'Copyright', `b_dateadd` as 'Date Added' FROM `tbl_itemlist` order by b_dateadd desc";
sqlcommand.CommandType = CommandType.Text;
sqlcommand.Connection = sqlconnect;
sqlcommand.ExecuteNonQuery();
sqlDataAdapter.SelectCommand = sqlcommand;
sqlDataAdapter.Fill(DS, "KAIBIGAN");
dataGridView1.DataSource = DS;
dataGridView1.DataMember = "KAIBIGAN";
sqlconnect.Close();
}
}
public void Aligned_Timer_PushMessages(object sender, EventArgs e)
{
//if (uiTimer_aligned.Enabled && count_trigger < 30)
if (uiTimer_aligned.Enabled && count_trigger < 10)
{ //same as mysql_cmd2_btn click event
//selected record with confidence (40, 90], show as UnsureMessages
//string myConnection = "SERVER=localhost;DATABASE=mixed_initiative_exp1_practice;UID=Christine;PASSWORD=20150330;";
//MySqlConnection myConn = new MySqlConnection(myConnection);
//myConn.Open();
current_selection_range = generate_selection_range();
generate_from_table_range();
string cmd_string = "INSERT INTO unsure_messages(from_table_id, from_table, time, content, confidence, is_about_delivery) SELECT id_all, table_name, time, content, confidence, is_about_delivery FROM all_messages WHERE confidence > 40 and confidence <= 90 and is_processed = 0 and " + current_selection_range;
MySqlCommand cmd = new MySqlCommand(cmd_string, myConn);
MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
// string cmd1_string = "UPDATE unsure_messages SET from_table='all_messages' WHERE " + from_table_range_string;
// MySqlCommand cmd1 = new MySqlCommand(cmd1_string, myConn);
// cmd1.ExecuteNonQuery();
string cmd2_string = "UPDATE all_messages SET is_processed='1' WHERE confidence > 40 and confidence <= 90 and " + current_selection_range;
MySqlCommand cmd2 = new MySqlCommand(cmd2_string, myConn);
cmd2.ExecuteNonQuery();
MySqlCommand cmd3 = new MySqlCommand("SELECT id_unsure_messages, time, content, note, confidence, is_about_delivery FROM unsure_messages", myConn);
MySqlDataAdapter adp3 = new MySqlDataAdapter(cmd3);
DataSet ds = new DataSet();
adp3.Fill(ds, "LoadDataBinding_Unsure");
Unsure_Messages.DG1.Items.Refresh();
Unsure_Messages.DG1.DataContext = ds;
//myConn.Close();
write_subtasktime(System.DateTime.Now, current_task_type, count_trigger, "System", "Push_messages", Unsure_Messages.DG1.Items.Count);
uiTimer_aligned.Interval = interval_ms[count_user_click];
update_count_trigger_and_messages();
system_push[count_trigger] = System.DateTime.Now;
//Console.Beep(4200, 50);
// BackgroundBeep.Beep();
}
//else if (count_trigger == 30)
else if (count_trigger == 10)
{
uiTimer_aligned.Tick -= Aligned_Timer_PushMessages;
uiTimer_aligned.Stop();
Aligned_timer_flag = 0;
//count_trigger = 0; count_user_click = 0;
}
}
public static void insertPacijent(string Ime, string Prezime, string GodRodj, string Adresa, string BolestiRizika)
{
MySqlConnection conn = new MySqlConnection(MyConnection2);
//proveravam da li vec postoji
List <string> rows = new List <string>();
rows = select5(
"SELECT ime, prezime, god_rodj, adresa, bolesti_rizika FROM pacijent" +
" WHERE ime='" + Ime + "' " +
"AND prezime='" + Prezime + "' " +
"AND god_rodj ='" + GodRodj + "' " +
"AND adresa='" + Adresa + "'" +
"AND bolesti_rizika='" + BolestiRizika + "'"
);
if (rows == null)
{
try
{
var sqlCommand = "INSERT INTO pacijent (ime, prezime, god_rodj, adresa, bolesti_rizika) " +
"VALUES (@ime, @prezime, @godRodj, @adresa, @bolestiRizika)";
conn.Open(); //uspostavlja vezu sa bazom
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = sqlCommand;
// da ne bi ubacivao prazne
if (Ime == "")
{
Ime = null;
}
if (Prezime == "")
{
Prezime = null;
}
if (GodRodj == "")
{
GodRodj = null;
}
if (Adresa == "")
{
Adresa = null;
}
if (BolestiRizika == "")
{
BolestiRizika = null;
}
comm.Parameters.AddWithValue("@ime", Ime);
comm.Parameters.AddWithValue("@prezime", Prezime);
comm.Parameters.AddWithValue("@godRodj", GodRodj);
comm.Parameters.AddWithValue("@adresa", Adresa);
comm.Parameters.AddWithValue("@bolestiRizika", BolestiRizika);
int a = comm.ExecuteNonQuery(); //izvrsava naredbu
long id = comm.LastInsertedId;
if (a > 0)
{
conn.Close();
MessageBox.Show("Pacijent uspešno dodat u bazu!", "Uspeh", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception)
{
conn.Close();
MessageBox.Show("Greška u unosu!", "Greška", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
conn.Close();
}
}
else
{
conn.Close();
MessageBox.Show("Pacijent već postoji u bazi!", "Greška", MessageBoxButtons.OK, MessageBoxIcon.Hand);
}
}
public void Button_Click_Check(object sender, RoutedEventArgs e)
{
string name = textBoxname.Text.Trim();
string secondname = textBoxsecondname.Text.Trim();
string email = textBoxemail.Text.Trim();
string login = textBoxlogin.Text.Trim();
string pass = textBoxpass.Password.Trim();
string repeatpass = textBoxrepeatpass.Password.Trim();
string post = comboBoxpost.Text;
string appDir = Environment.CurrentDirectory;
string Opened = appDir + "/Sound/Opened.wav";
string Error = appDir + "/Sound/Error.wav";
string Gun = appDir + "/Sound/Gun.wav";
string Exit = appDir + "/Sound/Exit.wav";
Boolean isUserExists()
{
DB db = new DB();
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter();
MySqlCommand command = new MySqlCommand("SELECT * FROM `users`WHERE `name`=@N AND `secondname`=@sN AND `email`=@eU AND `post`=@PS", db.getConnection());
command.Parameters.Add("@N", MySqlDbType.VarChar).Value = textBoxname.Text;
command.Parameters.Add("@sN", MySqlDbType.VarChar).Value = textBoxsecondname.Text;
command.Parameters.Add("@eU", MySqlDbType.VarChar).Value = textBoxemail.Text;
command.Parameters.Add("@PS", MySqlDbType.VarChar).Value = comboBoxpost.Text;
adapter.SelectCommand = command;
adapter.Fill(table);
if (table.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
Boolean Check_login()
{
DB db = new DB();
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter();
MySqlCommand command1 = new MySqlCommand("SELECT * FROM `users` WHERE `login`=@log", db.getConnection());
command1.Parameters.Add("@log", MySqlDbType.VarChar).Value = textBoxlogin.Text;
adapter.SelectCommand = command1;
adapter.Fill(table);
if (table.Rows.Count > 0)
{
_mpBgr.Open(new Uri(@Error, UriKind.Absolute));
_mpBgr.Play();
Alarm.Text = "Такой логин уже используется";
Alarm.Visibility = Visibility.Visible;
return(true);
}
else
{
return(false);
}
}
if (post == "")
{
comboBoxpost.Background = new SolidColorBrush(Color.FromRgb(204, 0, 0));
comboBoxpost.ToolTip = "Это поле введено не корректно!";
_mpBgr.Open(new Uri(@Error, UriKind.Absolute));
_mpBgr.Play();
}
else
{
comboBoxpost.Background = new SolidColorBrush(Color.FromRgb(221, 221, 221));
comboBoxpost.ToolTip = "Это поле введено корректно!";
if (name != "")
{
textBoxname.ToolTip = "Это поле введено корректно!";
textBoxname.Background = new SolidColorBrush(Color.FromRgb(0, 220, 0));
}
else
{
textBoxname.ToolTip = "Это поле введено не корректно!";
textBoxname.Background = new SolidColorBrush(Color.FromRgb(204, 0, 0));
_mpBgr.Open(new Uri(@Error, UriKind.Absolute));
_mpBgr.Play();
}
if (secondname != "")
{
textBoxsecondname.ToolTip = "Это поле введено корректно!";
textBoxsecondname.Background = new SolidColorBrush(Color.FromRgb(0, 220, 0));
}
else
{
textBoxsecondname.ToolTip = "Это поле введено не корректно!";
textBoxsecondname.Background = new SolidColorBrush(Color.FromRgb(204, 0, 0));
_mpBgr.Open(new Uri(@Error, UriKind.Absolute));
_mpBgr.Play();
}
if (email == "")
{
textBoxemail.ToolTip = "Это поле введено не корректно!";
textBoxemail.Background = new SolidColorBrush(Color.FromRgb(204, 0, 0));
_mpBgr.Open(new Uri(@Error, UriKind.Absolute));
_mpBgr.Play();
}
if (login == "")
{
_mpBgr.Open(new Uri(@Error, UriKind.Absolute));
_mpBgr.Play();
textBoxlogin.ToolTip = "Это поле введено не корректно!";
textBoxlogin.Background = new SolidColorBrush(Color.FromRgb(204, 0, 0));
}
if (pass == "")
{
_mpBgr.Open(new Uri(@Error, UriKind.Absolute));
_mpBgr.Play();
textBoxpass.ToolTip = "Это поле введено не корректно!";
textBoxpass.Background = new SolidColorBrush(Color.FromRgb(204, 0, 0));
}
if (repeatpass == "")
{
_mpBgr.Open(new Uri(@Error, UriKind.Absolute));
_mpBgr.Play();
textBoxrepeatpass.ToolTip = "Это поле введено не корректно!";
textBoxrepeatpass.Background = new SolidColorBrush(Color.FromRgb(204, 0, 0));
}
if (email.Length < 5)
{
_mpBgr.Open(new Uri(@Error, UriKind.Absolute));
_mpBgr.Play();
textBoxemail.ToolTip = "Это поле введено не корректно!";
textBoxemail.Background = new SolidColorBrush(Color.FromRgb(204, 0, 0));
}
else if (login.Length < 5 || login.Length > 15)
{
_mpBgr.Open(new Uri(@Error, UriKind.Absolute));
_mpBgr.Play();
textBoxemail.ToolTip = "Это поле введено корректно!";
textBoxemail.Background = new SolidColorBrush(Color.FromRgb(0, 220, 0));
textBoxlogin.ToolTip = "Это поле введено не корректно!";
textBoxlogin.Background = new SolidColorBrush(Color.FromRgb(204, 0, 0));
}
else if (pass.Length < 8 || !((pass.Contains("_") || (pass.Contains("@") || (pass.Contains("/") || (pass.Contains("#") || (pass.Contains("-"))))))))
{
_mpBgr.Open(new Uri(@Error, UriKind.Absolute));
_mpBgr.Play();
textBoxlogin.ToolTip = "Это поле введено корректно!";
textBoxemail.Background = new SolidColorBrush(Color.FromRgb(0, 220, 0));
textBoxlogin.Background = new SolidColorBrush(Color.FromRgb(0, 220, 0));
textBoxpass.ToolTip = "Это поле введено не корректно!";
textBoxpass.Background = new SolidColorBrush(Color.FromRgb(204, 0, 0));
}
else if (pass != repeatpass)
{
_mpBgr.Open(new Uri(@Error, UriKind.Absolute));
_mpBgr.Play();
textBoxpass.ToolTip = "Это поле введено корректно!";
textBoxemail.Background = new SolidColorBrush(Color.FromRgb(0, 220, 0));
textBoxlogin.Background = new SolidColorBrush(Color.FromRgb(0, 220, 0));
textBoxpass.Background = new SolidColorBrush(Color.FromRgb(0, 220, 0));
textBoxrepeatpass.ToolTip = "Это поле введено не корректно!";
textBoxrepeatpass.Background = new SolidColorBrush(Color.FromRgb(204, 0, 0));
}
else
{
textBoxname.Background = new SolidColorBrush(Color.FromRgb(0, 220, 0));
textBoxsecondname.Background = new SolidColorBrush(Color.FromRgb(0, 220, 0));
textBoxemail.Background = new SolidColorBrush(Color.FromRgb(0, 220, 0));
textBoxlogin.Background = new SolidColorBrush(Color.FromRgb(0, 220, 0));
textBoxpass.Background = new SolidColorBrush(Color.FromRgb(0, 220, 0));
textBoxrepeatpass.Background = new SolidColorBrush(Color.FromRgb(0, 220, 0));
comboBoxpost.Background = new SolidColorBrush(Color.FromRgb(0, 220, 0));
if (isUserExists())
{
if (Check_login())
{
}
else
{
DB db = new DB();
MySqlCommand command = new MySqlCommand("Update `users` SET login=@log, password=@pass WHERE name=@name AND secondname=@secondname AND email=@email", db.getConnection());
MySqlCommand command2 = new MySqlCommand("SELECT `id` FROM `users` WHERE email=@email1 ", db.getConnection());
command.Parameters.AddWithValue("@name", name);
command.Parameters.AddWithValue("@secondname", secondname);
command.Parameters.AddWithValue("@email", email);
command2.Parameters.Add("@email1", MySqlDbType.VarChar).Value = email;
db.openConnection();
using (MySqlDataReader reader = command2.ExecuteReader())
{
while (reader.Read())
{
var id = reader["id"].ToString();
int id_user = int.Parse(id);
id_user = id_user % 88;
string passnonhesh = textBoxpass.Password;
var hasher = new SHA512Managed();
var unhashed = System.Text.Encoding.Unicode.GetBytes(passnonhesh);
var hashed = hasher.ComputeHash(unhashed);
var hashedPassword = Convert.ToBase64String(hashed);
hashedPassword = hashedPassword.Insert(id_user, Salt);
command.Parameters.Add("@pass", MySqlDbType.VarChar).Value = hashedPassword;
}
}
command.Parameters.Add("@log", MySqlDbType.VarChar).Value = textBoxlogin.Text;
db.openConnection();
if (command.ExecuteNonQuery() == 1)
{
Alarm.Visibility = Visibility.Hidden;
_mpBgr.Open(new Uri(@Gun, UriKind.Absolute));
_mpBgr.Play();
db.closeConnection();
this.Close();
MainWindow MainWindow = new MainWindow();
MainWindow.Show();
}
db.closeConnection();
}
}
else
{
_mpBgr.Open(new Uri(@Error, UriKind.Absolute));
_mpBgr.Play();
Alarm.Text = "Вас либо нет в базе данных, либо вы неправильно ввели данные!Обращайтесь в поддержку.";
Alarm.Visibility = Visibility.Visible;
}
}
}
}
private void CreateNewDoctor(object sender, EventArgs e)
{
Messages msg = new Messages();
if (TextLastName.Text == "")
{
msg.DataError("Введите фамилию!");
TextLastName.Focus();
}
else if (TextFirstName.Text == "")
{
msg.DataError("Введите имя!");
TextFirstName.Focus();
}
else if (TextIIN.Text.Length != 12)
{
msg.DataError("Введите корректный ИИН!");
TextIIN.Focus();
}
else if (TextEmail.Text == "")
{
msg.DataError("Введите адрес электронной почты!");
TextEmail.Focus();
}
else if (TextPhone.Text == "")
{
msg.DataError("Введите номер телефона!");
TextPhone.Focus();
}
else if (TextPassword.Text == "")
{
msg.DataError("Введите пароль!");
TextPassword.Focus();
}
else if (TextPassword.Text != TextRepPassword.Text)
{
msg.DataError("Пароли не совпадают!");
TextPassword.Focus();
}
else if (CBSetSpec.Text == "")
{
msg.DataError("Выберите специализацию!");
CBSetSpec.Focus();
}
else
{
conn.Open();
sql = "SELECT COUNT(*) FROM DOCTOR WHERE DOC_EMAIL = '" + TextEmail.Text + "'";
MySqlCommand checkEmail = new MySqlCommand(sql, conn);
COUNT = Convert.ToInt32(checkEmail.ExecuteScalar());
if (COUNT == 0)
{
sql = "SELECT COUNT(*) FROM DOCTOR WHERE DOC_IIN = '" + TextIIN.Text + "'";
MySqlCommand checkIIN = new MySqlCommand(sql, conn);
COUNT = Convert.ToInt32(checkIIN.ExecuteScalar());
if (COUNT == 0)
{
sql = "INSERT INTO DOCTOR (DOC_LASTNAME, " +
"DOC_FIRSTNAME, " +
"DOC_PATRONYMIC, " +
"DOC_IIN, " +
"DOC_EMAIL, " +
"DOC_PHONE, " +
"DOC_PASSWORD, " +
"SPECIALISATION_ID_SPEC) " +
"VALUES ('" + TextLastName.Text + "', " +
"'" + TextFirstName.Text + "', " +
"'" + TextPatronymic.Text + "', " +
"'" + TextIIN.Text + "', " +
"'" + TextEmail.Text + "', " +
"'" + TextPhone.Text + "', " +
"'" + TextPassword.Text + "', " +
"" + idspec + ")";
MySqlCommand insdoc = new MySqlCommand(sql, conn);
if (insdoc.ExecuteNonQuery() == 1)
{
msg.WriteSuccess();
this.Close();
AdminMainForm amf = new AdminMainForm();
amf.Show();
conn.Close();
}
else
{
msg.WriteError();
conn.Close();
}
}
else
{
msg.DataError("ИИН уже существует в системе!");
conn.Close();
}
}
else
{
msg.DataError("Электронный адрес уже существует в системе!");
conn.Close();
}
}
}
/******************************************************************************************************************************
* *
* Agrega una nueva sucursal al catalogo *
* *
******************************************************************************************************************************/
public Boolean AgregaSucursal(DataTable sucursal)
{
Boolean Valor = false;
MySqlConnection cnObj = new MySqlConnection();
cnObj = objConexion.Conectar();
if (cnObj != null)
{
MySqlCommand cmdObj = new MySqlCommand();
cmdObj.Connection = cnObj;
string strSql;
foreach (DataRow dRow in sucursal.Rows)
{
strSql = "INSERT ";
strSql += "INTO ";
strSql += "sucursal ";
strSql += "(id_sucursal, ";
strSql += "id_empresa, ";
strSql += "sucursal, ";
strSql += "calle, ";
strSql += "num_ext, ";
strSql += "num_int, ";
strSql += "colonia, ";
strSql += "id_pais, ";
strSql += "id_estado, ";
strSql += "id_ciudad, ";
strSql += "cod_postal, ";
strSql += "telefono, ";
strSql += "extencion, ";
strSql += "responsable, ";
strSql += "correo, ";
strSql += "estatus) ";
strSql += "VALUES ";
strSql += "(" + dRow["id_sucursal"] + ", ";
strSql += "" + dRow["id_empresa"] + ", ";
strSql += "'" + dRow["sucursal"] + "', ";
strSql += "'" + dRow["calle"] + "', ";
strSql += "'" + dRow["num_ext"] + "', ";
strSql += "'" + dRow["num_int"] + "', ";
strSql += "'" + dRow["colonia"] + "', ";
strSql += "'" + dRow["pais"] + "', ";
strSql += "'" + dRow["estado"] + "', ";
strSql += "" + dRow["ciudad"] + ", ";
strSql += "'" + dRow["codigo_pos"] + "', ";
strSql += "'" + dRow["telefono"] + "', ";
strSql += "'" + dRow["extencion"] + "', ";
strSql += "'" + dRow["responsable"] + "', ";
strSql += "'" + dRow["correo"] + "', ";
strSql += "" + dRow["estatus"] + ") ";
cmdObj.CommandText = strSql;
try
{
cmdObj.ExecuteNonQuery();
Valor = true;
}catch (MySqlException ex)
{
MessageBox.Show("Error al Ingresar los Datos, " + ex.Message, "Error Critico", MessageBoxButton.OK);
Valor = false;
}
}
}
else
{
Valor = false;
}
return(Valor);
}
public int D_modificar_Usuario_Admin(Usuario usu)
{
String cadena = DConexion.cadena;
String sql = "UPDATE usuario SET CODIGO=@CODIGO,ENCARGADO=@ENCARGADO, USUARIO=@USUARIO,CONTRASENA=@CONTRASENA,TIPO=@TIPO,NOMBRES=@NOMBRES,APELLIDOS=@APELLIDOS,SEXO=@SEXO, DNI=@DNI, DIRECCION=@DIRECCION, REFERENCIA=@REFERENCIA,MOVISTAR=@MOVISTAR, CLARO=@CLARO, NEXTEL=@NEXTEL,TELEFONO=@TELEFONO,EMAIL=@EMAIL,FACEBOOK=@FACEBOOK,OCUPACION=@OCUPACION,ESTADO=@ESTADO WHERE SERIE = @SERIE";
cone = new MySqlConnection(cadena);
MySqlCommand com = new MySqlCommand(sql, cone);
int band;
cone.Open();
try
{
com.Parameters.AddWithValue("@CODIGO", usu.Codigo);
if (usu.Encargado == null)
{
com.Parameters.AddWithValue("@ENCARGADO", null);
}
else
{
com.Parameters.AddWithValue("@ENCARGADO", usu.Encargado.Serie);
}
if (usu.Usuario1 == null)
{
com.Parameters.AddWithValue("@USUARIO", null);
}
else
{
com.Parameters.AddWithValue("@USUARIO", usu.Usuario1);
}
if (usu.Contrasena == null)
{
com.Parameters.AddWithValue("@CONTRASENA", null);
}
else
{
com.Parameters.AddWithValue("@CONTRASENA", usu.Contrasena);
}
com.Parameters.AddWithValue("@TIPO", usu.Tipo);
com.Parameters.AddWithValue("@NOMBRES", usu.Nombres);
com.Parameters.AddWithValue("@APELLIDOS", usu.Apellidos);
com.Parameters.AddWithValue("@SEXO", usu.Sexo);
com.Parameters.AddWithValue("@DNI", usu.Dni);
com.Parameters.AddWithValue("@DIRECCION", usu.Direccion);
com.Parameters.AddWithValue("@REFERENCIA", usu.Referencia);
com.Parameters.AddWithValue("@MOVISTAR", usu.Movistar);
com.Parameters.AddWithValue("@CLARO", usu.Claro);
com.Parameters.AddWithValue("@NEXTEL", usu.Nextel);
com.Parameters.AddWithValue("@TELEFONO", usu.Telefono);
com.Parameters.AddWithValue("@EMAIL", usu.Email);
com.Parameters.AddWithValue("@FACEBOOK", usu.Facebook);
com.Parameters.AddWithValue("@OCUPACION", usu.Ocupacion);
com.Parameters.AddWithValue("@ESTADO", usu.Estado);
com.Parameters.AddWithValue("@SERIE", usu.Serie);
band = com.ExecuteNonQuery();
}
catch (Exception)
{
band = 0;
}
cone.Close();
return(band);
}
public static void ejecutar_sql(string sql, ref MySqlConnection cn, ref MySqlTransaction tr)
{
MySqlCommand cm = new MySqlCommand(sql, cn, tr);
cm.ExecuteNonQuery();
}
//修改数据库
public int nonSelect(string sql)
{
MySqlConnection conn = this.GetConn();
MySqlCommand cmmd = new MySqlCommand(sql, conn);//执行一条sql语句。
return cmmd.ExecuteNonQuery();
}
//////////M_SİPARİS TABLOSUNA VERİ EKLEDİK
private void Button3_Click(object sender, EventArgs e)
{
if (textBox5.Text != "" || textBox6.Text != "" || textBox7.Text != "" || textBox8.Text != "" || textBox11.Text != "")
{
// mysqlbaglan.Open();
cmd.Connection = mysqlbaglan;
cmd.CommandText = "Insert into m_siparis(m_id, siparis, miktar, kar) Values('" + textBox5.Text + "','" + textBox6.Text + "','" + textBox7.Text + "','" + textBox8.Text + "')";
string siparis = "", urunid = "";
int miktar = Convert.ToInt32(textBox7.Text), kid = 0, sayac_2 = 0;
double stok = 0, maliyet = 0, toplam = 0, fiyat = 0, tedarik = 0, element = 0;
double oran = 0;
double kar = Convert.ToDouble(textBox8.Text.ToString());
string tarih = textBox11.Text;
kar = kar / 100;
cmd.ExecuteNonQuery();
// mysqlbaglan.Close();
/* for (int i = 0; i < this.Controls.Count; i++)
* {
* if (Controls[i] is TextBox) Controls[i].Text = "";
* }*/
MySqlCommand cmd1 = new MySqlCommand("Select * From u_kimyasal,u_bilesen Where kimyasal_urun= @deger", mysqlbaglan);
cmd1.Parameters.AddWithValue("@deger", textBox6.Text);//&& b_id=1
MySqlDataReader dr = cmd1.ExecuteReader();
while (dr.Read())
{
kid = Convert.ToInt32(dr["k_id"].ToString());
siparis = dr["kimyasal_urun"].ToString();
stok = Convert.ToDouble(dr["stok"].ToString());
maliyet = Convert.ToDouble(dr["maliyet"].ToString());
element = Convert.ToDouble(dr["element"].ToString());
}
dr.Close();
cmd1.Dispose();
MySqlCommand cmd2 = new MySqlCommand("Select * From k_urun, k_fiyat Where urun_adi = @deger2", mysqlbaglan);
cmd2.Parameters.AddWithValue("@deger2", textBox6.Text);
MySqlDataReader dr2 = cmd2.ExecuteReader();
while (dr2.Read())
{
urunid = dr2["urun_id"].ToString();
toplam = Convert.ToDouble(dr2["toplam_maliyet"].ToString());
fiyat = Convert.ToDouble(dr2["satis_fiyati"].ToString());
}
dr2.Close();
cmd2.Dispose();
//bileşen sayısı
MySqlCommand cmd5 = new MySqlCommand("", mysqlbaglan);
cmd5.CommandText = "Select Count(b_id) From u_bilesen";
//Int64 hammadde = (Int64)cmd5.ExecuteScalar(); ;
//MySqlDataReader dr5 = cmd5.ExecuteReader();
/*while (dr5.Read())
* {
* hammadde = Convert.ToInt32(dr5["Count b_id"].ToString());
* }
* dr5.Close();*/
int hammadde = 3;
cmd5.Dispose();
int[] bid = new int[hammadde];
int[] mol = new int[hammadde];
MySqlCommand cmd6 = new MySqlCommand("", mysqlbaglan);
cmd6.CommandText = "Select Count(f_id) From t_firma ";
//Int64 firma = (Int64)cmd6.ExecuteScalar(); ;
/*MySqlDataReader dr6 = cmd6.ExecuteReader();
* while (dr6.Read())
* {
* firma = Convert.ToInt32(dr6["Count (f_id)"].ToString());
* }
* dr6.Close();*/
cmd6.Dispose();
int firma = 10;
int[] fid = new int[firma];
int[] t_stok = new int[firma];
int[] t_sfiyat = new int[firma];
string[] t_ulke = new string[firma];
int[] uzaklik = new int[firma];
int[] carpan = new int[firma];
int[] fid2 = new int[firma];
int[] t_stok2 = new int[firma];
int[] t_sfiyat2 = new int[firma];
string[] t_ulke2 = new string[firma];
double[] uzaklik2 = new double[firma];
double[] carpan2 = new double[firma];
if (stok >= miktar)
{
oran = (stok - miktar) / stok;
stok = stok * oran;
maliyet = maliyet * oran;
//textBox5.Text = oran.ToString();
/*toplam = toplam * oran;
* fiyat = fiyat * oran;*/
}
else if (miktar > stok)
{
tedarik = miktar - stok;//miktar=50 stok=30 tedarik=20
double tedarix = tedarik;
if (element == 2)
{
int sayac = 0, sayac3 = 0, toplamstoksayisi1 = 0;
for (int i = 0; i <= hammadde; i++)
{
MySqlCommand komut = new MySqlCommand("Select * From u_formul Where k_id='" + @kid + "'&& b_id='" + @i + "'", mysqlbaglan);
MySqlDataReader veri = komut.ExecuteReader();
while (veri.Read())
{
bid[sayac] = Convert.ToInt32(veri["b_id"].ToString());
mol[sayac] = Convert.ToInt32(veri["bilesen"].ToString());
sayac++;
}
veri.Close();
komut.Dispose();
}
double[] result = new double[2];
double son_fiyat = 0;
for (int k = 0; k < 2; k++)
{
tedarik = tedarix;
//tedarikçi bilgilerini çekme işlemi
sayac_2 = 0;
for (int i = 1; i < firma + 1; i++)
{
MySqlCommand komut1 = new MySqlCommand("Select * From tedarikci, t_firma Where h_id='" + bid[k] + "'&& fi_id='" + @i + "'&&f_id='" + @i + "'", mysqlbaglan);
MySqlDataReader veri1 = komut1.ExecuteReader();
while (veri1.Read())
{
fid[i - 1] = Convert.ToInt32(veri1["fi_id"].ToString());
t_stok[i - 1] = Convert.ToInt32(veri1["miktar"].ToString());
t_sfiyat[i - 1] = Convert.ToInt32(veri1["satis_fiyati"].ToString());
uzaklik[i - 1] = Convert.ToInt32(veri1["uzaklik"].ToString());
t_ulke[i - 1] = veri1["ulke"].ToString();
sayac_2++;
}
veri1.Close();
komut1.Dispose();
}
double[] sonuc = new double[firma];
int yli = 0, ysiz = 0;
double tedarik1 = tedarik;
for (int i = 0; i < firma; i++)
{
if (t_ulke[i] != "" && t_ulke[i] == "Turkiye")
{
carpan[i] = 2;
}
else if (t_ulke[i] != "" && t_ulke[i] != "Turkiye")
{
carpan[i] = 1;
}
}
for (int i = 0; i < firma; i++)
{
if (t_stok[i] != 0 && tedarik * mol[k] <= t_stok[i])
{
sonuc[i] = tedarik * mol[k] * t_sfiyat[i] + uzaklik[i] / carpan[i];
yli++;
}
else if (t_stok[i] != 0 && tedarik * mol[k] > t_stok[i])
{
sonuc[i] = t_stok[i] * t_sfiyat[i] + uzaklik[i] / carpan[i];
//tedarik1 -= t_stok[i];//100-5=95
ysiz++;
}
}
double[] yeterli = new double[yli];
double[] yetersiz = new double[ysiz];
int[] ysizstok = new int[ysiz];
int[] ylistok = new int[yli];
int a = 0, b = 0;
for (int i = 0; i < firma; i++)
{
if (t_stok[i] != 0 && tedarik * mol[k] <= t_stok[i])
{
yeterli[a] = sonuc[i];
ylistok[a] = t_stok[i];
a++;
}
else if (t_stok[i] != 0 && tedarik * mol[k] > t_stok[i])
{
yetersiz[b] = sonuc[i];
ysizstok[b] = t_stok[i];
b++;
}
}
//minliye göre yeterli stoklu olanların en küçüğü
double minli = yeterli[0];
int yeterli_en_kucuk = 0;
for (int i = 0; i < yli; i++)
{
if (yeterli[i] < minli)
{
minli = yeterli[i];
yeterli_en_kucuk = i;
}
}
double[] yli_birfi = new double[yli];
for (int j = 0; j < yli; j++)
{
yli_birfi[j] = yeterli[j] / tedarik;
}
double yli_minbirfi = yli_birfi[0];
int yli_en_kucuk_birfi = 0;
for (int j = 0; j < yli; j++)
{
if (yli_birfi[j] != 0 && yli_birfi[j] < yli_minbirfi)
{
yli_minbirfi = yli_birfi[j];
yli_en_kucuk_birfi = j;
}
}
//sonuç dizisine göre en küçük stok sayısına sahip tedarikçi maliyeti
for (int j = 0; j < firma; j++)
{
if (sonuc[j] == yeterli[yli_en_kucuk_birfi])
{
yeterli_en_kucuk = j;
}
}
//minsize göre yetersiz stoklu olanların en küçüğü
double minsiz = yetersiz[0];
int yetersiz_en_kucuk = 0;
for (int i = 0; i < ysiz; i++)
{
if (yetersiz[i] < minsiz)
{
minsiz = yetersiz[i];
yetersiz_en_kucuk = i;
}
}
double[] ysiz_birfi = new double[ysiz];
for (int j = 0; j < ysiz; j++)
{
ysiz_birfi[j] = yetersiz[j] / ysizstok[j];
}
double ysiz_minbirfi = ysiz_birfi[0];
int en_kucuk_birfi = 0;
for (int j = 0; j < ysiz; j++)
{
if (ysiz_birfi[j] != 0 && ysiz_birfi[j] < ysiz_minbirfi)
{
ysiz_minbirfi = ysiz_birfi[j];
en_kucuk_birfi = j;
}
}
//sonuc dizisine göre en küçük tedarik maliyeti indisi
for (int j = 0; j < firma; j++)
{
if (sonuc[j] == yetersiz[en_kucuk_birfi])
{
yetersiz_en_kucuk = j;
}
}
double yenimiktar = 0;
int c = 0;
int[] ysizfirma = new int[ysiz];
int[] ylifirma = new int[yli];
int firma_id = 0;
if (yli_minbirfi <= ysiz_minbirfi)
{
result[k] = sonuc[yeterli_en_kucuk];
//fid[yeterli_en_kucuk];
yenimiktar = t_stok[yeterli_en_kucuk] - tedarik * mol[k];
firma_id = fid[yeterli_en_kucuk];
mysqlbaglan.Close();
String guncelle5 = "Update tedarikci Set miktar='" + @yenimiktar + "' Where h_id='" + @bid[k] + "'&&fi_id='" + @firma_id + "'";
MySqlCommand komut5 = new MySqlCommand(guncelle5, mysqlbaglan);
MySqlDataReader MyReader5;
mysqlbaglan.Open();
MyReader5 = komut5.ExecuteReader();
//MessageBox.Show("değişim oldu!!!");
komut5.Dispose();
}
else if (yli_minbirfi > ysiz_minbirfi)
{
tedarik = tedarix;
sonuc[yetersiz_en_kucuk] = t_stok[yetersiz_en_kucuk] * t_sfiyat[yetersiz_en_kucuk] + uzaklik[yetersiz_en_kucuk] / carpan[yetersiz_en_kucuk];
yenimiktar = 0;
firma_id = fid[yetersiz_en_kucuk];
mysqlbaglan.Close();
String guncelle5 = "Update tedarikci Set miktar='" + @yenimiktar + "' Where h_id='" + @bid[k] + "'&&fi_id='" + @firma_id + "'";
MySqlCommand komut5 = new MySqlCommand(guncelle5, mysqlbaglan);
MySqlDataReader MyReader5;
mysqlbaglan.Open();
MyReader5 = komut5.ExecuteReader();
//MessageBox.Show("değişim oldu!!!");
komut5.Dispose();
tedarik = tedarik * mol[k] - t_stok[yetersiz_en_kucuk];
sonuc[yeterli_en_kucuk] = tedarik * t_sfiyat[yeterli_en_kucuk] + uzaklik[yeterli_en_kucuk] / carpan[yeterli_en_kucuk];
result[k] = sonuc[yetersiz_en_kucuk] + sonuc[yeterli_en_kucuk];
yenimiktar = t_stok[yeterli_en_kucuk] - tedarik;
firma_id = fid[yeterli_en_kucuk];
mysqlbaglan.Close();
String guncelle6 = "Update tedarikci Set miktar='" + @yenimiktar + "' Where h_id='" + @bid[k] + "'&&fi_id='" + @firma_id + "'";
MySqlCommand komut6 = new MySqlCommand(guncelle6, mysqlbaglan);
MySqlDataReader MyReader6;
mysqlbaglan.Open();
MyReader6 = komut6.ExecuteReader();
//MessageBox.Show("değişim oldu!!!");
komut6.Dispose();
}
}
//yeterli olan indisteki firmaya ulaş ve stok bilgisini güncelle
double sonmaliyet = result[0] + result[1] + tedarix;
son_fiyat = sonmaliyet + kar * sonmaliyet;
MySqlCommand ekle1 = new MySqlCommand();
ekle1.Connection = mysqlbaglan;
ekle1.CommandText = "Insert into k_fiyat(uretim_tarihi, urun_id, toplam_maliyet, satis_fiyati) Values('" + @tarih + "','" + @urunid + "','" + @sonmaliyet + "','" + @son_fiyat + "')";
ekle1.ExecuteNonQuery();
mysqlbaglan.Close();
stok = 0;
maliyet = 0;
toplam = result[0] + result[1] + tedarik;
fiyat = toplam * kar;
}
//Element sayısı 3 ise
else if (element == 3)
{
int sayac = 0;
for (int i = 0; i <= hammadde; i++)
{
MySqlCommand komut = new MySqlCommand("Select * From u_formul Where k_id='" + @kid + "'&& b_id='" + @i + "'", mysqlbaglan);
MySqlDataReader veri = komut.ExecuteReader();
while (veri.Read())
{
bid[sayac] = Convert.ToInt32(veri["b_id"].ToString());
mol[sayac] = Convert.ToInt32(veri["bilesen"].ToString());
sayac++;
}
veri.Close();
komut.Dispose();
}
double[] result = new double[3];
double son_fiyat = 0;
for (int k = 0; k < 3; k++)
{
tedarik = tedarix;
//tedarikçi bilgilerini çekme işlemi
sayac_2 = 0;
for (int i = 1; i < firma + 1; i++)
{
MySqlCommand komut1 = new MySqlCommand("Select * From tedarikci, t_firma Where h_id='" + bid[k] + "'&& fi_id='" + @i + "'&&f_id='" + @i + "'", mysqlbaglan);
MySqlDataReader veri1 = komut1.ExecuteReader();
while (veri1.Read())
{
fid[i - 1] = Convert.ToInt32(veri1["fi_id"].ToString());
t_stok[i - 1] = Convert.ToInt32(veri1["miktar"].ToString());
t_sfiyat[i - 1] = Convert.ToInt32(veri1["satis_fiyati"].ToString());
uzaklik[i - 1] = Convert.ToInt32(veri1["uzaklik"].ToString());
t_ulke[i - 1] = veri1["ulke"].ToString();
sayac_2++;
}
veri1.Close();
komut1.Dispose();
}
double[] sonuc = new double[firma];
int yli = 0, ysiz = 0;
double tedarik1 = tedarik;
for (int i = 0; i < firma; i++)
{
if (t_ulke[i] != "" && t_ulke[i] == "Turkiye")
{
carpan[i] = 2;
}
else if (t_ulke[i] != "" && t_ulke[i] != "Turkiye")
{
carpan[i] = 1;
}
}
for (int i = 0; i < firma; i++)
{
if (t_stok[i] != 0 && tedarik * mol[k] <= t_stok[i])
{
sonuc[i] = tedarik * mol[k] * t_sfiyat[i] + uzaklik[i] / carpan[i];
yli++;
}
else if (t_stok[i] != 0 && tedarik * mol[k] > t_stok[i])
{
sonuc[i] = t_stok[i] * t_sfiyat[i] + uzaklik[i] / carpan[i];
//tedarik1 -= t_stok[i];//100-5=95
ysiz++;
}
}
double[] yeterli = new double[yli];
double[] yetersiz = new double[ysiz];
int[] ysizstok = new int[ysiz];
int[] ylistok = new int[yli];
int a = 0, b = 0;
for (int i = 0; i < firma; i++)
{
if (t_stok[i] != 0 && tedarik * mol[k] <= t_stok[i])
{
yeterli[a] = sonuc[i];
ylistok[a] = t_stok[i];
a++;
}
else if (t_stok[i] != 0 && tedarik * mol[k] > t_stok[i])
{
yetersiz[b] = sonuc[i];
ysizstok[b] = t_stok[i];
b++;
}
}
//minliye göre yeterli stoklu olanların en küçüğü
double minli = yeterli[0];
int yeterli_en_kucuk = 0;
for (int i = 0; i < yli; i++)
{
if (yeterli[i] < minli)
{
minli = yeterli[i];
yeterli_en_kucuk = i;
}
}
double[] yli_birfi = new double[yli];
for (int j = 0; j < yli; j++)
{
yli_birfi[j] = yeterli[j] / tedarik;
}
double yli_minbirfi = yli_birfi[0];
int yli_en_kucuk_birfi = 0;
for (int j = 0; j < yli; j++)
{
if (yli_birfi[j] != 0 && yli_birfi[j] < yli_minbirfi)
{
yli_minbirfi = yli_birfi[j];
yli_en_kucuk_birfi = j;
}
}
//sonuç dizisine göre en küçük stok sayısına sahip tedarikçi maliyeti
for (int j = 0; j < firma; j++)
{
if (sonuc[j] == yeterli[yli_en_kucuk_birfi])
{
yeterli_en_kucuk = j;
}
}
//minsize göre yetersiz stoklu olanların en küçüğü
double minsiz = yetersiz[0];
int yetersiz_en_kucuk = 0;
for (int i = 0; i < ysiz; i++)
{
if (yetersiz[i] < minsiz)
{
minsiz = yetersiz[i];
yetersiz_en_kucuk = i;
}
}
double[] ysiz_birfi = new double[ysiz];
for (int j = 0; j < ysiz; j++)
{
ysiz_birfi[j] = yetersiz[j] / ysizstok[j];
}
double ysiz_minbirfi = ysiz_birfi[0];
int en_kucuk_birfi = 0;
for (int j = 0; j < ysiz; j++)
{
if (ysiz_birfi[j] != 0 && ysiz_birfi[j] < ysiz_minbirfi)
{
ysiz_minbirfi = ysiz_birfi[j];
en_kucuk_birfi = j;
}
}
//sonuc dizisine göre en küçük tedarik maliyeti indisi
for (int j = 0; j < firma; j++)
{
if (sonuc[j] == yetersiz[en_kucuk_birfi])
{
yetersiz_en_kucuk = j;
}
}
double yenimiktar = 0;
int c = 0;
int[] ysizfirma = new int[ysiz];
int[] ylifirma = new int[yli];
int firma_id = 0;
if (yli_minbirfi <= ysiz_minbirfi)
{
result[k] = sonuc[yeterli_en_kucuk];
//fid[yeterli_en_kucuk];
yenimiktar = t_stok[yeterli_en_kucuk] - tedarik * mol[k];
firma_id = fid[yeterli_en_kucuk];
mysqlbaglan.Close();
String guncelle5 = "Update tedarikci Set miktar='" + @yenimiktar + "' Where h_id='" + @bid[k] + "'&&fi_id='" + @firma_id + "'";
MySqlCommand komut5 = new MySqlCommand(guncelle5, mysqlbaglan);
MySqlDataReader MyReader5;
mysqlbaglan.Open();
MyReader5 = komut5.ExecuteReader();
//MessageBox.Show("değişim oldu!!!");
komut5.Dispose();
}
else
{
tedarik = tedarix;
sonuc[yetersiz_en_kucuk] = t_stok[yetersiz_en_kucuk] * t_sfiyat[yetersiz_en_kucuk] + uzaklik[yetersiz_en_kucuk] / carpan[yetersiz_en_kucuk];
yenimiktar = 0;
firma_id = fid[yetersiz_en_kucuk];
mysqlbaglan.Close();
String guncelle5 = "Update tedarikci Set miktar='" + @yenimiktar + "' Where h_id='" + @bid[k] + "'&&fi_id='" + @firma_id + "'";
MySqlCommand komut5 = new MySqlCommand(guncelle5, mysqlbaglan);
MySqlDataReader MyReader5;
mysqlbaglan.Open();
MyReader5 = komut5.ExecuteReader();
//MessageBox.Show("değişim oldu!!!");
komut5.Dispose();
tedarik = tedarik * mol[k] - t_stok[yetersiz_en_kucuk];
sonuc[yeterli_en_kucuk] = tedarik * t_sfiyat[yeterli_en_kucuk] + uzaklik[yeterli_en_kucuk] / carpan[yeterli_en_kucuk];
result[k] = sonuc[yetersiz_en_kucuk] + sonuc[yeterli_en_kucuk];
yenimiktar = t_stok[yeterli_en_kucuk] - tedarik;
firma_id = fid[yeterli_en_kucuk];
mysqlbaglan.Close();
String guncelle6 = "Update tedarikci Set miktar='" + @yenimiktar + "' Where h_id='" + @bid[k] + "'&&fi_id='" + @firma_id + "'";
MySqlCommand komut6 = new MySqlCommand(guncelle6, mysqlbaglan);
MySqlDataReader MyReader6;
mysqlbaglan.Open();
MyReader6 = komut6.ExecuteReader();
//MessageBox.Show("değişim oldu!!!");
komut6.Dispose();
}
}
//yeterli olan indisteki firmaya ulaş ve stok bilgisini güncelle
double sonmaliyet = result[0] + result[1] + result[2] + tedarix;
son_fiyat = sonmaliyet + kar * sonmaliyet;
MySqlCommand ekle1 = new MySqlCommand();
ekle1.Connection = mysqlbaglan;
ekle1.CommandText = "Insert into k_fiyat(uretim_tarihi, urun_id, toplam_maliyet, satis_fiyati) Values('" + @tarih + "','" + @urunid + "','" + @sonmaliyet + "','" + @son_fiyat + "')";
ekle1.ExecuteNonQuery();
mysqlbaglan.Close();
stok = 0;
maliyet = 0;
}
}
mysqlbaglan.Close();
String guncelle = "Update u_kimyasal Set stok='" + @stok + "', maliyet='" + @maliyet + "' Where kimyasal_urun= @deger";
MySqlCommand cmd3 = new MySqlCommand(guncelle, mysqlbaglan);
cmd3.Parameters.AddWithValue("@deger", textBox6.Text);
MySqlDataReader MyReader2;
mysqlbaglan.Open();
MyReader2 = cmd3.ExecuteReader();
MessageBox.Show("Data Updated");
cmd3.Dispose();
/*mysqlbaglan.Close();
* String guncelle1 = "Update k_fiyat Set toplam_maliyet='" + @toplam + "', satis_fiyati='" + @fiyat + "' Where urun_id= '"+@urunid+"'";
* MySqlCommand cmd4 = new MySqlCommand(guncelle1, mysqlbaglan);
* //cmd4.Parameters.AddWithValue("@deger", textBox6.Text);
* MySqlDataReader MyReader3;
* mysqlbaglan.Open();
* MyReader3 = cmd4.ExecuteReader();
* MessageBox.Show("Data Updated");
* cmd4.Dispose();*/
}
else
{
MessageBox.Show("Lutfen bilgileri eksiksiz giriniz");
}
}
public static int ExecNonQuery(string query)
{
MySqlCommand command = new MySqlCommand(query, connection);
return(command.ExecuteNonQuery());
}
public static void insertIntervencijuZaPostojeceg
(string anamneza, string dijagnoza, string terapija, string punaCena, string isplaceno, string napomena, int pacijentId, int doktorId, string datum)
{
MySqlConnection conn = new MySqlConnection(MyConnection2);
try
{
var sqlCommand = "INSERT INTO intervencija " +
"(anamneza, dijagnoza, terapija, puna_cena, isplaceno, napomena, pacijent_id, doktor_id, datum)" +
" VALUES " +
"(@anamneza, @dijagnoza, @terapija, @punaCena, @isplaceno, @napomena, @pacijentId, @doktorId, @datum)";
conn.Open(); //uspostavlja vezu sa bazom
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = sqlCommand;
// da ne bi ubacivao prazne
if (anamneza == "")
{
anamneza = null;
}
if (dijagnoza == "")
{
dijagnoza = null;
}
if (terapija == "")
{
terapija = null;
}
if (punaCena == "")
{
punaCena = null;
}
if (isplaceno == "")
{
isplaceno = null;
}
if (datum == "")
{
datum = null;
}
comm.Parameters.AddWithValue("@anamneza", anamneza);
comm.Parameters.AddWithValue("@dijagnoza", dijagnoza);
comm.Parameters.AddWithValue("@terapija", terapija);
comm.Parameters.AddWithValue("@punaCena", punaCena);
comm.Parameters.AddWithValue("@isplaceno", isplaceno);
comm.Parameters.AddWithValue("@napomena", napomena);
comm.Parameters.AddWithValue("@pacijentId", pacijentId);
comm.Parameters.AddWithValue("@doktorId", doktorId);
comm.Parameters.AddWithValue("@datum", datum);
int a = comm.ExecuteNonQuery(); //izvrsava naredbu
long id = comm.LastInsertedId;
if (a > 0)
{
conn.Close();
MessageBox.Show("Intervencija uspešno dodata u bazu!", "Uspeh", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception)
{
conn.Close();
MessageBox.Show("Greška u unosu!", "Greška", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
conn.Close();
}
}
public bool InsertXML(List <Object> list, Type type)
{
int result = -1;
StringBuilder queryBuilder = new StringBuilder();
if (type == typeof(Country))
{
queryBuilder.Append("INSERT INTO country VALUES ");
foreach (Country country in list)
{
queryBuilder.Append("(" + country.Id + ", '" + country.Iso + "', '" + country.Name.Replace("'", "''") + "'),");
}
queryBuilder = queryBuilder.Remove(queryBuilder.Length - 1, 1);
if (OpenConnection())
{
try
{
string query = queryBuilder.ToString();
MySqlCommand cmd = new MySqlCommand(query, Connector);
result = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
if (type == typeof(Duration))
{
queryBuilder.Append("INSERT INTO duration VALUES ");
foreach (Duration duration in list)
{
queryBuilder.Append("(" + duration.Id + ", '" + duration.Description + "'),");
}
queryBuilder = queryBuilder.Remove(queryBuilder.Length - 1, 1);
if (OpenConnection())
{
try
{
string query = queryBuilder.ToString();
MySqlCommand cmd = new MySqlCommand(query, Connector);
result = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
if (type == typeof(Book))
{
queryBuilder.Append("INSERT INTO book VALUES ");
foreach (Book book in list)
{
queryBuilder.Append("(" + book.Id + ", '" + book.Title.Replace("'", "''") + "', '" + book.Description + "'),");
}
queryBuilder = queryBuilder.Remove(queryBuilder.Length - 1, 1);
if (OpenConnection())
{
try
{
string query = queryBuilder.ToString();
MySqlCommand cmd = new MySqlCommand(query, Connector);
result = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
return(result > 0);
}
private void btn_add_Click(object sender, EventArgs e)
{
//inserting items
try
{
// check if the textboxes are filled
if (txt_accid.Text == "" || txt_title.Text == "" || txt_author.Text == "" || txt_copies.Text == "")
{
MessageBox.Show("Incomplete Fields", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
if (txt_copies.Text == "0" || txt_copies.Text == "00" || txt_copies.Text == "000" || txt_copies.Text == "000")
{
MessageBox.Show("Please input atleast 1 copy", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
if (!txt_accid.Text.Any(Char.IsLetter) || !txt_accid.Text.Any(Char.IsDigit) || txt_accid.Text.Any(Char.IsSymbol))
{
MessageBox.Show("Invalid Accession No.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
//textboxes are filled
DateTime time = DateTime.Now;
string format = "yyyy-MM-dd HH:mm:ss";
var mytime = time.ToString(format);
String sqlconstring = "Data Source = LOCALHOST; Initial Catalog = it_2d; username = root; password = '';Convert Zero Datetime=True";
MySqlConnection sqlconnect = new MySqlConnection(sqlconstring);
MySqlCommand sqlcommand = new MySqlCommand();
MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter();
DataSet DS = new DataSet();
sqlconnect.Open();
sqlcommand.CommandText = "INSERT INTO `it_2d`.`tbl_itemlist` (`b_no`, `b_medium`,`b_title`,`b_author`,`b_category`,`b_copies`,`b_copyright`,`b_dateadd`) VALUES ('" + txt_accid.Text + "' ,'" + cmb_medium.Text + "','" + txt_title.Text + "','" + txt_author.Text + "','" + cmb_category.Text + "','" + txt_copies.Text + "','" + this.dtp_copyright.Text + "','" + this.dtp_dadded.Text + "');";
sqlcommand.CommandType = CommandType.Text;
sqlcommand.Connection = sqlconnect;
sqlcommand.ExecuteNonQuery();
sqlDataAdapter.SelectCommand = sqlcommand;
sqlcommand.CommandText = "INSERT INTO `it_2d`.`tbl_activitylog` (`librarian_id`, `activity`,`account_no`,`date_activity`) VALUES ('" + txt_id.Text + "' ,' Insert new item','" + txt_accid.Text + "','" + mytime + "');";
sqlcommand.CommandType = CommandType.Text;
sqlcommand.Connection = sqlconnect;
sqlcommand.ExecuteNonQuery();
sqlDataAdapter.SelectCommand = sqlcommand;
sqlcommand.CommandText = "SELECT `b_no` as 'Accession No.', `b_medium` as 'Medium', `b_title` as 'Title', `b_author` as 'Author', `b_category` as 'Category', `b_copies` as 'Copies', `b_copyright` as 'Copyright', `b_dateadd` as 'Date Added' FROM `tbl_itemlist` ";
sqlcommand.CommandType = CommandType.Text;
sqlcommand.Connection = sqlconnect;
sqlcommand.ExecuteNonQuery();
sqlDataAdapter.SelectCommand = sqlcommand;
sqlDataAdapter.Fill(DS, "KAIBIGAN");
dataGridView1.DataSource = DS;
dataGridView1.DataMember = "KAIBIGAN";
sqlconnect.Close();
MessageBox.Show("Item Added", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
txt_accid.Clear();
txt_title.Clear();
txt_author.Clear();
txt_copies.Clear();
txt_total.Clear();
txt_stitle.Clear();
txt_saccno.Clear();
cmb_search.SelectedIndex = 0;
cmb_category.SelectedIndex = 0;
cmb_medium.SelectedIndex = 0;
cmb_smedium.SelectedIndex = 0;
cmb_scategory.SelectedIndex = 0;
}
}
}
}
catch (MySqlException er)
{
MessageBox.Show(er.Message, "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
protected void btnSalvar_Click(object sender, EventArgs e)
{
string strSQL;
if (txtCodigo.Text != "")
{
if (TxtNome.Text == "")
{
lblAviso.Text = "Nome Produto não informado!!";
lblAviso.Visible = true;
return;
}
if (TxtPreco.Text == "")
{
lblAviso.Text = "Preço Produto não informado!!";
lblAviso.Visible = true;
return;
}
if (TxtQuant.Text == "")
{
lblAviso.Text = "Quantidade Produto não informado!!";
lblAviso.Visible = true;
return;
}
MySqlConnection myConnectionCorrigeFA01;
MySqlCommand myCommand;
MySqlDataReader dsX;
string sdataInicial = DateTime.Now.ToString();
sdataInicial = sdataInicial.Substring(1, 9);
myConnectionCorrigeFA01 = new MySqlConnection("server=172.16.0.32; user id=intranetadmin; password=Intranet@Al34m; database=loja; pooling=false;SslMode = none;");
strSQL = "SELECT * from produto where codigo = '" + txtCodigo.Text + "'";
myConnectionCorrigeFA01.Open();
// Pega os valores das data definidas dentro da tabela padrão
myCommand = new MySqlCommand(strSQL, myConnectionCorrigeFA01);
dsX = myCommand.ExecuteReader();
// Se existir, alterar
if (dsX.Read())
{
MySqlCommand myCommand2;
MySqlConnection myConnectionCorrigeFA08;
myConnectionCorrigeFA08 = new MySqlConnection("server=172.16.0.32; user id=intranetadmin; password=Intranet@Al34m; database=loja; pooling=false;SslMode = none;");
strSQL = "UPDATE produto SET Nome = '" + TxtNome.Text.ToUpper() + "', precounitario = " + TxtPreco.Text + ", dataprod = '" + sdataInicial + "', quant = " + TxtQuant.Text + " where codigo = '" + txtCodigo.Text + "'";
try
{
myConnectionCorrigeFA08.Open();
myCommand2 = new MySqlCommand(strSQL, myConnectionCorrigeFA08);
myCommand2.ExecuteNonQuery();
}
catch (Exception ex)
{
lblAviso.Text = "Erro de Alteração de Dados!!";
lblAviso.Visible = true;
return;
}
myConnectionCorrigeFA08.Close();
lblAviso.Text = "Alteração realizada com Sucesso!!";
lblAviso.Visible = true;
return;
}
// Se não existir, inserir
else
{
MySqlConnection myConnectionCorrigeFA09;
myConnectionCorrigeFA09 = new MySqlConnection("server=172.16.0.32; user id=intranetadmin; password=Intranet@Al34m; database=loja; pooling=false;SslMode = none;");
try
{
MySqlCommand myCommand3;
myConnectionCorrigeFA09.Open();
strSQL = "call sp_ins(" + txtCodigo.Text + ",'" + TxtNome.Text.ToUpper() + "'," + TxtPreco.Text + ",'" + sdataInicial + "'," + TxtQuant.Text + ");";
myCommand3 = new MySqlCommand(strSQL, myConnectionCorrigeFA09);
myCommand3.ExecuteReader();
}
catch (Exception ex)
{
lblAviso.Text = "Erro de Inserção de Dados!!";
lblAviso.Visible = true;
return;
}
myConnectionCorrigeFA09.Close();
lblAviso.Text = "Inserção realizada com Sucesso!!";
lblAviso.Visible = true;
return;
}
dsX.Close();
myConnectionCorrigeFA01.Close();
}
}
public int D_registrar_Usuario_Admin(Usuario usu)
{
String cadena = DConexion.cadena;
String sql = "INSERT INTO usuario(SERIE,CODIGO, ENCARGADO,USUARIO,CONTRASENA,TIPO,NOMBRES,APELLIDOS,SEXO, DNI, DIRECCION, REFERENCIA,MOVISTAR, CLARO, NEXTEL,TELEFONO,EMAIL,FACEBOOK,OCUPACION,ESTADO)" +
"VALUES (@SERIE,@CODIGO,@ENCARGADO, @USUARIO,@CONTRASENA,@TIPO,@NOMBRES, @APELLIDOS,@SEXO,@DNI,@DIRECCION, @REFERENCIA,@MOVISTAR,@CLARO,@NEXTEL,@TELEFONO,@EMAIL,@FACEBOOK,@OCUPACION,@ESTADO)";
cone = new MySqlConnection(cadena);
MySqlCommand com = new MySqlCommand(sql, cone);
int band;
cone.Open();
try
{
com.Parameters.AddWithValue("@SERIE", usu.Serie);
com.Parameters.AddWithValue("@CODIGO", usu.Codigo);
if (usu.Encargado == null)
{
com.Parameters.AddWithValue("@ENCARGADO", null);
}
else
{
com.Parameters.AddWithValue("@ENCARGADO", usu.Encargado.Serie);
}
if (usu.Usuario1 == null)
{
com.Parameters.AddWithValue("@USUARIO", null);
}
else
{
com.Parameters.AddWithValue("@USUARIO", usu.Usuario1);
}
if (usu.Contrasena == null)
{
com.Parameters.AddWithValue("@CONTRASENA", null);
}
else
{
com.Parameters.AddWithValue("@CONTRASENA", usu.Contrasena);
}
com.Parameters.AddWithValue("@TIPO", usu.Tipo);
com.Parameters.AddWithValue("@NOMBRES", usu.Nombres);
com.Parameters.AddWithValue("@APELLIDOS", usu.Apellidos);
com.Parameters.AddWithValue("@SEXO", usu.Sexo);
com.Parameters.AddWithValue("@DNI", usu.Dni);
com.Parameters.AddWithValue("@DIRECCION", usu.Direccion);
com.Parameters.AddWithValue("@REFERENCIA", usu.Referencia);
com.Parameters.AddWithValue("@MOVISTAR", usu.Movistar);
com.Parameters.AddWithValue("@CLARO", usu.Claro);
com.Parameters.AddWithValue("@NEXTEL", usu.Nextel);
com.Parameters.AddWithValue("@TELEFONO", usu.Telefono);
com.Parameters.AddWithValue("@EMAIL", usu.Email);
com.Parameters.AddWithValue("@FACEBOOK", usu.Facebook);
com.Parameters.AddWithValue("@OCUPACION", usu.Ocupacion);
com.Parameters.AddWithValue("@ESTADO", usu.Estado);
band = com.ExecuteNonQuery();
}
catch (Exception e)
{
band = 0;
Console.WriteLine("" + e.Message);
}
cone.Close();
return(band);
}
private void btn_delete_Click(object sender, EventArgs e)
{
//deleting items
DateTime time = DateTime.Now;
string format = "yyyy-MM-dd HH:mm:ss";
var mytime = time.ToString(format);
String sqlconstring = "Data Source = LOCALHOST; Initial Catalog = it_2d; username = root; password = '';Convert Zero Datetime=True";
MySqlConnection sqlconnect = new MySqlConnection(sqlconstring);
MySqlCommand sqlcommand = new MySqlCommand();
MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter();
DataSet DS = new DataSet();
sqlconnect.Open();
//checking if the accession no inputted is existing
MySqlCommand check_User_Name = new MySqlCommand("SELECT * FROM tbl_itemlist WHERE (`b_no` = '" + txt_accid.Text + "') ", sqlconnect);
if (check_User_Name.ExecuteScalar() != null)
{
string UserExist = check_User_Name.ExecuteScalar().ToString();
if (UserExist != null)
{
//delete the item
sqlcommand.CommandText = "DELETE FROM `tbl_itemlist` WHERE `b_no` = ('" + txt_accid.Text + "') ";
sqlcommand.CommandType = CommandType.Text;
sqlcommand.Connection = sqlconnect;
sqlcommand.ExecuteNonQuery();
sqlDataAdapter.SelectCommand = sqlcommand;
sqlcommand.CommandText = "INSERT INTO `it_2d`.`tbl_activitylog` (`librarian_id`, `activity`,`account_no`,`date_activity`) VALUES ('" + txt_id.Text + "' ,' Delete item','" + txt_accid.Text + "','" + mytime + "');";
sqlcommand.CommandType = CommandType.Text;
sqlcommand.Connection = sqlconnect;
sqlcommand.ExecuteNonQuery();
sqlDataAdapter.SelectCommand = sqlcommand;
sqlcommand.CommandText = "SELECT `b_no` as 'Accession No.', `b_medium` as 'Medium', `b_title` as 'Title', `b_author` as 'Author', `b_category` as 'Category', `b_copies` as 'Copies', `b_copyright` as 'Copyright', `b_dateadd` as 'Date Added' FROM `tbl_itemlist` ";
sqlcommand.CommandType = CommandType.Text;
sqlcommand.Connection = sqlconnect;
sqlcommand.ExecuteNonQuery();
sqlDataAdapter.SelectCommand = sqlcommand;
sqlDataAdapter.Fill(DS, "KAIBIGAN");
dataGridView1.DataSource = DS;
dataGridView1.DataMember = "KAIBIGAN";
sqlconnect.Close();
MessageBox.Show("Item Removed", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
txt_accid.Clear();
txt_title.Clear();
txt_author.Clear();
txt_copies.Clear();
txt_total.Clear();
txt_stitle.Clear();
txt_saccno.Clear();
cmb_search.SelectedIndex = 0;
cmb_category.SelectedIndex = 0;
cmb_medium.SelectedIndex = 0;
cmb_smedium.SelectedIndex = 0;
cmb_scategory.SelectedIndex = 0;
;
}
}
else
{
MessageBox.Show("Invalid Accession No.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
private static int ExecuteNonQuery(MySqlCommand command)
{
command.ExecuteNonQuery(); return(0);
}
private void button1_Click(object sender, EventArgs e)
{
string address = "";
string user = "";
string name = "";
string phone = "";
string password = "";
conn.Open();//验重
string sql_repeat = $"select TNo from tradesman where TNo = '{textBox1.Text}'";
MySqlCommand cmd = new MySqlCommand(sql_repeat, conn);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
label6.Text = "账号已被注册！";
}
else
{
label6.Text = "";
user = textBox1.Text;
}
reader.Dispose();
conn.Close();
if (textBox4.Text.Length != 11 || textBox4.Text.Substring(0, 1) != "1")//验手机号
{
label11.Text = "格式错误！";
}
else
{
label11.Text = "";
phone = textBox4.Text;
}
if (textBox5.Text != textBox6.Text)//验密码
{
label7.Text = "两次密码不一致！";
}
else
{
label7.Text = "";
password = textBox5.Text;
}
if (comboBox1.SelectedItem == null || comboBox2.SelectedItem == null || textBox3.Text == "")
{
label13.Text = "请输入地址";
}
else
{
label13.Text = "";
address = comboBox1.SelectedItem.ToString() + "省" + comboBox2.SelectedItem.ToString() + "市" + textBox3.Text;
}
name = textBox2.Text;
if (label6.Text == "" && label7.Text == "" && label11.Text == "" && label13.Text == "")//无注册提示错误信息
{
conn.Open();
string sql_regi = $"insert into tradesman(TNo,TPassword,TName,TAddress,TContact) values('{user}','{password}','{name}','{address}','{phone}');";
cmd = new MySqlCommand(sql_regi, conn);
int result = cmd.ExecuteNonQuery();
conn.Close();
MessageBox.Show("注册成功！", "注册提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Asterisk);
this.Close();
}
else
{
MessageBox.Show("注册失败，请输入正确的信息！", "注册提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation);
}
}
private void buttonRegister_Click(object sender, EventArgs e)
{
DB db = new DB();
MySqlDataReader reader;
MySqlCommand command_log = new MySqlCommand("select sotrudnik.id from `sotrudnik` join vxod on SOTRUDNIK.id = SOTRUDNIK_id WHERE login = @login", db.getConnection());
command_log.Parameters.Add("@login", MySqlDbType.VarChar).Value = userNameField.Text;
db.openConnection();
reader = command_log.ExecuteReader();
while (reader.Read())
{
log_id = reader["id"].ToString();
}
db.closeConnection();
MySqlCommand command_vxod_id = new MySqlCommand("select vxod.id from `sotrudnik` join vxod on SOTRUDNIK.id = SOTRUDNIK_id WHERE login = @login", db.getConnection());
command_vxod_id.Parameters.Add("@login", MySqlDbType.VarChar).Value = userNameField.Text;
db.openConnection();
reader = command_vxod_id.ExecuteReader();
while (reader.Read())
{
vxod_id = reader["id"].ToString();
}
db.closeConnection();
MySqlCommand command_del_vxod = new MySqlCommand("DELETE FROM `oborot`.`vxod` WHERE (`id` = @vxod_id);", db.getConnection());
command_del_vxod.Parameters.Add("@vxod_id", MySqlDbType.VarChar).Value = vxod_id;
db.openConnection();
command_del_vxod.ExecuteNonQuery();
db.closeConnection();
MySqlCommand command = new MySqlCommand("DELETE FROM `oborot`.`sotrudnik` WHERE (`id` = @log_id);", db.getConnection());
command.Parameters.Add("@log_id", MySqlDbType.VarChar).Value = log_id;
db.openConnection();
if (command.ExecuteNonQuery() == 1)
{
MessageBox.Show("Аккаунт удален");
}
else
{
MessageBox.Show("Аккаунт не удален");
}
db.closeConnection();
}
private void button3_Click(object sender, EventArgs e)
{
if (textBox1.Text == string.Empty)
{
MessageBox.Show("field can't be empty, please enter a value!");
return;
}
if (textBox2.Text == string.Empty)
{
MessageBox.Show("field can't be empty, please enter a value!");
return;
}
if (textBox3.Text == string.Empty)
{
MessageBox.Show("field can't be empty, please enter a value!");
return;
}
if (textBox4.Text == string.Empty)
{
MessageBox.Show("field can't be empty, please enter a value!");
return;
}
if (textBox5.Text == string.Empty)
{
MessageBox.Show("field can't be empty, please enter a value!");
return;
}
if (textBox6.Text == string.Empty)
{
MessageBox.Show("field can't be empty, please enter a value!");
return;
}
if (textBox31.Text == string.Empty)
{
MessageBox.Show("field can't be empty, please enter a value!");
return;
}
if (textBox32.Text == string.Empty)
{
MessageBox.Show("field can't be empty, please enter a value!");
return;
}
if (textBox33.Text == string.Empty)
{
MessageBox.Show("field can't be empty, please enter a value!");
return;
}
if (textBox34.Text == string.Empty)
{
MessageBox.Show("field can't be empty, please enter a value!");
return;
}
if (textBox35.Text == string.Empty)
{
MessageBox.Show("field for month can't be empty, please enter a value!");
return;
}
long ticks = DateTime.Now.Ticks;
byte[] bytes = BitConverter.GetBytes(ticks);
string id = Convert.ToBase64String(bytes)
.Replace('+', '_')
.Replace('/', '-')
.TrimEnd('=');
textBox32.Text = id.ToString();
DateTime.Now.ToString("yyMMddHHmmssff");
textBox32.Text = DateTime.Now.ToLongTimeString();
MySqlConnection con = new MySqlConnection("server=localhost;user id=root;database=erp_crm");
con.Open();
MySqlCommand cmd = new MySqlCommand("INSERT INTO salesreg(`name_prd`, `desc`, `unit_price`, `qty`, `total`,`prd_id`,`name_cus`, `sales_rep`, `grand_total`, `invoice_no`, `month`) VALUES('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "', '" + textBox4.Text + "','" + textBox5.Text + "', '" + textBox6.Text + "','" + textBox34.Text + "', '" + textBox33.Text + "','" + textBox31.Text + "', '" + textBox32.Text + "', '" + textBox35.Text + "')", con);
cmd.ExecuteNonQuery();
MessageBox.Show("record has been succesfully inserted in the database, Click Ok to proceed in prinitng receipt");
Form8 myform = new Form8();
this.Hide();
myform.Show();
con.Close();
}
//Espera um parametro do tipo string contendo um comando SQL do tipo INSERT, UPDATE, DELETE
public override void ExecutarComandoSQL(string sql)
{
MySqlCommand command = new MySqlCommand(sql, Connection);
command.ExecuteNonQuery();
}
//
// System.Web.Security.RoleProvider methods.
//
//
// RoleProvider.AddUsersToRoles
//
public override void AddUsersToRoles(string[] usernames, string[] roleNames)
{
if (usernames == null)
{
throw new ArgumentNullException("usernames");
}
if (roleNames == null)
{
throw new ArgumentNullException("roleNames");
}
foreach (string rolename in roleNames)
{
if (!RoleExists(rolename))
{
throw new ProviderException("Role name not found.");
}
}
foreach (string username in usernames)
{
if (username.IndexOf(',') > 0)
{
throw new ArgumentException("User names cannot contain commas.");
}
foreach (string rolename in roleNames)
{
if (IsUserInRole(username, rolename))
{
throw new ProviderException("User is already in role.");
}
}
}
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
using (MySqlCommand cmd = new MySqlCommand("INSERT INTO `" + usersInRolesTable + "`" +
" (Username, Rolename, ApplicationName) " +
" Values(?Username, ?Rolename, ?ApplicationName)", conn))
{
MySqlParameter userParm = cmd.Parameters.Add("?Username", MySqlDbType.VarChar, 255);
MySqlParameter roleParm = cmd.Parameters.Add("?Rolename", MySqlDbType.VarChar, 255);
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = ApplicationName;
MySqlTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
foreach (string username in usernames)
{
foreach (string rolename in roleNames)
{
userParm.Value = username;
roleParm.Value = rolename;
cmd.ExecuteNonQuery();
}
}
tran.Commit();
}
catch (MySqlException e)
{
try
{
tran.Rollback();
}
catch (MySqlException) { }
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "AddUsersToRoles");
}
else
{
throw;
}
}
}
}
}
//
// RoleProvider.DeleteRole
//
public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
{
if (!RoleExists(roleName))
{
throw new ProviderException("Role does not exist.");
}
if (throwOnPopulatedRole && GetUsersInRole(roleName).Length > 0)
{
throw new ProviderException("Cannot delete a populated role.");
}
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
using (MySqlCommand cmd = new MySqlCommand("DELETE FROM `" + rolesTable + "`" +
" WHERE Rolename = ?Rolename AND ApplicationName = ?ApplicationName", conn))
{
cmd.Parameters.Add("?Rolename", MySqlDbType.VarChar, 255).Value = roleName;
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = ApplicationName;
using (MySqlCommand cmd2 = new MySqlCommand("DELETE FROM `" + usersInRolesTable + "`" +
" WHERE Rolename = ?Rolename AND ApplicationName = ?ApplicationName", conn))
{
cmd2.Parameters.Add("?Rolename", MySqlDbType.VarChar, 255).Value = roleName;
cmd2.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = ApplicationName;
MySqlTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
cmd2.Transaction = tran;
cmd2.ExecuteNonQuery();
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (MySqlException e)
{
try
{
tran.Rollback();
}
catch (MySqlException) { }
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "DeleteRole");
return(false);
}
else
{
throw;
}
}
}
}
}
return(true);
}
public static string MyConnection2 = "server=remotemysql.com;uid=az1Qt8dZpk;pwd=ihTg5bupu2;database=az1Qt8dZpk"; //promenim da cita iz propratnog txt-a
//private static string MyConnection2 = "server=sql7.freemysqlhosting.net;uid=sql7293520;pwd=ivflmq8gcK;database=sql7293520";
#region Intervencija uz unos pacijenta
public static void insertIntervencijaUzPacijenta(string Amanmeza, string Dijagnoza, string Terapija, string PunaCena, string Isplaceno,
string Napomena, int DoktorID, string Datum, string Ime, string Prezime, string GodRodj, string Adresa, string BolestiRizika)
{
MySqlConnection conn = new MySqlConnection(MyConnection2);
//unosim podatke
try
{
var sqlCommand = "INSERT INTO intervencija (anamneza, dijagnoza, terapija, puna_cena, isplaceno, napomena, pacijent_id, doktor_id, datum) " +
"VALUES (@anamneza, @dijagnoza, @terapija, @puna_cena, @isplaceno, @napomena, @pacijent_id, @doktor_id, @datum)";
conn.Open(); //uspostavlja vezu sa bazom
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = sqlCommand;
// da ne bi ubacivao prazne
if (Ime == "")
{
Ime = null;
}
if (Prezime == "")
{
Prezime = null;
}
if (GodRodj == "")
{
GodRodj = null;
}
if (Adresa == "")
{
Adresa = null;
}
if (BolestiRizika == "")
{
BolestiRizika = null;
}
if (Amanmeza == "")
{
Amanmeza = null;
}
if (Dijagnoza == "")
{
Dijagnoza = null;
}
if (Terapija == "")
{
Terapija = null;
}
if (PunaCena == "")
{
PunaCena = null;
}
if (Isplaceno == "")
{
Isplaceno = null;
}
if (Datum == "")
{
Datum = null;
}
if (BolestiRizika == "")
{
BolestiRizika = null;
}
insertPacijent(Ime, Prezime, GodRodj, Adresa, BolestiRizika);
//trazim id pacijenta na osnovu datih podataka
string idPacijenta;
idPacijenta = select1("SELECT Id FROM pacijent " +
" WHERE ime = '" + Ime + "' " +
"AND prezime='" + Prezime + "' " +
"AND god_rodj ='" + GodRodj + "' " +
"AND adresa='" + Adresa + "'" +
"AND bolesti_rizika='" + BolestiRizika + "'"
);
//dodajem vrednosti poljima za unos u bazu
comm.Parameters.AddWithValue("@anamneza", Amanmeza);
comm.Parameters.AddWithValue("@dijagnoza", Dijagnoza);
comm.Parameters.AddWithValue("@terapija", Terapija); //ovde se dodeljuju vrednosti poljima za unos u bazu
comm.Parameters.AddWithValue("@puna_cena", PunaCena);
comm.Parameters.AddWithValue("@isplaceno", Isplaceno);
comm.Parameters.AddWithValue("@napomena", Napomena);
comm.Parameters.AddWithValue("@pacijent_id", idPacijenta);
comm.Parameters.AddWithValue("@doktor_id", DoktorID);
comm.Parameters.AddWithValue("@datum", Datum);
int a = comm.ExecuteNonQuery(); //izvrsava naredbu
long id = comm.LastInsertedId;
if (a > 0)
{
conn.Close();
MessageBox.Show("Intervencija uspešno dodata u bazu!", "Uspeh", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
conn.Close();
MessageBox.Show(ex.Message);
//MessageBox.Show("Greška u unosu!", "Greška", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
conn.Close();
}
}
/// <summary>
/// DB 업데이트(UPDATE) 및 입력(INSERT) 처리
/// </summary>
private void ControlDataProcess()
{
// 빈값확인(NULL CHECK)
if (string.IsNullOrEmpty(TxtAuthor.Text) || CboDivision.SelectedIndex < 1 ||
string.IsNullOrEmpty(TxtNames.Text) || string.IsNullOrEmpty(TxtIsbn.Text))
{
MetroMessageBox.Show(this, "빈값은 넣을 수 없습니다.", "오류", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (myMode == BaseMode.NONE)
{
MetroMessageBox.Show(this, "신규등록시 신규 버튼을 눌러주세요.", "알림", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
try
{
// DB에 새로운 값 추가/변경
using (MySqlConnection conn = new MySqlConnection(Commons.CONNSTR))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
// 저장 버튼을 눌렀을 때
if (myMode == BaseMode.UPDATE)
{
cmd.CommandText = @"UPDATE bookstbl
SET
Author = @Author,
Division = @Division,
Names = @Names,
ReleaseDate = @ReleaseDate,
ISBN = @ISBN,
Price = @Price
WHERE Idx = @Idx" ;
}
// 신규 버튼을 눌렀을 때
else if (myMode == BaseMode.INSERT)
{
cmd.CommandText = @"INSERT INTO bookstbl(
Author,
Division,
Names,
ReleaseDate,
ISBN,
Price)
VALUES(
@Author,
@Division,
@Names,
@ReleaseDate,
@ISBN,
@Price)" ;
}
// 자료형과 크기는 최대한 DB의 내용과 통일한다
MySqlParameter paramAutor = new MySqlParameter("@Author", MySqlDbType.VarChar, 45);
paramAutor.Value = TxtAuthor.Text;
cmd.Parameters.Add(paramAutor);
MySqlParameter paramDivision = new MySqlParameter("@Division", MySqlDbType.VarChar, 4);
paramDivision.Value = CboDivision.SelectedValue;
cmd.Parameters.Add(paramDivision);
MySqlParameter paramNames = new MySqlParameter("@Names", MySqlDbType.VarChar, 100)
{
Value = TxtNames.Text
};
cmd.Parameters.Add(paramNames);
MySqlParameter paramReleaseDate = new MySqlParameter("@ReleaseDate", MySqlDbType.Date);
paramReleaseDate.Value = DtReleaseDate.Value;
cmd.Parameters.Add(paramReleaseDate);
MySqlParameter paramISBN = new MySqlParameter("@ISBN", MySqlDbType.VarChar, 13);
paramISBN.Value = TxtIsbn.Text;
cmd.Parameters.Add(paramISBN);
MySqlParameter paramPrice = new MySqlParameter("@Price", MySqlDbType.Decimal)
{
Value = TxtPrice.Text
};
cmd.Parameters.Add(paramPrice);
// UPDATE인 경우만 Idx를 사용.
if (myMode == BaseMode.UPDATE)
{
MySqlParameter paramIdx = new MySqlParameter("@Idx", MySqlDbType.Int32);
paramIdx.Value = TxtIdx.Text;
cmd.Parameters.Add(paramIdx);
}
// 값이 제대로 DB로 넘어가면 return 1
var result = cmd.ExecuteNonQuery();
if (myMode == BaseMode.INSERT)
{
MetroMessageBox.Show(this, $"{result}건이 신규입력되었습니다.", "신규입력");
}
else if (myMode == BaseMode.UPDATE)
{
MetroMessageBox.Show(this, $"{result}건이 추가되었습니다.", "추가");
}
}
}
catch (Exception ex)
{
MetroMessageBox.Show(this, $"에러발생 : {ex.Message}", "에러", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
// 위에서 생성한 사용자 생성 함수
// 업데이트한 내용으로 DB를 다시 불러온다.
UpdateData();
}
}
