private void button1_Click(object sender, EventArgs e)
{
try
{
MySqlConnection myConn = new MySqlConnection();
myConn.Host = "192.168.69.8";
myConn.Port = 3306;
myConn.UserId = "cody";
myConn.Password = "******";
myConn.Open();
MySqlCommand myCommand = new MySqlCommand(" SELECT * FROM registration.regUser ;", myConn);
MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
myDataAdapter.SelectCommand = myCommand;
DataTable dbDataSet = new DataTable();
myDataAdapter.Fill(dbDataSet);
BindingSource bSource = new BindingSource();
bSource.DataSource = dbDataSet;
dataGridView1.DataSource = bSource;
myDataAdapter.Update(dbDataSet);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
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 BulkLoadColumnOrder()
{
execSQL(@"CREATE TABLE Test (id INT NOT NULL, n1 VARCHAR(250), n2 VARCHAR(250),
n3 VARCHAR(250), PRIMARY KEY(id))");
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(path);
for (int i = 0; i < 20; i++)
sw.WriteLine(i + ",col3,col2,col1");
sw.Flush();
sw.Close();
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
loader.LineTerminator = Environment.NewLine;
loader.Columns.Add("id");
loader.Columns.Add("n3");
loader.Columns.Add("n2");
loader.Columns.Add("n1");
int count = loader.Load();
Assert.AreEqual(20, count);
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(20, dt.Rows.Count);
Assert.AreEqual("col1", dt.Rows[0][1]);
Assert.AreEqual("col2", dt.Rows[0][2]);
Assert.AreEqual("col3", dt.Rows[0][3].ToString().Trim());
}
public void AutoIncrementColumnsOnInsert()
{
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test (id INT UNSIGNED NOT NULL AUTO_INCREMENT, " +
"name VARCHAR(100), PRIMARY KEY(id))");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
dt.Columns[0].AutoIncrement = true;
Assert.IsTrue(dt.Columns[0].AutoIncrement);
dt.Columns[0].AutoIncrementSeed = -1;
dt.Columns[0].AutoIncrementStep = -1;
DataRow row = dt.NewRow();
row["name"] = "Test";
try
{
dt.Rows.Add(row);
da.Update(dt);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
dt.Clear();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(1, dt.Rows[0]["id"]);
Assert.AreEqual("Test", dt.Rows[0]["name"]);
cb.Dispose();
}
public void Bug19481()
{
execSQL("DROP TABLE Test");
execSQL("CREATE TABLE Test(ID INT NOT NULL AUTO_INCREMENT, " +
"SATELLITEID VARCHAR(3) NOT NULL, ANTENNAID INT, AOS_TIMESTAMP DATETIME NOT NULL, " +
"TEL_TIMESTAMP DATETIME, LOS_TIMESTAMP DATETIME, PRIMARY KEY (ID))");
execSQL("INSERT INTO Test VALUES (NULL,'224','0','2005-07-24 00:00:00'," +
"'2005-07-24 00:02:00','2005-07-24 00:22:00')");
execSQL("INSERT INTO Test VALUES (NULL,'155','24','2005-07-24 03:00:00'," +
"'2005-07-24 03:02:30','2005-07-24 03:20:00')");
execSQL("INSERT INTO Test VALUES (NULL,'094','34','2005-07-24 09:00:00'," +
"'2005-07-24 09:00:30','2005-07-24 09:15:00')");
execSQL("INSERT INTO Test VALUES (NULL,'224','54','2005-07-24 12:00:00'," +
"'2005-07-24 12:01:00','2005-07-24 12:33:00')");
execSQL("INSERT INTO Test VALUES (NULL,'155','25','2005-07-24 15:00:00'," +
"'2005-07-24 15:02:00','2005-07-24 15:22:00')");
execSQL("INSERT INTO Test VALUES (NULL,'094','0','2005-07-24 17:00:00'," +
"'2005-07-24 17:02:12','2005-07-24 17:20:00')");
execSQL("INSERT INTO Test VALUES (NULL,'224','24','2005-07-24 19:00:00'," +
"'2005-07-24 19:02:00','2005-07-24 19:27:00')");
execSQL("INSERT INTO Test VALUES (NULL,'155','34','2005-07-24 21:00:00'," +
"'2005-07-24 21:02:33','2005-07-24 21:22:55')");
execSQL("INSERT INTO Test VALUES (NULL,'094','55','2005-07-24 23:00:00'," +
"'2005-07-24 23:00:45','2005-07-24 23:22:23')");
DateTime date = DateTime.Parse("7/24/2005");
StringBuilder sql = new StringBuilder();
sql.AppendFormat(CultureInfo.InvariantCulture,
@"SELECT ID, ANTENNAID, TEL_TIMESTAMP, LOS_TIMESTAMP FROM Test
WHERE TEL_TIMESTAMP >= '{0}'", date.ToString("u"));
MySqlDataAdapter da = new MySqlDataAdapter(sql.ToString(), conn);
DataSet dataSet = new DataSet();
da.Fill(dataSet);
}
public void MultiWord()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(100), dt DATETIME, tm TIME, `multi word` int, PRIMARY KEY(id))");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
DataRow row = dt.NewRow();
row["id"] = 1;
row["name"] = "Name";
row["dt"] = DBNull.Value;
row["tm"] = DBNull.Value;
row["multi word"] = 2;
dt.Rows.Add(row);
da.Update(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(2, dt.Rows[0]["multi word"]);
dt.Rows[0]["multi word"] = 3;
da.Update(dt);
cb.Dispose();
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(3, dt.Rows[0]["multi word"]);
}
public void AutoIncrementColumnsOnInsert()
{
execSQL("CREATE TABLE Test (id INT UNSIGNED NOT NULL AUTO_INCREMENT, " +
"name VARCHAR(100), PRIMARY KEY(id))");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
da.InsertCommand = cb.GetInsertCommand();
da.InsertCommand.CommandText += "; SELECT last_insert_id()";
da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
DataTable dt = new DataTable();
da.Fill(dt);
dt.Columns[0].AutoIncrement = true;
Assert.IsTrue(dt.Columns[0].AutoIncrement);
dt.Columns[0].AutoIncrementSeed = -1;
dt.Columns[0].AutoIncrementStep = -1;
DataRow row = dt.NewRow();
row["name"] = "Test";
dt.Rows.Add(row);
da.Update(dt);
dt.Clear();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(1, dt.Rows[0]["id"]);
Assert.AreEqual("Test", dt.Rows[0]["name"]);
cb.Dispose();
}
public void AutoIncrementColumnsOnInsert2()
{
execSQL("CREATE TABLE Test (id INT UNSIGNED NOT NULL " +
"AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20))");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
MySqlCommand cmd = (MySqlCommand)(cb.GetInsertCommand() as ICloneable).Clone();
cmd.CommandText += "; SELECT last_insert_id() as id";
cmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
da.InsertCommand = cmd;
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows.Clear();
DataRow row = dt.NewRow();
row["name"] = "Test";
dt.Rows.Add(row);
da.Update(dt);
Assert.AreEqual(1, dt.Rows[0]["id"]);
Assert.AreEqual("Test", dt.Rows[0]["name"]);
row = dt.NewRow();
row["name"] = "Test2";
dt.Rows.Add(row);
da.Update(dt);
Assert.AreEqual(2, dt.Rows[1]["id"]);
Assert.AreEqual("Test2", dt.Rows[1]["name"]);
Assert.AreEqual(1, dt.Rows[0]["id"]);
}
public void FunctionsReturnStringAndDecimal()
{
execSQL("CREATE TABLE bug52187a (a decimal(5,2) not null)");
execSQL("CREATE TABLE bug52187b (b decimal(5,2) not null)");
execSQL("insert into bug52187a values (1.25)");
execSQL("insert into bug52187b values (5.99)");
CultureInfo curCulture = Thread.CurrentThread.CurrentCulture;
CultureInfo curUICulture = Thread.CurrentThread.CurrentUICulture;
CultureInfo c = new CultureInfo("pt-PT");
Thread.CurrentThread.CurrentCulture = c;
Thread.CurrentThread.CurrentUICulture = c;
string connStr = GetConnectionString(true) + ";functions return string=true";
try
{
using (MySqlConnection con = new MySqlConnection(connStr))
{
con.Open();
MySqlDataAdapter da = new MySqlDataAdapter(
"select *,(select b from bug52187b) as field_b from bug52187a", con);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(1.25, dt.Rows[0][0]);
Assert.AreEqual(5.99, dt.Rows[0][1]);
}
}
finally
{
Thread.CurrentThread.CurrentCulture = curCulture;
Thread.CurrentThread.CurrentUICulture = curUICulture;
}
}
public void MultiWord()
{
try
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
DataRow row = dt.NewRow();
row["id"] = 1;
row["name"] = "Name";
row["dt"] = DBNull.Value;
row["tm"] = DBNull.Value;
row["multi word"] = 2;
dt.Rows.Add(row);
da.Update(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(2, dt.Rows[0]["multi word"]);
dt.Rows[0]["multi word"] = 3;
da.Update(dt);
cb.Dispose();
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(3, dt.Rows[0]["multi word"]);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
}
public void ShowCreateTable()
{
MySqlDataAdapter da = new MySqlDataAdapter("SHOW CREATE TABLE Test", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(2, dt.Columns.Count);
}
public void ShowCreateTable()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
MySqlDataAdapter da = new MySqlDataAdapter("SHOW CREATE TABLE Test", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(2, dt.Columns.Count);
}
internal static void fill(string queryString, DataTable toReturn)
{
using (var conn = new MySqlConnection(connString)) {
conn.Open();
conn.ChangeDatabase(Server.MySQLDatabaseName);
using (MySqlDataAdapter da = new MySqlDataAdapter(queryString, conn)) {
da.Fill(toReturn);
}
conn.Close();
}
}
public void CommentsInSQL()
{
string sql = "INSERT INTO Test /* my table */ VALUES (1 /* this is the id */, 'Test' );" +
"/* These next inserts are just for testing \r\n" +
" comments */\r\n" +
"INSERT INTO \r\n" +
" # This table is bogus\r\n" +
"Test VALUES (2, 'Test2')";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
DataTable table = new DataTable();
da.Fill(table);
Assert.AreEqual(1, table.Rows[0]["id"]);
Assert.AreEqual("Test", table.Rows[0]["name"]);
Assert.AreEqual(2, table.Rows.Count);
Assert.AreEqual(2, table.Rows[1]["id"]);
Assert.AreEqual("Test2", table.Rows[1]["name"]);
}
public void AdapterConcurrentException()
{
execSQL(
"CREATE TABLE T (" +
"id_auto int(11) NOT NULL AUTO_INCREMENT," +
"field varchar(50) DEFAULT NULL," +
"PRIMARY KEY (id_auto))");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM T", conn);
da.InsertCommand = conn.CreateCommand();
da.InsertCommand.CommandText = @"INSERT INTO T(field) VALUES (@p_field);
SELECT * FROM T WHERE id_auto=@@IDENTITY";
da.InsertCommand.Parameters.Add("@p_field", MySqlDbType.VarChar, 50, "field");
da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
da.DeleteCommand = conn.CreateCommand();
da.DeleteCommand.CommandText = "DELETE FROM T WHERE id_auto=@id_auto";
da.DeleteCommand.Parameters.Add("@id_auto", MySqlDbType.Int32, 4, "id_auto");
DataSet ds = new DataSet();
da.Fill(ds, "T");
DataTable table = ds.Tables["T"];
DataRow r = table.NewRow();
r["field"] = "row";
table.Rows.Add(r);
da.Update(table);
Assert.AreEqual(r.RowState, DataRowState.Unchanged);
table.Rows[0].Delete();
r = table.NewRow();
r["field"] = "row2";
table.Rows.Add(r);
da.Update(table); // here was concurrencyviolation
da.Fill(ds);
Assert.AreEqual(ds.Tables["T"].Rows.Count, 1);
Assert.AreEqual(ds.Tables["T"].Rows[0]["field"], "row2");
}
public void BulkCopyCTC(List<EmployeeDet> list)
{
DataTable dt =new DataTable();
dt.Columns.Add(new DataColumn("employee_id",typeof(System.String)));
dt.Columns.Add(new DataColumn("employee_name",typeof(System.String)));
dt.Columns.Add(new DataColumn("emp_ctc",typeof(System.Decimal)));
foreach(EmployeeDet item in list)
{
DataRow dr = dt.NewRow();
dr["employee_id"]= item.GetID();
dr["employee_name"]= item.GetName();
dr["emp_ctc"]= item.GetCTC();
dt.Rows.Add(dr);
}
MySqlConnection con =new MySqlConnection(newConnectionUtils().GetConnectionString());
if(con.State==ConnectionState.Open)
{
con.Close();
}
con.Open();
MySqlCommand cmd =new MySqlCommand("SP_InsertCTC", con);
cmd.CommandType=CommandType.StoredProcedure;
cmd.UpdatedRowSource=UpdateRowSource.None;
cmd.Parameters.Add("?e_id",MySqlDbType.String).SourceColumn="employee_id";
cmd.Parameters.Add("?e_name",MySqlDbType.String).SourceColumn= "employee_name";
cmd.Parameters.Add("?emp_ctc",MySqlDbType.Decimal).SourceColumn= "emp_ctc";
MySqlDataAdapter da =new MySqlDataAdapter();
da.InsertCommand= cmd;
da.UpdateBatchSize=100;
int records = da.Update(dt);
con.Close();
}
public void AutoIncrementColumnsOnInsert2()
{
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test (id INT UNSIGNED NOT NULL " +
"AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20))");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
cb.ReturnGeneratedIdentifiers = true;
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows.Clear();
try
{
DataRow row = dt.NewRow();
row["name"] = "Test";
dt.Rows.Add(row);
da.Update(dt);
Assert.AreEqual(1, dt.Rows[0]["id"]);
Assert.AreEqual("Test", dt.Rows[0]["name"]);
row = dt.NewRow();
row["name"] = "Test2";
dt.Rows.Add(row);
da.Update(dt);
Assert.AreEqual(2, dt.Rows[1]["id"]);
Assert.AreEqual("Test2", dt.Rows[1]["name"]);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
Assert.AreEqual(1, dt.Rows[0]["id"]);
}
public void AmbiguousColumns()
{
if (Version < new Version(5, 0)) return;
execSQL("CREATE TABLE t1 (id INT)");
execSQL("CREATE TABLE t2 (id1 INT, id INT)");
execSQL(@"CREATE PROCEDURE spTest() BEGIN SELECT * FROM t1;
SELECT id FROM t1 JOIN t2 on t1.id=t2.id;
SELECT * FROM t2; END");
MySqlCommand cmd = new MySqlCommand("spTest", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
Assert.Fail("The above should have thrown an exception");
}
catch (Exception)
{
}
}
public void UpdatingWithDateInKey()
{
execSQL("CREATE TABLE Test (cod INT, dt DATE, PRIMARY KEY(cod, dt))");
execSQL("INSERT INTO Test (cod, dt) VALUES (1, '2006-1-1')");
execSQL("INSERT INTO Test (cod, dt) VALUES (2, '2006-1-2')");
execSQL("INSERT INTO Test (cod, dt) VALUES (3, '2006-1-3')");
execSQL("INSERT INTO Test (cod, dt) VALUES (4, '2006-1-4')");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test ORDER BY cod", conn);
MySqlCommandBuilder bld = new MySqlCommandBuilder(da);
bld.ConflictOption = ConflictOption.OverwriteChanges;
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows[0]["cod"] = 6;
da.Update(dt);
dt.Clear();
da.SelectCommand.CommandText = "SELECT * FROM Test WHERE cod=6";
da.Fill(dt);
Assert.AreEqual(6, dt.Rows[0]["cod"]);
}
public void SemicolonAtEndOfSQL()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(100), PRIMARY KEY(id))");
execSQL("INSERT INTO Test VALUES(1, 'Data')");
DataSet ds = new DataSet();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM `Test`;", conn);
da.FillSchema(ds, SchemaType.Source, "Test");
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows[0]["id"] = 2;
da.Update(dt);
dt.Clear();
da.Fill(dt);
cb.Dispose();
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(2, dt.Rows[0]["id"]);
}
private void label6_Click(object sender, EventArgs e)
{
connection.Open();
string GetInfo = "select * from student where SID='" + textBox1.Text + "'";
MySqlCommand command = new MySqlCommand(GetInfo, connection);
reader = command.ExecuteReader();
reader.Read();
string Name = reader.GetString("Name");
string Lname = reader.GetString("LName");
string ID = reader.GetString("ID");
string Parent = reader.GetString("Parent");
reader.Close();
string currentMonth = DateTime.Now.ToString("MMMM");
string currentYear = DateTime.Now.Year.ToString();
string currentDate = DateTime.Now.Date.ToString("yyyy.MM.dd");
string currentTime = DateTime.Now.ToString("h:mm tt");
MySqlDataAdapter sdap = new MySqlDataAdapter("select * from payment where SID='" + textBox1.Text + "' and PMonth='" + currentMonth + "' and Year='" + currentYear + "'and TName='" + comboBox1.Text + "'and CType='" + comboBox3.Text + "' ", connection);
DataTable dtp = new DataTable();
sdap.Fill(dtp);
if (dtp.Rows.Count > 0)
{
if (textBox1.Text != null && comboBox1.Text != "Teachers" && comboBox2.Text == "Entrance" && checkBox2.Checked == false)
{
string query = "INSERT INTO attendance( SID, TName, CType, Date, Time, Status, Action) VALUES('" + textBox1.Text + "','" + comboBox1.Text + "','" + comboBox3.Text + "','" + currentDate + "','" + currentTime + "','" + comboBox2.Text + "','In')";
command = new MySqlCommand(query, connection);
if (command.ExecuteNonQuery() == 1)
{
try
{
string tel = "+94";
string tel1 = Parent;
SerialPort sp = new SerialPort();
sp.PortName = "COM4";
sp.Open();
sp.WriteLine("AT" + Environment.NewLine);
Thread.Sleep(1000);
sp.WriteLine("AT+CMGF=1" + Environment.NewLine);
Thread.Sleep(1000);
sp.WriteLine("AT+CSCS=\"GSM\"" + Environment.NewLine);
Thread.Sleep(1000);
sp.WriteLine("AT+CMGS=\"" + tel + tel1 + "\"" + Environment.NewLine);
Thread.Sleep(1000);
sp.WriteLine(Name + " is arrived to the class at " + currentTime + " on " + currentDate + Environment.NewLine + "TUITION MASTER" + Environment.NewLine);
sp.Write(new byte[] { 26 }, 0, 1);
Thread.Sleep(1000);
var response = sp.ReadExisting();
sp.Close();
}
catch (Exception ex)
{
MessageBox.Show("Usb Modem Issue");
}
}
else
{
MessageBox.Show("Error");
}
}
else if (textBox1.Text != null && comboBox1.Text != "Teachers" && comboBox2.Text == "Interval" && checkBox2.Checked == false)
{
string query = "INSERT INTO attendance( SID, TName, CType, Date, Time, Status, Action) VALUES('" + textBox1.Text + "','" + comboBox1.Text + "','" + comboBox3.Text + "','" + currentDate + "','" + currentTime + "','" + comboBox2.Text + "','In')";
command = new MySqlCommand(query, connection);
command.ExecuteNonQuery();
}
else if (textBox1.Text != null && comboBox1.Text != "Teachers" && comboBox2.Text == "Other" && checkBox2.Checked == false)
{
string query = "INSERT INTO attendance( SID, TName, CType, Date, Time, Status, Action) VALUES('" + textBox1.Text + "','" + comboBox1.Text + "','" + comboBox3.Text + "','" + currentDate + "','" + currentTime + "','" + comboBox2.Text + "','In')";
command = new MySqlCommand(query, connection);
command.ExecuteNonQuery();
}
else if (textBox1.Text != null && comboBox1.Text != "Teachers" && comboBox2.Text == "Entrance" && checkBox2.Checked)
{
string query = "INSERT INTO attendance( SID, TName, CType, Date, Time, Status, Action) VALUES('" + textBox1.Text + "','" + comboBox1.Text + "','" + comboBox3.Text + "','" + currentDate + "','" + currentTime + "','" + comboBox2.Text + "','Out')";
command = new MySqlCommand(query, connection);
if (command.ExecuteNonQuery() == 1)
{
try
{
string tel = "+94";
string tel1 = Parent;
SerialPort sp = new SerialPort();
sp.PortName = "COM4";
sp.Open();
sp.WriteLine("AT" + Environment.NewLine);
Thread.Sleep(1000);
sp.WriteLine("AT+CMGF=1" + Environment.NewLine);
Thread.Sleep(1000);
sp.WriteLine("AT+CSCS=\"GSM\"" + Environment.NewLine);
Thread.Sleep(1000);
sp.WriteLine("AT+CMGS=\"" + tel + tel1 + "\"" + Environment.NewLine);
Thread.Sleep(1000);
sp.WriteLine(Name + " left at " + currentTime + " on " + currentDate + " from the class" + Environment.NewLine + "TUITON MASTER" + Environment.NewLine);
sp.Write(new byte[] { 26 }, 0, 1);
Thread.Sleep(1000);
var response = sp.ReadExisting();
sp.Close();
}
catch (Exception ex)
{
MessageBox.Show("Usb Modem Issue");
}
}
else
{
MessageBox.Show("Error");
}
}
else if (textBox1.Text != null && comboBox1.Text != "Teachers" && comboBox2.Text == "Interval" && checkBox2.Checked)
{
string query = "INSERT INTO attendance( SID, TName, CType, Date, Time, Status, Action) VALUES('" + textBox1.Text + "','" + comboBox1.Text + "','" + comboBox3.Text + "','" + currentDate + "','" + currentTime + "','" + comboBox2.Text + "','Out')";
command = new MySqlCommand(query, connection);
command.ExecuteNonQuery();
}
else if (textBox1.Text != null && comboBox1.Text != "Teachers" && comboBox2.Text == "Other" && checkBox2.Checked)
{
string query = "INSERT INTO attendance( SID, TName, CType, Date, Time, Status, Action) VALUES('" + textBox1.Text + "','" + comboBox1.Text + "','" + comboBox3.Text + "','" + currentDate + "','" + currentTime + "','" + comboBox2.Text + "','Out')";
command = new MySqlCommand(query, connection);
command.ExecuteNonQuery();
}
}
else
{
MessageBox.Show("Unpaid");
}
connection.Close();
}
private void label9_Click(object sender, EventArgs e)
{
string currentMonth = DateTime.Now.ToString("MMMM");
string currentYear = DateTime.Now.Year.ToString();
string currentDate = DateTime.Now.Date.ToString("yyyy.MM.dd");
string currentTime = DateTime.Now.ToString("h:mm tt");
//end class
try
{
connection.Open();
MySqlDataAdapter sda = new MySqlDataAdapter("select SID from payment where PMonth= '" + currentMonth + "' and Year='" + currentYear + "' and TName='" + comboBox1.Text + "'and CType='" + comboBox3.Text + "'", connection);
DataTable dt = new DataTable();
sda.Fill(dt);
dataGridView3.DataSource = dt;
string[] end = new string[dataGridView3.Rows.Count];
int i = 0;
foreach (DataGridViewRow row in dataGridView3.Rows)
{
end[i] = row.Cells[0].Value != null ? row.Cells[0].Value.ToString() : string.Empty;
i++;
}
for (i = 0; i < end.Length - 1; i++)
{
string Query = "select * from student where SID='" + end[i] + "'";
MySqlCommand command = new MySqlCommand(Query, connection);
reader = command.ExecuteReader();
reader.Read();
string tel1 = reader.GetString("Parent");
string name = reader.GetString("Name");
reader.Close();
string tel = "+94";
SerialPort sp = new SerialPort();
sp.PortName = "COM4";
sp.Open();
sp.WriteLine("AT" + Environment.NewLine);
Thread.Sleep(1000);
sp.WriteLine("AT+CMGF=1" + Environment.NewLine);
Thread.Sleep(1000);
sp.WriteLine("AT+CSCS=\"GSM\"" + Environment.NewLine);
Thread.Sleep(1000);
sp.WriteLine("AT+CMGS=\"" + tel + tel1 + "\"" + Environment.NewLine);
Thread.Sleep(1000);
sp.WriteLine("Mr/Mrs " + comboBox1.Text + "'s class now ended." + Environment.NewLine + "TUITION MASTER" + Environment.NewLine);
sp.Write(new byte[] { 26 }, 0, 1);
Thread.Sleep(1000);
var response = sp.ReadExisting();
sp.Close();
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Something Went Wrong!");
}
}
public void UpdateZhongjiangResult(DateTime touzhushijian)
{
string conStr = "server=localhost;User Id=root;database=aicai;" +
"Password=root;Character Set=utf8;";
DataSet ds = new DataSet();
using (MySqlConnection mySqlCon = new MySqlConnection(conStr))
{
mySqlCon.Open();
using (MySqlCommand mySqlCom = mySqlCon.CreateCommand())
{
MySqlDataAdapter da = new MySqlDataAdapter();
string sql = @"
SELECT
*
FROM
yuce_app
where riqi = ?touzhushijian
";
//--and y.Yucetype = '[竞彩足球]'
mySqlCom.CommandText = sql;
mySqlCom.Parameters.AddWithValue("?touzhushijian", touzhushijian.ToString("yyyy-MM-dd"));
da.SelectCommand = mySqlCom;
da.Fill(ds);
}
foreach (DataRow row in ds.Tables[0].Rows)
{
string id = row["id"].ToString();
string riqi = row["riqi"].ToString();
string bianhao = row["bianhao"].ToString();
string yucespf = row["spfresult"].ToString();
string rangqiushu = row["rangqiushu"].ToString();
Kaijiang kaijiang = new KaijiangDAL().GetKaijangSpfResult(riqi, bianhao);
if (kaijiang != null)
{
int lucky = 0;
if (String.IsNullOrEmpty(yucespf))
{
continue;
}
if (!string.IsNullOrEmpty(kaijiang.SpfResult))
{
if (rangqiushu == "0")
{
if (yucespf.IndexOf(kaijiang.SpfResult) != -1)
{
lucky = 1;
}
else
{
lucky = 2; //不中奖
}
}
else
{
if (yucespf.IndexOf(kaijiang.RqspfResult) != -1)
{
lucky = 1;
}
else
{
lucky = 2; //不中奖
}
}
UpdateYuceDetail(id, lucky, kaijiang, rangqiushu);
}
}
}
}
}
public bool ValidarSiElRegistroEstaVinculado(ProductoLoteEN oRegistroEN, DatosDeConexionEN oDatos, string TipoDeOperacion)
{
oTransaccionesAD = new TransaccionesAD();
try
{
Cnn = new MySqlConnection(TraerCadenaDeConexion(oDatos));
Cnn.Open();
Comando = new MySqlCommand();
Comando.Connection = Cnn;
Comando.CommandType = CommandType.StoredProcedure;
Comando.CommandText = "ValidarSiElRegistroEstaVinculadoUnaTabla";
Comando.Parameters.Add(new MySqlParameter("@CampoABuscar_", MySqlDbType.VarChar, 200)).Value = "idLoteDelProducto";
Comando.Parameters.Add(new MySqlParameter("@ValorCampoABuscar", MySqlDbType.Int32)).Value = oRegistroEN.idLoteDelProducto;
Comando.Parameters.Add(new MySqlParameter("@ExcluirTabla_", MySqlDbType.VarChar, 200)).Value = "ProductoLote";
Adaptador = new MySqlDataAdapter();
DT = new DataTable();
Adaptador.SelectCommand = Comando;
Adaptador.Fill(DT);
if (DT.Rows[0].ItemArray[0].ToString().ToUpper() == "NINGUNA".ToUpper())
{
return(false);
}
else
{
this.Error = String.Format("La Operación: '{1}', {0} no se puede completar por que el registro: {0} '{2}', {0} se encuentra asociado con: {0} {3}", Environment.NewLine, TipoDeOperacion, InformacionDelRegistro(oRegistroEN), oTransaccionesAD.ConvertirValorDeLaCadena(DT.Rows[0].ItemArray[0].ToString()));
DescripcionDeOperacion = this.Error;
//Agregamos la Transacción....
TransaccionesEN oTran = InformacionDelaTransaccion(oRegistroEN, "VALIDAR", "VALIDAR SI EL REGISTRO ESTA VINCULADO", "CORRECTO");
oTransaccionesAD.Agregar(oTran, oDatos);
return(true);
}
}
catch (Exception ex)
{
this.Error = ex.Message;
DescripcionDeOperacion = string.Format("Se produjo el seguiente error: '{2}' al validar el registro. {0} {1} ", Environment.NewLine, InformacionDelRegistro(oRegistroEN), ex.Message);
//Agregamos la Transacción....
TransaccionesEN oTran = InformacionDelaTransaccion(oRegistroEN, "VALIDAR", "VALIDAR SI EL REGISTRO ESTA VINCULADO", "ERROR");
oTransaccionesAD.Agregar(oTran, oDatos);
return(false);
}
finally
{
if (Cnn != null)
{
if (Cnn.State == ConnectionState.Open)
{
Cnn.Close();
}
}
Cnn = null;
Comando = null;
Adaptador = null;
oTransaccionesAD = null;
}
}
public void ParameterLengths()
{
if (Version < new Version(4, 1)) return;
execSQL("CREATE TABLE Test (id int, name VARCHAR(255))");
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?id, ?name)", conn);
cmd.Parameters.Add("?id", MySqlDbType.Int32);
cmd.Parameters.Add("?name", MySqlDbType.VarChar);
cmd.Parameters[1].Size = 255;
cmd.Prepare();
cmd.Parameters[0].Value = 1;
cmd.Parameters[1].Value = "short string";
cmd.ExecuteNonQuery();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(1, dt.Rows[0]["id"]);
Assert.AreEqual("short string", dt.Rows[0]["name"]);
}
/// <summary>
/// 获取需要上道的设备id
/// </summary>
/// <returns></returns>
public int GetIsRegionID(string termCode)
{
//List<jcTerminal> jcTerminalList = new List<jcTerminal>();
int count = 0;
JcTerminal jcTerminal = new JcTerminal();
MySqlCommand sqlCmd = new MySqlCommand();
sqlCmd.Connection = m_Connection;
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = "select count(*) from jc_terminal where in_region = 0 and is_fault = 0 and term_code = '" + termCode + "' ";
MySqlDataAdapter sqlAdapter = new MySqlDataAdapter();
sqlAdapter.SelectCommand = sqlCmd;
#region
//DataTable dt = new DataTable();
//try
//{
// sqlAdapter.Fill(dt);
//}
//catch (Exception)
//{
//}
//MySqlDataReader reader = sqlCmd.ExecuteReader();
//try
//{
// if (reader != null)
// {
// if (reader.HasRows)
// {
// while (reader.Read())
// {
// for (int i = 0; i < reader.FieldCount; i++)
// {
// Console.WriteLine(reader[i]);
// }
// jcTerminal jcTerminal = new jcTerminal();
// jcTerminal.TermCode = reader["term_code"].ToString();
// jcTerminalList.Add(jcTerminal);
// }
// }
// else
// {
// Console.WriteLine("没有查到数据！");
// }
// }
//}
//catch (Exception ex)
//{
// MessageBox.Show(ex.Message + "数据库操作异常！！");
//}
//Console.WriteLine("==============================查到的数据" + jcTerminalList);
#endregion
try
{
count = Convert.ToInt32(sqlCmd.ExecuteScalar());
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.WriteLine("查询到的总条数" + count);
return(count);
}
public ActionResult Edit(DocManger docManger)
{
string result = "";
string getimage = docManger.Image;
HttpPostedFileBase files = Request.Files["filename"];
//docManger.File = Request.Files["filename"];
string fileName = files.FileName;
//Console.WriteLine(fileName);
if (fileName == "")
{
docManger.Image = getimage;
}
else
{
string fileFormat = fileName.Split('.')[fileName.Split('.').Length - 1]; // 以“.”截取，获取“.”后面的文件后缀
Regex imageFormat = new Regex(@"^(bmp)|(png)|(gif)|(jpg)|(jpeg)"); // 验证文件后缀的表达式（这段可以限制上传文件类型）
Console.WriteLine(Server.MapPath("~/"));
if (string.IsNullOrEmpty(fileName) || !imageFormat.IsMatch(fileFormat)) // 验证后缀，判断文件是否是所要上传的格式
{
result = "error";
}
else
{
string timeStamp = DateTime.Now.Ticks.ToString(); // 获取当前时间的string类型
string firstFileName = timeStamp.Substring(0, timeStamp.Length - 4); // 通过截取获得文件名
string imageStr = "pic/"; // 获取保存附件的项目文件夹
string uploadPath = Server.MapPath("~/" + imageStr); // 将项目路径与文件夹合并
string pictureFormat = fileName.Split('.')[fileName.Split('.').Length - 1]; // 设置文件格式
string fileNames = firstFileName + "." + fileFormat; // 设置完整（文件名+文件格式）
string saveFile = uploadPath + fileNames; //文件路径
files.SaveAs(saveFile); // 保存文件
// 如果单单是上传，不用保存路径的话，下面这行代码就不需要写了！
result = "http://58.192.132.31:9011/" + imageStr + fileNames; // 设置数据库保存的路径
docManger.Image = result;
}
}
if (ModelState.IsValid)
{
string a = Request.Form["Position"];
MySqlConnection mysql = getMySqlConnection();
MySqlCommand mySqlCommand = getSqlCommand(" UPDATE user set name=" +
"\"" + docManger.Name + "\"" + ",sex=" + docManger.Sex + ",phone=" + "\"" + docManger.Phone + "\"" + ",password="******"\"" + docManger.Password + "\"" + ",position=" + docManger.Position + ",goodat=" + "\"" + docManger.Goodat + "\"" + ",introduction=" +
"\"" + docManger.Introduction + "\"" + ",Image=" + "\"" + docManger.Image + "\"" +
",gonghao=" + "'" + docManger.gonghao + "'" + ",danwei=" + "'" + docManger.danwei +
"' where id=" + docManger.Id, mysql);
mysql.Open();
MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
mySqlCommand.ExecuteNonQuery();
mysql.Close();
return(RedirectToAction("Index"));
}
else
{
List <string> Keys = ModelState.Keys.ToList();
//获取每一个key对应的ModelStateDictionary
foreach (var key in Keys)
{
var errors = ModelState[key].Errors.ToList();
//将错误描述输出到控制台
foreach (var error in errors)
{
Console.WriteLine(error.ErrorMessage);
}
}
}
ViewBag.SexList = GetSexList();
return(View(docManger));
}
public ActionResult Create([Bind(Include = "Id,Name,Sex,Phone,Password,Position,Goodat,Introduction,Image,Kind,Isnew,File")] DocManger docManger)
{
string result = "";
HttpPostedFileBase files = Request.Files["filename"];
//docManger.File = Request.Files["filename"];
string fileName = files.FileName;
string fileFormat = fileName.Split('.')[fileName.Split('.').Length - 1]; // 以“.”截取，获取“.”后面的文件后缀
Regex imageFormat = new Regex(@"^(bmp)|(png)|(gif)|(jpg)|(jpeg)"); // 验证文件后缀的表达式（这段可以限制上传文件类型）
Console.WriteLine(Server.MapPath("~/"));
if (string.IsNullOrEmpty(fileName) || !imageFormat.IsMatch(fileFormat)) // 验证后缀，判断文件是否是所要上传的格式
{
result = "error";
}
else
{
string timeStamp = DateTime.Now.Ticks.ToString(); // 获取当前时间的string类型
string firstFileName = timeStamp.Substring(0, timeStamp.Length - 4); // 通过截取获得文件名
string imageStr = "pic/"; // 获取保存附件的项目文件夹
string uploadPath = Server.MapPath("~/" + imageStr); // 将项目路径与文件夹合并
string pictureFormat = fileName.Split('.')[fileName.Split('.').Length - 1]; // 设置文件格式
string fileNames = firstFileName + "." + fileFormat; // 设置完整（文件名+文件格式）
string saveFile = uploadPath + fileNames; //文件路径
files.SaveAs(saveFile); // 保存文件
// 如果单单是上传，不用保存路径的话，下面这行代码就不需要写了！
result = "http://58.192.132.31:9011/" + imageStr + fileNames; // 设置数据库保存的路径
docManger.Image = result;
}
//return result;
//docManger.Image = SaveImage(file);
//docManger.Image = SaveImage();
docManger.Kind = "1";
docManger.Isnew = "1";
string gong = Request.Form["gonghao"];
string dan = Request.Form["danwei"];
if (ModelState.IsValid)
{
MySqlConnection mysql = getMySqlConnection();
MySqlCommand mySqlCommand = getSqlCommand("INSERT INTO user(name,sex,phone,password,position,goodat," +
"introduction,Image,kind,isnew,gonghao,danwei)VALUES" + "(" +
"'" + docManger.Name + "'" + "," + docManger.Sex + "," + "'" + docManger.Phone + "'" + "," +
"'" + docManger.Password + "'" + "," + docManger.Position + "," + "'" + docManger.Goodat + "'" + "," +
"'" + docManger.Introduction + "'" + "," + "'" + docManger.Image + "'" + "," + "'" +
docManger.Kind + "'" + "," + "'" + docManger.Isnew + "','" + gong + "','" + dan +
"')", mysql);
mysql.Open();
MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
mySqlCommand.ExecuteNonQuery();
mysql.Close();
return(RedirectToAction("Index"));
}
else
{
List <string> Keys = ModelState.Keys.ToList();
//获取每一个key对应的ModelStateDictionary
foreach (var key in Keys)
{
var errors = ModelState[key].Errors.ToList();
//将错误描述输出到控制台
foreach (var error in errors)
{
Console.WriteLine(error.ErrorMessage);
}
}
}
ViewBag.SexList = GetSexList();
return(View(docManger));
}
public void muestra_gr(string ser, string cor, string nomfcr) // muestra la grt
{
using (MySqlConnection conn = new MySqlConnection(DB_CONN_STR))
{
if (lib.procConn(conn) == true)
{
string consulta = "select a.id,a.fechopegr,a.sergui,a.numgui,a.numpregui,a.tidodegri,a.nudodegri,a.nombdegri,a.diredegri," +
"a.ubigdegri,a.tidoregri,a.nudoregri,a.nombregri,a.direregri,a.ubigregri,lo.descrizionerid as ORIGEN,a.dirorigen,a.ubiorigen," +
"ld.descrizionerid as DESTINO,a.dirdestin,a.ubidestin,a.docsremit,a.obspregri,a.clifingri,a.cantotgri,a.pestotgri," +
"a.tipmongri,a.tipcamgri,a.subtotgri,a.igvgri,round(a.totgri,1) as totgri,a.totpag,a.salgri,s.descrizionerid as ESTADO,a.impreso," +
"a.frase1,a.frase2,a.fleteimp,a.tipintrem,a.tipintdes,a.tippagpre,a.seguroE,a.userc,a.userm,a.usera," +
"a.serplagri,a.numplagri,a.plaplagri,a.carplagri,a.autplagri,a.confvegri,a.breplagri,a.proplagri," +
"ifnull(b.chocamcar,'') as chocamcar,ifnull(b.fecplacar,'') as fecplacar,ifnull(b.fecdocvta,'') as fecdocvta,ifnull(f.descrizionerid,'') as tipdocvta," +
"ifnull(b.serdocvta,'') as serdocvta,ifnull(b.numdocvta,'') as numdocvta,ifnull(b.codmonvta,'') as codmonvta," +
"ifnull(b.totdocvta,0) as totdocvta,ifnull(b.codmonpag,'') as codmonpag,ifnull(b.totpagado,0) as totpagado,ifnull(b.saldofina,0) as saldofina," +
"ifnull(b.feculpago,'') as feculpago,ifnull(b.estadoser,'') as estadoser,ifnull(c.razonsocial,'') as razonsocial,a.grinumaut," +
"ifnull(d.marca,'') as marca,ifnull(d.modelo,'') as modelo,a.teleregri as telrem,a.teledegri as teldes,ifnull(t.nombclt,'') as clifact," +
"u1.nombre AS distrem,u2.nombre as provrem,u3.nombre as deptrem,v1.nombre as distdes,v2.nombre as provdes,v3.nombre as deptdes,mo.descrizionerid as MON " +
"from cabguiai a " +
"left join controlg b on b.serguitra=a.sergui and b.numguitra=a.numgui " +
"left join desc_tdv f on f.idcodice=b.tipdocvta " +
"left join cabfactu t on t.tipdvta=a.tipdocvta and t.serdvta=a.serdocvta and t.numdvta=a.numdocvta " +
"left join anag_for c on c.ruc=a.proplagri and c.tipdoc=@tdep " +
"left join vehiculos d on d.placa=a.plaplagri " +
"left join anag_cli er on er.ruc=a.nudoregri and er.tipdoc=a.tidoregri " +
"left join anag_cli ed on ed.ruc=a.nudodegri and ed.tipdoc=a.tidodegri " +
"left join desc_est s on s.idcodice=a.estadoser " +
"left join desc_loc lo on lo.idcodice=a.locorigen " +
"left join desc_loc ld on ld.idcodice=a.locdestin " +
"left join desc_mon mo on mo.idcodice=a.tipmongri " +
"LEFT JOIN ubigeos u1 ON CONCAT(u1.depart, u1.provin, u1.distri)= a.ubigregri " +
"LEFT JOIN(SELECT* FROM ubigeos WHERE depart<>'00' AND provin<>'00' AND distri = '00') u2 ON u2.depart = left(a.ubigregri, 2) AND u2.provin = concat(substr(a.ubigregri, 3, 2)) " +
"LEFT JOIN (SELECT* FROM ubigeos WHERE depart<>'00' AND provin='00' AND distri = '00') u3 ON u3.depart = left(a.ubigregri, 2) " +
"LEFT JOIN ubigeos v1 ON CONCAT(v1.depart, v1.provin, v1.distri)= a.ubigdegri " +
"LEFT JOIN (SELECT* FROM ubigeos WHERE depart<>'00' AND provin<>'00' AND distri = '00') v2 ON v2.depart = left(a.ubigdegri, 2) AND v2.provin = concat(substr(a.ubigdegri, 3, 2)) " +
"LEFT JOIN (SELECT* FROM ubigeos WHERE depart<>'00' AND provin='00' AND distri = '00') v3 ON v3.depart = left(a.ubigdegri, 2) " +
"where a.sergui = @ser and a.numgui = @num";
using (MySqlCommand micon = new MySqlCommand(consulta, conn))
{
micon.Parameters.AddWithValue("@ser", ser);
micon.Parameters.AddWithValue("@num", cor);
micon.Parameters.AddWithValue("@tdep", "DOC002");
using (MySqlDataAdapter da = new MySqlDataAdapter(micon))
{
dtgrtcab.Clear();
da.Fill(dtgrtcab);
}
}
consulta = "select id,sergui,numgui,cantprodi,unimedpro,codiprodi,descprodi,round(pesoprodi,1),precprodi,totaprodi " +
"from detguiai where sergui = @ser and numgui = @num";
using (MySqlCommand micon = new MySqlCommand(consulta, conn))
{
micon.Parameters.AddWithValue("@ser", ser);
micon.Parameters.AddWithValue("@num", cor);
using (MySqlDataAdapter da = new MySqlDataAdapter(micon))
{
dtgrtdet.Clear();
da.Fill(dtgrtdet);
}
}
}
// llenamos el set
setParaCrystal("GRT", nomfcr);
}
}
public dynamic GetDataBy <T>(T obj, string fieldName, string secondFieldName)
{
var dateBaseGetAttribute = typeof(DataBaseGetAttribute);
var fields = String.Empty;
var result = obj.GetType();
var properties = result.GetProperties();
var searchField = String.Empty;
var searchValue = String.Empty;
var secondSearchField = String.Empty;
var secondSearchValue = String.Empty;
var tableName = result.GetProperty("TableName").GetValue(obj, null);
var flagForSqlQuery = true;
foreach (PropertyInfo property in properties)
{
var field = property.Name;
var attributes = property.GetCustomAttributes(dateBaseGetAttribute, true);
if (!attributes.Any())
{
continue; //выбираем только те properties, которые помечены атрибутом DataBaseGetAttribute
}
fields += field + ",";
if (field == fieldName)
{
searchField = field;
searchValue = property.GetValue(obj, null).ToString();
if (searchValue.Contains('"'))
{
flagForSqlQuery = false;
}
}
if (secondFieldName != null)
{
if (field == secondFieldName)
{
secondSearchField = field;
secondSearchValue = property.GetValue(obj, null).ToString();
if (secondSearchValue.Contains('"'))
{
flagForSqlQuery = false;
}
}
}
}
string sql;
if (flagForSqlQuery)
{
sql = secondFieldName != null?String.Format("SELECT {1} FROM {3} WHERE {0} = \"{2}\" AND {4} = \"{5}\"", searchField, fields.Remove(fields.Length - 1), searchValue, tableName, secondSearchField, secondSearchValue)
: String.Format("SELECT {1} FROM {3} WHERE {0} = \"{2}\" ", searchField, fields.Remove(fields.Length - 1), searchValue, tableName);
}
else
{
sql = secondFieldName != null?String.Format("SELECT {1} FROM {3} WHERE {0} = '{2}' AND {4} = '{5}'", searchField, fields.Remove(fields.Length - 1), searchValue, tableName, secondSearchField, secondSearchValue)
: String.Format("SELECT {1} FROM {3} WHERE {0} = '{2}' ", searchField, fields.Remove(fields.Length - 1), searchValue, tableName);
}
var connectionString = Globals.Settings.DefaultConnectionStringName;
var connection = new MySqlConnection(connectionString);
connection.Open();
var command = new MySqlCommand {
CommandText = sql, Connection = connection
};
var adapter = new MySqlDataAdapter {
SelectCommand = command
};
var dataset = new DataSet();
adapter.Fill(dataset);
foreach (PropertyInfo property in properties)
{
var field = property.Name;
var attributes = property.GetCustomAttributes(dateBaseGetAttribute, true);
if (!attributes.Any())
{
continue; //выбираем только те properties, которые помечены атрибутом DataBaseGetAttribute
}
if ((dataset.Tables.Count > 0) && (dataset.Tables[0].Rows.Count > 0) && dataset.Tables[0].Rows[0][field] != DBNull.Value)
{
property.SetValue(obj, dataset.Tables[0].Rows[0][field], null);
}
}
connection.Close();
return(obj);
}
private void Kontrol(String filtreleme)
{
MySqlConnection bgl = new MySqlConnection(baglanti);
string ek = "where";
if (cmbYakitTuru.Text != "")
{
filtreleme += ek + " yakıt_turu = " + "'" + cmbYakitTuru.SelectedItem.ToString() + "'";
ek = "and";
}
if (cmbVitesTuru.Text != "")
{
filtreleme += ek + " vites_turu = '" + cmbVitesTuru.SelectedItem.ToString() + "'";
ek = "and";
}
if (cmbSehir.Text != "")
{
filtreleme += ek + " sehir = " + "'" + cmbSehir.SelectedItem.ToString() + "'";
ek = "and";
}
if (cmbRenk.Text.ToString() != "")
{
filtreleme += ek + " renk = " + "'" + cmbRenk.SelectedItem.ToString() + "'";
ek = "and";
}
if (txtmarka.Text != "")
{
filtreleme += ek + " araba_marka = " + "'" + txtmarka.Text.ToString() + "'";
ek = "and";
}
if (txtModel.Text != "")
{
filtreleme += ek + " araba_model = " + "'" + txtModel.Text.ToString() + "'";
ek = "and";
}
if (txtilanAdi.Text != "")
{
filtreleme += ek + " ilan_adi = " + "'" + txtilanAdi.Text.ToString() + "'";
ek = "and";
}
if (txtilantarih.Text != "")
{
filtreleme += ek + " ilan_tarih = " + "'" + txtilantarih.Text.ToString() + "'";
ek = "and";
}
if (txtkmmax.Text != "" & txtkmmin.Text != "")
{
filtreleme += ek + " ilan_km > " + txtkmmin.Text.ToString() + " and ilan_fiyat < " + txtkmmax.Text.ToString();
ek = "and";
}
if (txtfiyatmax.Text != "" & txtfiyatmin.Text != "")
{
filtreleme += ek + " ilan_fiyat > " + txtfiyatmin.Text.ToString() + " and ilan_fiyat < " + txtfiyatmax.Text.ToString();
ek = "and";
}
if (cmbSirala.Text != "")
{
ek = "order by";
if (cmbSehir.SelectedItem.ToString() == "a-z sırala")
{
filtreleme += ek + "tbl_ilan" + "ASC";
}
else if (cmbSehir.SelectedItem.ToString() == "z-a sırala")
{
filtreleme += ek + "tbl_ilan" + "DESC";
}
}
DataTable dt = new DataTable();
bgl.Open();
MySqlDataAdapter da = new MySqlDataAdapter(filtreleme, bgl);
da.Fill(dt);
dataGridView1.DataSource = dt;
bgl.Close();
}
private void createButton_Click(object sender, EventArgs e)
{
if (orderGridView.Rows.Count <= 0)
{
MessageBox.Show("There are no orders to be made.");
}
else
{
try
{
DBConnect db = new DBConnect();
MySqlConnection con = db.connect();
MySqlCommand comm = new MySqlCommand("SELECT id, reportDate FROM dailysalesreport WHERE reportDate = CURDATE()", con);
MySqlDataAdapter adp = new MySqlDataAdapter(comm);
DataTable dt = new DataTable();
adp.Fill(dt);
//CREATES SALES REPORT FOR THE DAY IF DOES NOT EXISTS YET
if (dt.Rows.Count == 0)
{
con.Open();
MessageBox.Show("Sales Report for the day has been created!");
MySqlCommand comm2 = new MySqlCommand("INSERT INTO dailysalesreport (reportDate) VALUES (NOW())", con);
comm2.ExecuteNonQuery();
adp.Fill(dt);
con.Close();
DSR_ID = Int32.Parse(dt.Rows[0]["id"].ToString());
}
else
{
//GRABS THE ID OF CURRENT DSR
DSR_ID = Int32.Parse(dt.Rows[0]["id"].ToString());
}
//INSERTS ORDER RECEIPTS INTO CURRENT DSR
con.Open();
MySqlCommand comm3 = new MySqlCommand("INSERT INTO order_receipt (dailysalesreport_id, orderTime, totalPrice) " +
"VALUES (" + DSR_ID + ", CURRENT_TIME()," + totalPrice + ")", con);
comm3.ExecuteNonQuery();
//SELECTS BOTH THE ID'S OF ORDER_RECEIPTS AND MENUITEM
comm = new MySqlCommand("SELECT id FROM order_receipt ORDER BY id DESC", con);
adp = new MySqlDataAdapter(comm);
DataTable dt2 = new DataTable();
adp.Fill(dt2);
//GRABS THE ID'S OF CURRENT ORDER RECEIPT AND MENUITEMS
int orderID = Int32.Parse(dt2.Rows[0]["id"].ToString());
MySqlCommand command1;
MySqlCommand comm4;
int menuitemID;
//ADDS QUANTITY TO MENUITEM
foreach (DataGridViewRow row in orderGridView.Rows)
{
command1 = new MySqlCommand("SELECT id FROM menuitem WHERE name = '" + row.Cells[0].Value.ToString() + "'", con);
adp = new MySqlDataAdapter(command1);
DataTable dt3 = new DataTable();
adp.Fill(dt3);
menuitemID = Int32.Parse(dt3.Rows[0]["id"].ToString());
comm4 = new MySqlCommand("INSERT INTO order_menuitem (order_id, menuitem_id, quantity) " +
"VALUES (" + orderID + "," + menuitemID + "," + Int32.Parse(row.Cells[2].Value.ToString()) + ")", con);
comm4.ExecuteNonQuery();
}
//UPDATES DSR REVENUE
String updateRevenue = ("UPDATE dailysalesreport SET revenue = revenue + " + (totalPrice - totalCostPrice) + " WHERE reportDate = CURDATE()");
MySqlCommand updateRev = new MySqlCommand(updateRevenue, con);
updateRev.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
MessageBox.Show("Transaction has been recorded!");
orderGridView.Rows.Clear();
totalPrice = 0.00f;
totalCostPrice = 0.00f;
amountLabel.Text = "P" + totalPrice.ToString("0.00");
}
}
public DBAdapter(int cType, object cLink,string Query)
{
this.cType = cType;
this.cLink = cLink;
switch (cType)
{
case 0:
Adapter = new MySqlDataAdapter(Query, (MySqlConnection)cLink);
break;
case 1:
Adapter = new SqlDataAdapter(Query, (SqlConnection)cLink);
break;
case 2:
Adapter = new SQLiteDataAdapter(Query, (SQLiteConnection)cLink);
break;
}
}
/// <summary>
/// 获取上道区域
/// </summary>
/// <returns></returns>
public List <string> GetRoad()
{
List <string> txRailroadsList = new List <string>();
MySqlCommand sqlCmd = new MySqlCommand();
sqlCmd.Connection = m_Connection;
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = "select road_id,direction,road_name from tx_railroad";
MySqlDataAdapter sqlAdapter = new MySqlDataAdapter();
sqlAdapter.SelectCommand = sqlCmd;
try
{
reader = sqlCmd.ExecuteReader();
#region
//try
//{
// if (reader != null)
// {
// if (reader.HasRows)
// {
// while (reader.Read())
// {
// for (int i = 0; i < reader.FieldCount; i++)
// {
// TxRailroad txRailroad = new TxRailroad()
// {
// RoadId = reader.GetInt32(0),
// RoadName = reader.GetString(1)
// };
// txRailroadsList.Add(txRailroad);
// Console.WriteLine(reader[i]);
// }
// }
// }
// else
// {
// Console.WriteLine("没有查到数据！");
// }
// }
//}
//catch (Exception ex)
//{
// MessageBox.Show(ex.Message + "数据库操作异常！！");
//}
//Console.WriteLine("==============================查到的数据" + txRailroadsList.ToString());
#endregion
while (reader.Read())
{
txRailroadsList.Add(reader["road_name"].ToString());
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
//关闭MySqlDataAdapter
reader.Close();
}
return(txRailroadsList);
}
private void btnImprimir_Click(object sender, EventArgs e)
{
//Conexion a la base de datos
MySqlConnection myConexion = new MySqlConnection(clsConexion.ConectionString);
// Creando el command que ejecutare
MySqlCommand myCommand = new MySqlCommand();
// Creando el Data Adapter
MySqlDataAdapter myAdapter = new MySqlDataAdapter();
// Creando el String Builder
StringBuilder sbQuery = new StringBuilder();
// Otras variables del entorno
string cWhere = " WHERE 1 = 1";
string cUsuario = "";
string cTitulo = "";
try
{
// Abro conexion
myConexion.Open();
// Creo comando
myCommand = myConexion.CreateCommand();
// Adhiero el comando a la conexion
myCommand.Connection = myConexion;
// Filtros de la busqueda
string fechadesde = fechaDesde.Value.ToString("yyyy-MM-dd");
string fechahasta = fechaHasta.Value.ToString("yyyy-MM-dd");
cWhere = cWhere + " AND fecha >= " + "'" + fechadesde + "'" + " AND fecha <= " + "'" + fechahasta + "'" + "";
sbQuery.Clear();
sbQuery.Append("SELECT id, fecha, monto");
sbQuery.Append(" FROM ventas ");
sbQuery.Append(cWhere);
// Paso los valores de sbQuery al CommandText
myCommand.CommandText = sbQuery.ToString();
// Creo el objeto Data Adapter y ejecuto el command en el
myAdapter = new MySqlDataAdapter(myCommand);
// Creo el objeto Data Table
DataTable dtVentas = new DataTable();
// Lleno el data adapter
myAdapter.Fill(dtVentas);
// Cierro el objeto conexion
myConexion.Close();
// Verifico cantidad de datos encontrados
int nRegistro = dtVentas.Rows.Count;
if (nRegistro == 0)
{
MessageBox.Show("No Hay Datos Para Mostrar, Favor Verificar", "Sistema de Gestion Oficial Ejecutivo", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
else
{
//1ero.HACEMOS LA COLECCION DE PARAMETROS
//los campos de parametros contiene un objeto para cada campo de parametro en el informe
ParameterFields oParametrosCR = new ParameterFields();
//Proporciona propiedades para la recuperacion y configuracion del tipo de los parametros
ParameterValues oParametrosValuesCR = new ParameterValues();
//2do.CREAMOS LOS PARAMETROS
ParameterField oUsuario = new ParameterField();
ParameterField oFechaInicial = new ParameterField();
ParameterField oFechaFinal = new ParameterField();
//parametervaluetype especifica el TIPO de valor de parametro
//ParameterValueKind especifica el tipo de valor de parametro en la PARAMETERVALUETYPE de la Clase PARAMETERFIELD
oUsuario.ParameterValueType = ParameterValueKind.StringParameter;
oFechaInicial.ParameterValueType = ParameterValueKind.DateTimeParameter;
oFechaFinal.ParameterValueType = ParameterValueKind.DateTimeParameter;
//3ero.VALORES PARA LOS PARAMETROS
//ParameterDiscreteValue proporciona propiedades para la recuperacion y configuracion de
//parametros de valores discretos
ParameterDiscreteValue oUsuarioDValue = new ParameterDiscreteValue();
oUsuarioDValue.Value = cUsuario;
ParameterDiscreteValue oFechaDValue = new ParameterDiscreteValue();
oFechaDValue.Value = fechadesde;
ParameterDiscreteValue oFechaFinDValue = new ParameterDiscreteValue();
oFechaFinDValue.Value = fechahasta;
//4to. AGREGAMOS LOS VALORES A LOS PARAMETROS
oUsuario.CurrentValues.Add(oUsuarioDValue);
oFechaInicial.CurrentValues.Add(oFechaDValue);
oFechaFinal.CurrentValues.Add(oFechaFinDValue);
//5to. AGREGAMOS LOS PARAMETROS A LA COLECCION
oParametrosCR.Add(oUsuario);
oParametrosCR.Add(oFechaInicial);
oParametrosCR.Add(oFechaFinal);
//nombre del parametro en CR (Crystal Reports)
oParametrosCR[0].Name = "cUsuario";
oParametrosCR[1].Name = "cFechaInicial";
oParametrosCR[2].Name = "cFechaFinal";
//nombre del TITULO DEL INFORME
cTitulo = "CUADRE DE VENTAS CANTINA";
//6to Instanciamos nuestro REPORTE
//Reportes.ListadoDoctores oListado = new Reportes.ListadoDoctores();
rptVentasPorFecha orptVentasPorFecha = new rptVentasPorFecha();
//pasamos el nombre del TITULO del Listado
//SumaryInfo es un objeto que se utiliza para leer,crear y actualizar las propiedades del reporte
// oListado.SummaryInfo.ReportTitle = cTitulo;
orptVentasPorFecha.SummaryInfo.ReportTitle = cTitulo;
//7mo. instanciamos nuestro el FORMULARIO donde esta nuestro ReportViewer
frmPrinter ofrmPrinter = new frmPrinter(dtVentas, orptVentasPorFecha, cTitulo);
//ParameterFieldInfo Obtiene o establece la colección de campos de parámetros.
ofrmPrinter.CrystalReportViewer1.ParameterFieldInfo = oParametrosCR;
ofrmPrinter.ShowDialog();
}
}
catch (Exception myEx)
{
MessageBox.Show("Error : " + myEx.Message, "Mostrando Reporte", MessageBoxButtons.OK,
MessageBoxIcon.Information);
//ExceptionLog.LogError(myEx, false);
return;
}
}
//boolean for activating the instant post
//add posted post at instant
private void instLoadPost()
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = login.dbConnection;
MySqlDataAdapter ad;
//query to check if the post is added at instant
string readpost = "SELECT * FROM post where chek = 'New' order by post_id DESC LIMIT 1";
MySqlCommand com = new MySqlCommand(readpost, con);
ad = new MySqlDataAdapter(com);
DataTable table1 = new DataTable();
ad.Fill(table1);
ad.Dispose();
bt = new BunifuFlatButton[table1.Rows.Count];
phot = new PictureBox[table1.Rows.Count];
btnName = new string[table1.Rows.Count];
try
{
con.Open();
if (table1.Rows.Count > 0)
{
for (int j = 0; j < table1.Rows.Count; j++)
{
i++;
string post_id = table1.Rows[j][0].ToString();
//Image
phot[j] = new PictureBox();
phot[j].Width = 300;
phot[j].Height = 172;
phot[j].Name = post_id;
phot[j].SizeMode = PictureBoxSizeMode.Zoom;
phot[j].Cursor = Cursors.Hand;
phot[j].Click += new EventHandler(photoClickBtn_Click);
//takking photo to the panel
try
{
byte[] img = (byte[])table1.Rows[j][1];
MemoryStream ms = new MemoryStream(img);
phot[j].Image = Image.FromStream(ms);
}
catch
{
}
//Label
lb = new Label();
lb.Name = "lable" + k;
lb.AutoSize = true;
lb.Font = new Font("Cambria", 16);
try
{
lb.Text = table1.Rows[j][2].ToString();
}
catch
{
}
//User Full name
string fullname = table1.Rows[j][5].ToString();
Label uname = new Label();
uname = new Label();
uname.Name = table1.Rows[j][3].ToString();
uname.AutoSize = true;
uname.ForeColor = Color.DarkGreen;
uname.Font = new Font("Cambria", 14);
uname.Cursor = Cursors.Hand;
uname.Text = "Posted by: " + fullname;
uname.Click += new EventHandler(uname_Click);
//Button
bt[j] = new BunifuFlatButton();
bt[j].Text = "Comment";
bt[j].Name = post_id;
bt[j].Height = 25;
bt[j].Width = 100;
bt[j].Normalcolor = Color.FromArgb(0, 122, 204);
bt[j].OnHovercolor = Color.FromArgb(32, 9, 191);
bt[j].Activecolor = Color.FromArgb(0, 122, 204);
bt[j].Iconimage = null;
bt[j].TextAlign = ContentAlignment.MiddleCenter;
bt[j].BorderRadius = 5;
bt[j].Click += new EventHandler(commentPostBtn_Click);
//TextBox
txt = new BunifuCustomTextbox();
txt.Name = post_id;
txt.Width = 300;
txt.Height = 25;
txt.Multiline = true;
txt.Font = new Font("Cambria", 11);
txt.BackColor = Color.FromArgb(240, 240, 240);
txt.BorderStyle = BorderStyle.FixedSingle;
txt.ForeColor = Color.Black;
txt.BorderColor = Color.FromArgb(32, 9, 191);
txt.TextChanged += new EventHandler(txt_TextChanged);
//a panel for photo
FlowLayoutPanel photoPanel = new FlowLayoutPanel();
photoPanel.AutoSize = true;
photoPanel.Controls.Add(phot[j]);
////a panel forcomments
comPanel[j] = new FlowLayoutPanel();
comPanel[j].AutoSize = true;
comPanel[j].Name = post_id;
comPanel[j].BackColor = Color.LightGray;
comPanel[j].FlowDirection = FlowDirection.TopDown;
//a panel for comment and button
FlowLayoutPanel commentPanel = new FlowLayoutPanel();
commentPanel.AutoSize = true;
commentPanel.FlowDirection = FlowDirection.LeftToRight;
commentPanel.WrapContents = false;
commentPanel.Controls.Add(txt);
commentPanel.Controls.Add(bt[j]);
//a panel for caption
FlowLayoutPanel captionPanel = new FlowLayoutPanel();
captionPanel.AutoSize = true;
captionPanel.Controls.Add(lb);
//a panel for caption
FlowLayoutPanel unamePanel = new FlowLayoutPanel();
unamePanel.AutoSize = true;
unamePanel.Controls.Add(uname);
////adding comment and button to the panel
flowLayoutPanel1.Controls.Add(commentPanel);
//taking comments to panel
flowLayoutPanel1.Controls.Add(comPanel[j]);
////taking photo to panel
flowLayoutPanel1.Controls.Add(photoPanel);
////taking lable to the panel
flowLayoutPanel1.Controls.Add(captionPanel);
//adding user name to the panel
flowLayoutPanel1.Controls.Add(unamePanel);
//update the poast
string update = "update post set chek = 'Posted' where post_id = '" + post_id + "'";
MySqlCommand com1 = new MySqlCommand(update, con);
MySqlDataReader rd;
rd = com1.ExecuteReader();
rd.Close();
}
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
}
private void btnImprimir_Click(object sender, EventArgs e)
{
// GENERANDO EL LISTADO DE REQUERIMIENTOS EN GENERAL
if (chkTodos.Checked == true)
{
//Conexion a la base de datos
MySqlConnection myConexion = new MySqlConnection(clsConexion.ConectionString);
// Creando el command que ejecutare
MySqlCommand myCommand = new MySqlCommand();
// Creando el Data Adapter
MySqlDataAdapter myAdapter = new MySqlDataAdapter();
// Creando el String Builder
StringBuilder sbQuery = new StringBuilder();
// Otras variables del entorno
//string myStatus;
string cWhere = " WHERE 1 = 1";
string cUsuario = frmLogin.cUsuarioActual;
string cTitulo = "";
// Verifico si se quiere Requerimientos Pendientes o los Adquiridos.
if (chkAdquiridos.Checked == true)
{
cWhere = cWhere + " AND status = 1";
}
else
{
cWhere = cWhere + " AND status = 0";
}
try
{
// Abro conexion
myConexion.Open();
// Creo comando
myCommand = myConexion.CreateCommand();
// Adhiero el comando a la conexion
myCommand.Connection = myConexion;
// Filtros de la busqueda
sbQuery.Clear();
sbQuery.Append("SELECT requerimientos.id, dependencias.dependencia, requerimientos.requerimiento, requerimientos.monto_itbi as itbi,");
sbQuery.Append(" requerimientos.fecharegistro, requerimientos.fecharequerido, requerimientos.monto_cotizacion as monto");
sbQuery.Append(" FROM requerimientos ");
sbQuery.Append(" INNER JOIN dependencias ON dependencias.id = requerimientos.dependencia");
sbQuery.Append(cWhere);
sbQuery.Append(" ORDER BY dependencias.dependencia ASC");
// Paso los valores de sbQuery al CommandText
myCommand.CommandText = sbQuery.ToString();
// Creo el objeto Data Adapter y ejecuto el command en el
myAdapter = new MySqlDataAdapter(myCommand);
// Creo el objeto Data Table
DataTable dtRequerimientos = new DataTable();
// Lleno el data adapter
myAdapter.Fill(dtRequerimientos);
// Cierro el objeto conexion
myConexion.Close();
// Verifico cantidad de datos encontrados
int nRegistro = dtRequerimientos.Rows.Count;
if (nRegistro == 0)
{
MessageBox.Show("No Hay Datos Para Mostrar, Favor Verificar", "Sistema Gestion de Compras", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
else
{
//1ero.HACEMOS LA COLECCION DE PARAMETROS
//los campos de parametros contiene un objeto para cada campo de parametro en el informe
ParameterFields oParametrosCR = new ParameterFields();
//Proporciona propiedades para la recuperacion y configuracion del tipo de los parametros
ParameterValues oParametrosValuesCR = new ParameterValues();
//2do.CREAMOS LOS PARAMETROS
ParameterField oUsuario = new ParameterField();
//parametervaluetype especifica el TIPO de valor de parametro
//ParameterValueKind especifica el tipo de valor de parametro en la PARAMETERVALUETYPE de la Clase PARAMETERFIELD
oUsuario.ParameterValueType = ParameterValueKind.StringParameter;
//3ero.VALORES PARA LOS PARAMETROS
//ParameterDiscreteValue proporciona propiedades para la recuperacion y configuracion de
//parametros de valores discretos
ParameterDiscreteValue oUsuarioDValue = new ParameterDiscreteValue();
oUsuarioDValue.Value = cUsuario;
//4to. AGREGAMOS LOS VALORES A LOS PARAMETROS
oUsuario.CurrentValues.Add(oUsuarioDValue);
//5to. AGREGAMOS LOS PARAMETROS A LA COLECCION
oParametrosCR.Add(oUsuario);
//nombre del parametro en CR (Crystal Reports)
oParametrosCR[0].Name = "cUsuario";
//nombre del TITULO DEL INFORME
if (chkAdquiridos.Checked == true)
{
cTitulo = "LISTADO DE REQUERIMIENTOS ADQUIRIDOS POR DEPENDENCIAS";
}
else
{
cTitulo = "LISTADO DE REQUERIMIENTOS PENDIENTES POR DEPENDENCIAS";
}
//6to Instanciamos nuestro REPORTE
//Reportes.ListadoDoctores oListado = new Reportes.ListadoDoctores();
rptRequerimientosTodos orptRequerimientosTodos = new rptRequerimientosTodos();
//pasamos el nombre del TITULO del Listado
//SumaryInfo es un objeto que se utiliza para leer,crear y actualizar las propiedades del reporte
// oListado.SummaryInfo.ReportTitle = cTitulo;
orptRequerimientosTodos.SummaryInfo.ReportTitle = cTitulo;
//7mo. instanciamos nuestro el FORMULARIO donde esta nuestro ReportViewer
frmPrinter ofrmPrinter = new frmPrinter(dtRequerimientos, orptRequerimientosTodos, cTitulo);
//ParameterFieldInfo Obtiene o establece la colección de campos de parámetros.
ofrmPrinter.CrystalReportViewer1.ParameterFieldInfo = oParametrosCR;
ofrmPrinter.ShowDialog();
}
}
catch (Exception myEx)
{
MessageBox.Show("Error : " + myEx.Message, "Mostrando Reporte", MessageBoxButtons.OK,
MessageBoxIcon.Information);
//ExceptionLog.LogError(myEx, false);
return;
}
}
// GENERANDO EL LISTADO POR FECHAS DE REQUERIMIENTOS
else if (chkFecha.Checked == true)
{
//Conexion a la base de datos
MySqlConnection myConexion = new MySqlConnection(clsConexion.ConectionString);
// Creando el command que ejecutare
MySqlCommand myCommand = new MySqlCommand();
// Creando el Data Adapter
MySqlDataAdapter myAdapter = new MySqlDataAdapter();
// Creando el String Builder
StringBuilder sbQuery = new StringBuilder();
// Otras variables del entorno
//string myStatus;
string cWhere = " WHERE 1 = 1";
string cUsuario = frmLogin.cUsuarioActual;
string cTitulo = "";
// Verifico si se quiere Requerimientos Pendientes o los Adquiridos.
if (chkAdquiridos.Checked == true)
{
cWhere = cWhere + " AND status = 1";
}
else
{
cWhere = cWhere + " AND status = 0";
}
try
{
// Abro conexion
myConexion.Open();
// Creo comando
myCommand = myConexion.CreateCommand();
// Adhiero el comando a la conexion
myCommand.Connection = myConexion;
// Filtros de la busqueda
string fechaDesde = fechadesde.Value.ToString("yyyy-MM-dd");
string fechaHasta = fechahasta.Value.ToString("yyyy-MM-dd");
cWhere = cWhere + " AND fecharequerido >= " + "'" + fechaDesde + "'" + " AND fecharequerido <= " + "'" + fechaHasta + "'" + "";
sbQuery.Clear();
sbQuery.Append("SELECT requerimientos.id, dependencias.dependencia, requerimientos.requerimiento, requerimientos.monto_itbi as itbi,");
sbQuery.Append(" requerimientos.fecharegistro, requerimientos.fecharequerido, requerimientos.monto_cotizacion as monto");
sbQuery.Append(" FROM requerimientos ");
sbQuery.Append(" INNER JOIN dependencias ON dependencias.id = requerimientos.dependencia");
sbQuery.Append(cWhere);
sbQuery.Append(" ORDER BY dependencias.dependencia ASC");
// Paso los valores de sbQuery al CommandText
myCommand.CommandText = sbQuery.ToString();
// Creo el objeto Data Adapter y ejecuto el command en el
myAdapter = new MySqlDataAdapter(myCommand);
// Creo el objeto Data Table
DataTable dtRequerimientos = new DataTable();
// Lleno el data adapter
myAdapter.Fill(dtRequerimientos);
// Cierro el objeto conexion
myConexion.Close();
// Verifico cantidad de datos encontrados
int nRegistro = dtRequerimientos.Rows.Count;
if (nRegistro == 0)
{
MessageBox.Show("No Hay Datos Para Mostrar, Favor Verificar", "Sistema Gestion de Compras", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
else
{
//1ero.HACEMOS LA COLECCION DE PARAMETROS
//los campos de parametros contiene un objeto para cada campo de parametro en el informe
ParameterFields oParametrosCR = new ParameterFields();
//Proporciona propiedades para la recuperacion y configuracion del tipo de los parametros
ParameterValues oParametrosValuesCR = new ParameterValues();
//2do.CREAMOS LOS PARAMETROS
ParameterField oUsuario = new ParameterField();
ParameterField oFechaInicial = new ParameterField();
ParameterField oFechaFinal = new ParameterField();
//parametervaluetype especifica el TIPO de valor de parametro
//ParameterValueKind especifica el tipo de valor de parametro en la PARAMETERVALUETYPE de la Clase PARAMETERFIELD
oUsuario.ParameterValueType = ParameterValueKind.StringParameter;
oFechaInicial.ParameterValueType = ParameterValueKind.DateTimeParameter;
oFechaFinal.ParameterValueType = ParameterValueKind.DateTimeParameter;
//3ero.VALORES PARA LOS PARAMETROS
//ParameterDiscreteValue proporciona propiedades para la recuperacion y configuracion de
//parametros de valores discretos
ParameterDiscreteValue oUsuarioDValue = new ParameterDiscreteValue();
oUsuarioDValue.Value = cUsuario;
ParameterDiscreteValue oFechaDValue = new ParameterDiscreteValue();
oFechaDValue.Value = fechaDesde;
ParameterDiscreteValue oFechaFinDValue = new ParameterDiscreteValue();
oFechaFinDValue.Value = fechaHasta;
//4to. AGREGAMOS LOS VALORES A LOS PARAMETROS
oUsuario.CurrentValues.Add(oUsuarioDValue);
oFechaInicial.CurrentValues.Add(oFechaDValue);
oFechaFinal.CurrentValues.Add(oFechaFinDValue);
//5to. AGREGAMOS LOS PARAMETROS A LA COLECCION
oParametrosCR.Add(oUsuario);
oParametrosCR.Add(oFechaInicial);
oParametrosCR.Add(oFechaFinal);
//nombre del parametro en CR (Crystal Reports)
oParametrosCR[0].Name = "cUsuario";
oParametrosCR[1].Name = "cFechaInicial";
oParametrosCR[2].Name = "cFechaFinal";
//nombre del TITULO DEL INFORME
if (chkAdquiridos.Checked == true)
{
cTitulo = "LISTADO DE REQUERIMIENTOS ADQUIRIDOS POR FECHA";
}
else
{
cTitulo = "LISTADO DE REQUERIMIENTOS PENDIENTES POR FECHA";
}
//6to Instanciamos nuestro REPORTE
//Reportes.ListadoDoctores oListado = new Reportes.ListadoDoctores();
rptRequerimientosxFecha orptRequerimientosxFecha = new rptRequerimientosxFecha();
//pasamos el nombre del TITULO del Listado
//SumaryInfo es un objeto que se utiliza para leer,crear y actualizar las propiedades del reporte
// oListado.SummaryInfo.ReportTitle = cTitulo;
orptRequerimientosxFecha.SummaryInfo.ReportTitle = cTitulo;
//7mo. instanciamos nuestro el FORMULARIO donde esta nuestro ReportViewer
frmPrinter ofrmPrinter = new frmPrinter(dtRequerimientos, orptRequerimientosxFecha, cTitulo);
//ParameterFieldInfo Obtiene o establece la colección de campos de parámetros.
ofrmPrinter.CrystalReportViewer1.ParameterFieldInfo = oParametrosCR;
ofrmPrinter.ShowDialog();
}
}
catch (Exception myEx)
{
MessageBox.Show("Error : " + myEx.Message, "Mostrando Reporte", MessageBoxButtons.OK,
MessageBoxIcon.Information);
//ExceptionLog.LogError(myEx, false);
return;
}
}
// GENERANDO EL LISTADO POR DEPENDENCIAS
else if (chkDependencia.Checked == true)
{
//Conexion a la base de datos
MySqlConnection myConexion = new MySqlConnection(clsConexion.ConectionString);
// Creando el command que ejecutare
MySqlCommand myCommand = new MySqlCommand();
// Creando el Data Adapter
MySqlDataAdapter myAdapter = new MySqlDataAdapter();
// Creando el String Builder
StringBuilder sbQuery = new StringBuilder();
// Otras variables del entorno
//string myStatus;
string cWhere = " WHERE 1 = 1";
string cUsuario = frmLogin.cUsuarioActual;
string cTitulo = "";
// Verifico si se quiere Requerimientos Pendientes o los Adquiridos.
if (chkAdquiridos.Checked == true)
{
cWhere = cWhere + " AND status = 1";
}
else
{
cWhere = cWhere + " AND status = 0";
}
try
{
// Abro conexion
myConexion.Open();
// Creo comando
myCommand = myConexion.CreateCommand();
// Adhiero el comando a la conexion
myCommand.Connection = myConexion;
// Filtros de la busqueda
//string fechaDesde = fechadesde.Value.ToString("yyyy-MM-dd");
//string fechaHasta = fechahasta.Value.ToString("yyyy-MM-dd");
cWhere = cWhere + " AND requerimientos.dependencia =" + cmbDependencia.SelectedValue + "";
sbQuery.Clear();
sbQuery.Append("SELECT requerimientos.id, dependencias.dependencia, requerimientos.requerimiento, requerimientos.monto_itbi as itbi,");
sbQuery.Append(" requerimientos.fecharegistro, requerimientos.fecharequerido, requerimientos.monto_cotizacion as monto");
sbQuery.Append(" FROM requerimientos ");
sbQuery.Append(" INNER JOIN dependencias ON dependencias.id = requerimientos.dependencia");
sbQuery.Append(cWhere);
sbQuery.Append(" ORDER BY dependencias.dependencia ASC");
// Paso los valores de sbQuery al CommandText
myCommand.CommandText = sbQuery.ToString();
// Creo el objeto Data Adapter y ejecuto el command en el
myAdapter = new MySqlDataAdapter(myCommand);
// Creo el objeto Data Table
DataTable dtRequerimientos = new DataTable();
// Lleno el data adapter
myAdapter.Fill(dtRequerimientos);
// Cierro el objeto conexion
myConexion.Close();
// Verifico cantidad de datos encontrados
int nRegistro = dtRequerimientos.Rows.Count;
if (nRegistro == 0)
{
MessageBox.Show("No Hay Datos Para Mostrar, Favor Verificar", "Sistema Gestion de Compras", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
else
{
//1ero.HACEMOS LA COLECCION DE PARAMETROS
//los campos de parametros contiene un objeto para cada campo de parametro en el informe
ParameterFields oParametrosCR = new ParameterFields();
//Proporciona propiedades para la recuperacion y configuracion del tipo de los parametros
ParameterValues oParametrosValuesCR = new ParameterValues();
//2do.CREAMOS LOS PARAMETROS
ParameterField oUsuario = new ParameterField();
//parametervaluetype especifica el TIPO de valor de parametro
//ParameterValueKind especifica el tipo de valor de parametro en la PARAMETERVALUETYPE de la Clase PARAMETERFIELD
oUsuario.ParameterValueType = ParameterValueKind.StringParameter;
//3ero.VALORES PARA LOS PARAMETROS
//ParameterDiscreteValue proporciona propiedades para la recuperacion y configuracion de
//parametros de valores discretos
ParameterDiscreteValue oUsuarioDValue = new ParameterDiscreteValue();
oUsuarioDValue.Value = cUsuario;
//4to. AGREGAMOS LOS VALORES A LOS PARAMETROS
oUsuario.CurrentValues.Add(oUsuarioDValue);
//5to. AGREGAMOS LOS PARAMETROS A LA COLECCION
oParametrosCR.Add(oUsuario);
//nombre del parametro en CR (Crystal Reports)
oParametrosCR[0].Name = "cUsuario";
//nombre del TITULO DEL INFORME
if (chkAdquiridos.Checked == true)
{
cTitulo = "LISTADO DE REQUERIMIENTOS ADQUIRIDOS";
}
else
{
cTitulo = "LISTADO DE REQUERIMIENTOS PENDIENTES";
}
//6to Instanciamos nuestro REPORTE
//Reportes.ListadoDoctores oListado = new Reportes.ListadoDoctores();
rptRequerimientosxDependencia orptRequerimientosxDependencia = new rptRequerimientosxDependencia();
//pasamos el nombre del TITULO del Listado
//SumaryInfo es un objeto que se utiliza para leer,crear y actualizar las propiedades del reporte
// oListado.SummaryInfo.ReportTitle = cTitulo;
orptRequerimientosxDependencia.SummaryInfo.ReportTitle = cTitulo;
//7mo. instanciamos nuestro el FORMULARIO donde esta nuestro ReportViewer
frmPrinter ofrmPrinter = new frmPrinter(dtRequerimientos, orptRequerimientosxDependencia, cTitulo);
//ParameterFieldInfo Obtiene o establece la colección de campos de parámetros.
ofrmPrinter.CrystalReportViewer1.ParameterFieldInfo = oParametrosCR;
ofrmPrinter.ShowDialog();
}
}
catch (Exception myEx)
{
MessageBox.Show("Error : " + myEx.Message, "Mostrando Reporte", MessageBoxButtons.OK,
MessageBoxIcon.Information);
//ExceptionLog.LogError(myEx, false);
return;
}
}
}
//function for retreaving posts from post table
private void loadPost()
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = login.dbConnection;
MySqlDataAdapter ad;
//reading data query
string readImage = "select * from post";
//read the comments
string loadcomments = "select post_id,comment,user_name,user_id from comments";
MySqlCommand com1 = new MySqlCommand(loadcomments, con);
DataTable table = new DataTable();
MySqlDataReader rd;
try
{
con.Open();
//taking the comments
rd = com1.ExecuteReader();
table.Load(rd);
rd.Close();
MySqlCommand com = new MySqlCommand(readImage, con);
ad = new MySqlDataAdapter(com);
DataTable table1 = new DataTable();
ad.Fill(table1);
bt = new BunifuFlatButton[table1.Rows.Count];
phot = new PictureBox[table1.Rows.Count];
btnName = new string[table1.Rows.Count];
comPanel = new FlowLayoutPanel[table1.Rows.Count];
count = table1.Rows.Count;
dbCount = table1.Rows.Count;
for (int j = 0; j < table1.Rows.Count; j++)
{
i++;
string post_id = table1.Rows[j][0].ToString();
//Image
phot[j] = new PictureBox();
phot[j].Width = 300;
phot[j].Height = 172;
phot[j].Name = post_id;
phot[j].SizeMode = PictureBoxSizeMode.Zoom;
phot[j].Cursor = Cursors.Hand;
phot[j].Click += new EventHandler(photoClickBtn_Click);
//takking photo to the panel
try
{
byte[] img = (byte[])table1.Rows[j][1];
MemoryStream ms = new MemoryStream(img);
phot[j].Image = Image.FromStream(ms);
}
catch
{
}
//Label
lb = new Label();
lb.Name = "lable" + k;
lb.AutoSize = true;
lb.Font = new Font("Cambria", 16);
try
{
lb.Text = table1.Rows[j][2].ToString();
}
catch
{
}
//User Full name
string fullname = table1.Rows[j][5].ToString();
Label uname = new Label();
uname = new Label();
uname.Name = table1.Rows[j][3].ToString();
uname.AutoSize = true;
uname.ForeColor = Color.DarkGreen;
uname.Cursor = Cursors.Hand;
uname.Font = new Font("Cambria", 14);
uname.Text = "Posted by: " + fullname;
uname.Click += new EventHandler(uname_Click);
//Button
bt[j] = new BunifuFlatButton();
bt[j].Text = "Comment";
bt[j].Name = post_id;
bt[j].Height = 25;
bt[j].Width = 100;
bt[j].Normalcolor = Color.FromArgb(0, 122, 204);
bt[j].OnHovercolor = Color.FromArgb(32, 9, 191);
bt[j].Activecolor = Color.FromArgb(0, 122, 204);
bt[j].Iconimage = null;
bt[j].TextAlign = ContentAlignment.MiddleCenter;
bt[j].BorderRadius = 5;
bt[j].Click += new EventHandler(commentPostBtn_Click);
//TextBox
txt = new BunifuCustomTextbox();
txt.Name = post_id;
txt.Width = 300;
txt.Height = 25;
txt.Multiline = true;
txt.Font = new Font("Cambria", 11);
txt.BackColor = Color.FromArgb(240, 240, 240);
txt.BorderStyle = BorderStyle.FixedSingle;
txt.ForeColor = Color.Black;
txt.BorderColor = Color.FromArgb(32, 9, 191);
txt.TextChanged += new EventHandler(txt_TextChanged);
//a panel for photo
FlowLayoutPanel photoPanel = new FlowLayoutPanel();
photoPanel.AutoSize = true;
photoPanel.Controls.Add(phot[j]);
////a panel forcomments
comPanel[j] = new FlowLayoutPanel();
comPanel[j].AutoSize = true;
comPanel[j].Name = post_id;
comPanel[j].BackColor = Color.LightGray;
comPanel[j].FlowDirection = FlowDirection.TopDown;
//adding data to comment panel
for (int i = 0; i < table.Rows.Count; i++)
{
if (comPanel[j].Name == table.Rows[i][0].ToString())
{
//label
Label coomm = new Label();
coomm.Font = new Font("Sitka Small", 11, FontStyle.Bold);
coomm.ForeColor = Color.FromArgb(30, 0, 40);
coomm.Text = ": " + table.Rows[i][1].ToString();
coomm.AutoSize = true;
//LinkLable for username
LinkLabel userbt = new LinkLabel();
userbt.Text = table.Rows[i][2].ToString();
userbt.Name = table.Rows[i][3].ToString();
userbt.ActiveLinkColor = Color.Gray;
userbt.LinkColor = Color.Gray;
userbt.AutoSize = true;
userbt.LinkBehavior = LinkBehavior.NeverUnderline;
userbt.Cursor = Cursors.Hand;
userbt.BackColor = Color.Transparent;
userbt.Font = new Font("Lucida Fax", 9, FontStyle.Bold);
userbt.TextAlign = ContentAlignment.MiddleLeft;
userbt.Click += new EventHandler(buttonBtn_Click);
//flowlayout to add comment and name
FlowLayoutPanel content = new FlowLayoutPanel();
content.FlowDirection = FlowDirection.LeftToRight;
content.WrapContents = false;
content.AutoSize = true;
//ading the comment to the panel
content.Controls.Add(userbt);
content.Controls.Add(coomm);
//adding the panel to the main panel
comPanel[j].AutoSize = true;
comPanel[j].Controls.Add(content);
}
else
{
}
}
//a panel for comment text and button
FlowLayoutPanel commentPanel = new FlowLayoutPanel();
commentPanel.AutoSize = true;
commentPanel.FlowDirection = FlowDirection.LeftToRight;
commentPanel.WrapContents = false;
commentPanel.Controls.Add(txt);
commentPanel.Controls.Add(bt[j]);
//a panel for caption
FlowLayoutPanel captionPanel = new FlowLayoutPanel();
captionPanel.AutoSize = true;
captionPanel.Controls.Add(lb);
//a panel for caption
FlowLayoutPanel unamePanel = new FlowLayoutPanel();
unamePanel.AutoSize = true;
unamePanel.Controls.Add(uname);
////adding comment and button to the panel
flowLayoutPanel1.Controls.Add(commentPanel);
//taking comments to panel
flowLayoutPanel1.Controls.Add(comPanel[j]);
////taking photo to panel
flowLayoutPanel1.Controls.Add(photoPanel);
////taking lable to the panel
flowLayoutPanel1.Controls.Add(captionPanel);
//adding user name to the panel
flowLayoutPanel1.Controls.Add(unamePanel);
}
ad.Dispose();
}
catch(MySqlException ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
}
private void label2_Click(object sender, EventArgs e)
{
string currentMonth = DateTime.Now.ToString("MMMM");
string currentYear = DateTime.Now.Year.ToString();
string currentDate = DateTime.Now.Date.ToString("yyyy.MM.dd");
string currentTime = DateTime.Now.ToString("h:mm tt");
errorProvider1.Clear();
errorProvider2.Clear();
errorProvider3.Clear();
errorProvider4.Clear();
if (textBox1.Text == "Student ID" || textBox1.Text == null)
{
errorProvider1.SetError(textBox1, "Student Id shouldn't be null");
}
else if (comboBox3.Text == "Type")
{
errorProvider4.SetError(comboBox3, "Please Select Class Type");
}
else if (comboBox1.Text == "Teachers")
{
errorProvider1.SetError(comboBox1, "Teachers Should be selected");
}
else if (comboBox2.Text == "Status")
{
errorProvider3.SetError(comboBox2, "Status Should be selected");
}
else
{
connection.Open();
MySqlDataAdapter sda = new MySqlDataAdapter("select * from student where SID='" + textBox1.Text + "' ", connection);
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
string GetInfo = "select * from student where SID='" + textBox1.Text + "'";
MySqlCommand command = new MySqlCommand(GetInfo, connection);
reader = command.ExecuteReader();
reader.Read();
string Name = reader.GetString("Name");
string Lname = reader.GetString("LName");
string ID = reader.GetString("ID");
string Parent = reader.GetString("Parent");
byte[] img = (byte[])(reader["img"]);
if (img == null)
{
pictureBox13.Image = null;
}
else
{
MemoryStream ms = new MemoryStream(img);
pictureBox13.Image = System.Drawing.Image.FromStream(ms);
}
label3.Text = Name;
label4.Text = Lname;
label5.Text = ID;
}
else
{
errorProvider1.SetError(textBox1, "Invalid Student Id");
}
connection.Close();
}
}
//the function to retrieve user acounts from the databases
private void showFriend()
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = login.dbConnection;
MySqlDataAdapter ad;
//reading data query
string readAccount = "select * from users where user_id <> '"+ login.user_id +"'";
//string to read userID from request table
string checkReq = "select * from requests where sender_id = '"+ login.user_id +"'";
con.Open();
//for request user_id fetch
MySqlCommand com1 = new MySqlCommand(checkReq, con);
ad = new MySqlDataAdapter(com1);
DataTable table = new DataTable();
ad.Fill(table);
//execute ones to check if the database is empty or not
object nullValue = com1.ExecuteScalar();
ReqUser_id = new int[table.Rows.Count];
for(int i =0; i< table.Rows.Count; i++)
{
ReqUser_id[i] = (int)table.Rows[i][1];
}
ad.Dispose();
//for users
MySqlCommand com = new MySqlCommand(readAccount, con);
ad = new MySqlDataAdapter(com);
DataTable table1 = new DataTable();
ad.Fill(table1);
i = 0;
for (int j = 0; j < table1.Rows.Count; j++)
{
string user_id = table1.Rows[j][0].ToString();
//if the database return null value
if(nullValue == null || nullValue == DBNull.Value)
{
//Image
PictureBox phot = new PictureBox();
phot.Width = 120;
phot.Height = 95;
phot.Name = user_id;
phot.SizeMode = PictureBoxSizeMode.Zoom;
phot.Cursor = Cursors.Hand;
phot.Click += new EventHandler(ProfilePhoto_Click);
//takking photo to the panel
try
{
byte[] img = (byte[])table1.Rows[j][7];
MemoryStream ms = new MemoryStream(img);
phot.Image = Image.FromStream(ms);
}
catch
{
}
//User Full name
string fullname = table1.Rows[j][1].ToString() + " " + table1.Rows[j][2].ToString();
Label uname = new Label();
uname = new Label();
uname.Name = table1.Rows[j][0].ToString();
uname.AutoSize = true;
uname.ForeColor = Color.DarkGreen;
uname.Cursor = Cursors.Hand;
uname.Font = new Font("Cambria", 11, FontStyle.Bold);
uname.Click += new EventHandler(uname_Click);
uname.Text = fullname;
//Button
BunifuFlatButton bt = new BunifuFlatButton();
bt.Name = user_id;
bt.Text = "Follow";
bt.Height = 30;
bt.Width = 120;
bt.Normalcolor = Color.FromArgb(0, 122, 204);
bt.OnHovercolor = Color.FromArgb(32, 9, 191);
bt.Activecolor = Color.FromArgb(0, 122, 204);
bt.Iconimage = null;
bt.TextAlign = ContentAlignment.MiddleCenter;
bt.BorderRadius = 5;
bt.Click += new EventHandler(addFriendBtn_Click);
//taking photo to panel
flowLayoutPanel2.Controls.Add(phot);
//adding user name to the panel
flowLayoutPanel2.Controls.Add(uname);
//adding button to the panel
flowLayoutPanel2.Controls.Add(bt);
}
//if the database doesnt return null value
else
{
try
{
//reseting if the requests index bound exeeds
if (i > (table.Rows.Count - 1))
{
i = table.Rows.Count - 1;
}
else
{
}
//checking if the request is available
if (ReqUser_id[i] != int.Parse(user_id))
{
//Image
PictureBox phot = new PictureBox();
phot.Width = 120;
phot.Height = 95;
phot.Name = user_id;
phot.SizeMode = PictureBoxSizeMode.Zoom;
phot.Cursor = Cursors.Hand;
phot.Click += new EventHandler(ProfilePhoto_Click);
//takking photo to the panel
try
{
byte[] img = (byte[])table1.Rows[j][7];
MemoryStream ms = new MemoryStream(img);
phot.Image = Image.FromStream(ms);
}
catch
{
}
//User Full name
string fullname = table1.Rows[j][1].ToString() + " " + table1.Rows[j][2].ToString();
Label uname = new Label();
uname = new Label();
uname.Name = table1.Rows[j][0].ToString();
uname.AutoSize = true;
uname.ForeColor = Color.DarkGreen;
uname.Cursor = Cursors.Hand;
uname.Font = new Font("Cambria", 11, FontStyle.Bold);
uname.Click += new EventHandler(uname_Click);
uname.Text = fullname;
//Button
BunifuFlatButton bt = new BunifuFlatButton();
bt.Name = user_id;
bt.Text = "Follow";
bt.Height = 30;
bt.Width = 120;
bt.Normalcolor = Color.FromArgb(0, 122, 204);
bt.OnHovercolor = Color.FromArgb(32, 9, 191);
bt.Activecolor = Color.FromArgb(0, 122, 204);
bt.Iconimage = null;
bt.TextAlign = ContentAlignment.MiddleCenter;
bt.BorderRadius = 5;
bt.Click += new EventHandler(addFriendBtn_Click);
//taking photo to panel
flowLayoutPanel2.Controls.Add(phot);
//adding user name to the panel
flowLayoutPanel2.Controls.Add(uname);
//adding button to the panel
flowLayoutPanel2.Controls.Add(bt);
}
else
{
}
i++;
}
catch
{
}
}
}
ad.Dispose();
con.Close();
}
private void label10_Click(object sender, EventArgs e)
{
string currentMonth = DateTime.Now.ToString("MMMM");
string currentYear = DateTime.Now.Year.ToString();
string currentDate = DateTime.Now.Date.ToString("yyyy.MM.dd");
string currentTime = DateTime.Now.ToString("h:mm tt");
connection.Close();
connection.Open();
MySqlDataAdapter sda = new MySqlDataAdapter("select SID from payment where PMonth= '" + currentMonth + "' and Year='" + currentYear + "' and TName='" + comboBox1.Text + "'and CType='" + comboBox3.Text + "'", connection);
DataTable dt = new DataTable();
sda.Fill(dt);
dataGridView1.DataSource = dt;
string[] column0Array = new string[dataGridView1.Rows.Count];
int i = 0;
foreach (DataGridViewRow row in dataGridView1.Rows)
{
column0Array[i] = row.Cells[0].Value != null ? row.Cells[0].Value.ToString() : string.Empty;
i++;
}
for (i = 0; i < column0Array.Length - 1; i++)
{
MySqlDataAdapter sda2 = new MySqlDataAdapter("select * from attendance where SID='" + column0Array[i] + "' and Date= '" + currentDate + "' and Status='Entrance' and Action='In'and TName='" + comboBox1.Text + "' and CType='" + comboBox3.Text + "'", connection);
DataTable dt2 = new DataTable();
sda2.Fill(dt2);
if (dt2.Rows.Count > 0)
{
MySqlDataAdapter sda3 = new MySqlDataAdapter("select * from attendance where SID='" + column0Array[i] + "' and Date= '" + currentDate + "' and Status='Entrance' and Action='Out'", connection);
DataTable dt3 = new DataTable();
sda3.Fill(dt3);
if (dt3.Rows.Count > 0)
{
//supiri
}
else
{
MySqlDataAdapter sda4 = new MySqlDataAdapter("SELECT * FROM attendance WHERE SID='" + column0Array[i] + "' AND TIME=(SELECT MAX(TIME) FROM attendance WHERE sid='" + column0Array[i] + "' and Date='" + currentDate + "') and TName='" + comboBox1.Text + "'and CType='" + comboBox3.Text + "' LIMIT 1", connection);
DataTable dt4 = new DataTable();
sda4.Fill(dt4);
if (dt4.Rows.Count > 0)
{
//padiri
try
{
string Query = "select * from student where SID='" + column0Array[i] + "'";
MySqlCommand command = new MySqlCommand(Query, connection);
reader = command.ExecuteReader();
reader.Read();
string tel1 = reader.GetString("Parent");
string name = reader.GetString("Name");
reader.Close();
string Query2 = "SELECT * FROM attendance WHERE SID='" + column0Array[i] + "' AND TIME=(SELECT MAX(TIME) FROM attendance WHERE sid='" + column0Array[i] + "' and Date='" + currentDate + "') and TName='" + comboBox1.Text + "'and CType='" + comboBox3.Text + "' LIMIT 1";
MySqlCommand command2 = new MySqlCommand(Query2, connection);
reader = command2.ExecuteReader();
reader.Read();
string time = reader.GetString("Time");
string tname = reader.GetString("TName");
string date = reader.GetString("Date");
reader.Close();
string tel = "+94";
SerialPort sp = new SerialPort();
sp.PortName = "COM4";
sp.Open();
sp.WriteLine("AT" + Environment.NewLine);
Thread.Sleep(1000);
sp.WriteLine("AT+CMGF=1" + Environment.NewLine);
Thread.Sleep(1000);
sp.WriteLine("AT+CSCS=\"GSM\"" + Environment.NewLine);
Thread.Sleep(1000);
sp.WriteLine("AT+CMGS=\"" + tel + tel1 + "\"" + Environment.NewLine);
Thread.Sleep(1000);
sp.WriteLine("You Child " + name + " was left from the class at: " + time + Environment.NewLine + "on: " + currentDate + "from MR/MRs/Ms" + tname + "'s class." + Environment.NewLine + "TUITION MASTER" + Environment.NewLine);
sp.Write(new byte[] { 26 }, 0, 1);
Thread.Sleep(1000);
var response = sp.ReadExisting();
sp.Close();
}
catch (Exception ex)
{
MessageBox.Show("Something Went Wrong!");
}
}
}
}
else
{
//wala
try
{
string Query = "select * from student where SID='" + column0Array[i] + "'";
MySqlCommand command = new MySqlCommand(Query, connection);
reader = command.ExecuteReader();
reader.Read();
string tel1 = reader.GetString("Parent");
string name = reader.GetString("Name");
reader.Close();
string tel = "+94";
SerialPort sp = new SerialPort();
sp.PortName = "COM4";
sp.Open();
sp.WriteLine("AT" + Environment.NewLine);
Thread.Sleep(1000);
sp.WriteLine("AT+CMGF=1" + Environment.NewLine);
Thread.Sleep(1000);
sp.WriteLine("AT+CSCS=\"GSM\"" + Environment.NewLine);
Thread.Sleep(1000);
sp.WriteLine("AT+CMGS=\"" + tel + tel1 + "\"" + Environment.NewLine);
Thread.Sleep(1000);
sp.WriteLine("Sorry.Your child, " + name + " not attended to MR/MRs " + comboBox1.Text + "'s class on today(" + currentDate + ")" + Environment.NewLine + "TUITION MASTER" + Environment.NewLine);
sp.Write(new byte[] { 26 }, 0, 1);
Thread.Sleep(1000);
var response = sp.ReadExisting();
sp.Close();
}
catch (Exception ex)
{
MessageBox.Show("Something Went Wrong!");
}
}
}
connection.Close();
}
public void BlobAsUtf8()
{
execSQL(@"CREATE TABLE Test(include_blob BLOB, include_tinyblob TINYBLOB,
include_longblob LONGBLOB, exclude_tinyblob TINYBLOB, exclude_blob BLOB,
exclude_longblob LONGBLOB)");
byte[] utf8_bytes = new byte[4] { 0xf0, 0x90, 0x80, 0x80 };
Encoding utf8 = Encoding.GetEncoding("UTF-8");
string utf8_string = utf8.GetString(utf8_bytes, 0, utf8_bytes.Length);
// insert our UTF-8 bytes into the table
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?p1, ?p2, ?p3, ?p4, ?p5, ?p5)", conn);
cmd.Parameters.AddWithValue("?p1", utf8_bytes);
cmd.Parameters.AddWithValue("?p2", utf8_bytes);
cmd.Parameters.AddWithValue("?p3", utf8_bytes);
cmd.Parameters.AddWithValue("?p4", utf8_bytes);
cmd.Parameters.AddWithValue("?p5", utf8_bytes);
cmd.Parameters.AddWithValue("?p6", utf8_bytes);
cmd.ExecuteNonQuery();
// now check that the on/off is working
string connStr = GetConnectionString(true) + ";Treat Blobs As UTF8=yes;BlobAsUTF8IncludePattern=.*";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", c);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataColumn col in dt.Columns)
{
Assert.AreEqual(typeof(string), col.DataType);
string s = (string)dt.Rows[0][0];
byte[] b = utf8.GetBytes(s);
Assert.AreEqual(utf8_string, dt.Rows[0][col.Ordinal].ToString());
}
}
// now check that exclusion works
connStr = GetConnectionString(true) + ";Treat Blobs As UTF8=yes;BlobAsUTF8ExcludePattern=exclude.*";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", c);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataColumn col in dt.Columns)
{
if (col.ColumnName.StartsWith("exclude"))
Assert.AreEqual(typeof(byte[]), col.DataType);
else
{
Assert.AreEqual(typeof(string), col.DataType);
Assert.AreEqual(utf8_string, dt.Rows[0][col.Ordinal].ToString());
}
}
}
// now check that inclusion works
connStr = GetConnectionString(true) + ";Treat Blobs As UTF8=yes;BlobAsUTF8IncludePattern=include.*";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", c);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataColumn col in dt.Columns)
{
if (col.ColumnName.StartsWith("include"))
{
Assert.AreEqual(typeof(string), col.DataType);
Assert.AreEqual(utf8_string, dt.Rows[0][col.Ordinal].ToString());
}
else
Assert.AreEqual(typeof(byte[]), col.DataType);
}
}
}
public void NotLastOneWins()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(100), dt DATETIME, tm TIME, `multi word` int, PRIMARY KEY(id))");
execSQL("INSERT INTO Test (id, name) VALUES (1, 'Test')");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
cb.ConflictOption = ConflictOption.CompareAllSearchableValues;
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
execSQL("UPDATE Test SET name='Test2' WHERE id=1");
try
{
dt.Rows[0]["name"] = "Test3";
da.Update(dt);
Assert.Fail("This should not work");
}
catch (DBConcurrencyException)
{
}
dt.Rows.Clear();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual("Test2", dt.Rows[0]["name"]);
}
public void RespectBinaryFlags()
{
if (Version.Major >= 5 && Version.Minor >= 5) return;
string connStr = GetConnectionString(true) + ";respect binary flags=true";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlDataAdapter da = new MySqlDataAdapter(
"SELECT CONCAT('Trädgårdsvägen', 1)", c);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.IsTrue(dt.Rows[0][0] is byte[]);
}
connStr = GetConnectionString(true) + ";respect binary flags=false";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlDataAdapter da = new MySqlDataAdapter(
"SELECT CONCAT('Trädgårdsvägen', 1)", c);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.IsTrue(dt.Rows[0][0] is string);
Assert.AreEqual("Trädgårdsvägen1", dt.Rows[0][0]);
}
}
public void SpecialCharactersInFieldNames()
{
execSQL("CREATE TABLE Test (`col%1` int PRIMARY KEY, `col()2` int, `col<>3` int, `col/4` int)");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
cb.ToString(); // keep the compiler happy
DataTable dt = new DataTable();
da.Fill(dt);
DataRow row = dt.NewRow();
row[0] = 1;
row[1] = 2;
row[2] = 3;
row[3] = 4;
dt.Rows.Add(row);
da.Update(dt);
}
private void buttonRegistr_Click(object sender, EventArgs e)
{
//if (isUserExist())
// return;
string card_type;
string access_gum;
string access_swimming;
Int32 Price;
if (card_VIP_check.Checked)
{
cardPrice.Text = "22500 рублей";
card_type = "vip";
access_gum = "yes";
access_swimming = "yes";
Price = 22500;
}
else
{
cardPrice.Text = "15000 рублей";
card_type = "normal";
access_gum = "yes";
access_swimming = "no";
Price = 15000;
}
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter();
MySqlDataAdapter adapter2 = new MySqlDataAdapter();
DB db = new DB();
MySqlCommand check = new MySqlCommand("SELECT phone FROM `customer` WHERE customer.phone= @phone ", db.getConnection());
check.Parameters.Add("@phone", MySqlDbType.VarChar).Value = customerPhoneField.Text;
//commandUp.Parameters.Add("@phone1", MySqlDbType.VarChar).Value = customerPhoneField.Text;
adapter.SelectCommand = check;
adapter.Fill(table);
db.openConnection();
if (table.Rows.Count > 0)
{
MySqlCommand command = new MySqlCommand("INSERT INTO `card`(`phone`, `price`, `card_type`, `validity`, `access_gum`, `access_swimming`) VALUES(@phone , @price ,@card_type ,CURDATE(), @access_gum, @access_swimming); UPDATE `card` SET `validity`= DATE_ADD(`validity`, INTERVAL 1 year) WHERE card.phone=@phone; ", db.getConnection());
command.Parameters.Add("@phone", MySqlDbType.VarChar).Value = customerPhoneField.Text;
command.Parameters.Add("@price", MySqlDbType.Int32).Value = Price;
command.Parameters.Add("@card_type", MySqlDbType.VarChar).Value = card_type;
command.Parameters.Add("@access_gum", MySqlDbType.VarChar).Value = access_gum;
command.Parameters.Add("@access_swimming", MySqlDbType.VarChar).Value = access_swimming;
adapter2.SelectCommand = command;
if (command.ExecuteNonQuery() == 2)
{
MessageBox.Show("Карта зарегистрированна");
curCard();
}
else
{
MessageBox.Show("Что то пошло не так");
}
}
else
{
MessageBox.Show("Карта не была привязана, проверьте правильность написания номера");
}
db.closeConnection();
}
public void UsingFunctions()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(100), dt DATETIME, tm TIME, `multi word` int, PRIMARY KEY(id))");
execSQL("INSERT INTO Test (id, name) VALUES (1,'test1')");
execSQL("INSERT INTO Test (id, name) VALUES (2,'test2')");
execSQL("INSERT INTO Test (id, name) VALUES (3,'test3')");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT id, name, now() as ServerTime FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows[0]["id"] = 4;
da.Update(dt);
da.SelectCommand.CommandText = "SELECT id, name, CONCAT(name, ' boo') as newname from Test where id=4";
dt.Clear();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual("test1", dt.Rows[0]["name"]);
Assert.AreEqual("test1 boo", dt.Rows[0]["newname"]);
dt.Rows[0]["id"] = 5;
da.Update(dt);
dt.Clear();
da.SelectCommand.CommandText = "SELECT * FROM Test WHERE id=5";
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual("test1", dt.Rows[0]["name"]);
da.SelectCommand.CommandText = "SELECT *, now() as stime FROM Test WHERE id<4";
cb = new MySqlCommandBuilder(da);
cb.ConflictOption = ConflictOption.OverwriteChanges;
da.InsertCommand = cb.GetInsertCommand();
}
private void Form1_Load(object sender, EventArgs e)
{
try
{
string query = "select * from Store";
MySqlDataAdapter da = new MySqlDataAdapter(query, dbconnection);
DataTable dt = new DataTable();
da.Fill(dt);
comFromStore.DataSource = dt;
comFromStore.DisplayMember = dt.Columns["Store_Name"].ToString();
comFromStore.ValueMember = dt.Columns["Store_ID"].ToString();
comFromStore.SelectedIndex = -1;
comFromStore.SelectedValue = FromStore;
query = "select * from Store where Store_ID<>" + comFromStore.SelectedValue.ToString();
da = new MySqlDataAdapter(query, dbconnection);
dt = new DataTable();
da.Fill(dt);
comToStore.DataSource = dt;
comToStore.DisplayMember = dt.Columns["Store_Name"].ToString();
comToStore.ValueMember = dt.Columns["Store_ID"].ToString();
comToStore.SelectedIndex = -1;
comToStore.SelectedValue = ToStore;
query = "select * from store_places where Store_ID=" + comToStore.SelectedValue.ToString();
da = new MySqlDataAdapter(query, dbconnection);
dt = new DataTable();
da.Fill(dt);
comStorePlace.DataSource = dt;
comStorePlace.DisplayMember = dt.Columns["Store_Place_Code"].ToString();
comStorePlace.ValueMember = dt.Columns["Store_Place_ID"].ToString();
query = "select * from type";
da = new MySqlDataAdapter(query, dbconnection);
dt = new DataTable();
da.Fill(dt);
comType.DataSource = dt;
comType.DisplayMember = dt.Columns["Type_Name"].ToString();
comType.ValueMember = dt.Columns["Type_ID"].ToString();
comType.Text = "";
query = "select * from factory";
da = new MySqlDataAdapter(query, dbconnection);
dt = new DataTable();
da.Fill(dt);
comFactory.DataSource = dt;
comFactory.DisplayMember = dt.Columns["Factory_Name"].ToString();
comFactory.ValueMember = dt.Columns["Factory_ID"].ToString();
comFactory.Text = "";
query = "select * from groupo";
da = new MySqlDataAdapter(query, dbconnection);
dt = new DataTable();
da.Fill(dt);
comGroup.DataSource = dt;
comGroup.DisplayMember = dt.Columns["Group_Name"].ToString();
comGroup.ValueMember = dt.Columns["Group_ID"].ToString();
comGroup.Text = "";
query = "select * from product";
da = new MySqlDataAdapter(query, dbconnection);
dt = new DataTable();
da.Fill(dt);
comProduct.DataSource = dt;
comProduct.DisplayMember = dt.Columns["Product_Name"].ToString();
comProduct.ValueMember = dt.Columns["Product_ID"].ToString();
comProduct.Text = "";
query = "select * from size";
da = new MySqlDataAdapter(query, dbconnection);
dt = new DataTable();
da.Fill(dt);
comSize.DataSource = dt;
comSize.DisplayMember = dt.Columns["Size_Value"].ToString();
comSize.ValueMember = dt.Columns["Size_ID"].ToString();
comSize.Text = "";
query = "select * from color";
da = new MySqlDataAdapter(query, dbconnection);
dt = new DataTable();
da.Fill(dt);
comColor.DataSource = dt;
comColor.DisplayMember = dt.Columns["Color_Name"].ToString();
comColor.ValueMember = dt.Columns["Color_ID"].ToString();
comColor.Text = "";
query = "select * from sort";
da = new MySqlDataAdapter(query, dbconnection);
dt = new DataTable();
da.Fill(dt);
comSort.DataSource = dt;
comSort.DisplayMember = dt.Columns["Sort_Value"].ToString();
comSort.ValueMember = dt.Columns["Sort_ID"].ToString();
comSort.Text = "";
query = "SELECT data.Data_ID,data.Code as 'الكود',type.Type_Name as 'النوع',concat(product.Product_Name, ' ', COALESCE(color.Color_Name, ''), ' ', data.Description, ' ', groupo.Group_Name, ' ', factory.Factory_Name, ' ', COALESCE(size.Size_Value, ''), ' ', COALESCE(sort.Sort_Value, '')) as 'الاسم',data.Carton as 'الكرتنة',transfer_product_details.Quantity as 'الكمية' FROM transfer_product_details INNER JOIN transfer_product ON transfer_product_details.TransferProduct_ID = transfer_product.TransferProduct_ID left JOIN store as storeTo ON storeTo.Store_ID = transfer_product.To_Store left join store as storeFrom on storeFrom.Store_ID = transfer_product.From_Store INNER JOIN data ON transfer_product_details.Data_ID = data.Data_ID LEFT JOIN color ON color.Color_ID = data.Color_ID LEFT JOIN size ON size.Size_ID = data.Size_ID LEFT JOIN sort ON sort.Sort_ID = data.Sort_ID INNER JOIN groupo ON data.Group_ID = groupo.Group_ID INNER JOIN factory ON factory.Factory_ID = data.Factory_ID INNER JOIN product ON product.Product_ID = data.Product_ID INNER JOIN type ON type.Type_ID = data.Type_ID where transfer_product.TransferProduct_ID=" + TransferProductID + " and transfer_product.Canceled=0 order by SUBSTR(data.Code, 1, 16),color.Color_Name,data.Description,data.Sort_ID";
da = new MySqlDataAdapter(query, dbconnection);
dt = new DataTable();
da.Fill(dt);
gridControl2.DataSource = dt;
gridView2.Columns[0].Visible = false;
gridView2.Columns["الاسم"].Width = 300;
loaded = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void searchdata(string valueTosearch)
{
try {
MySqlConnection con = new MySqlConnection("server=" + host + ";user id=" + userName + ";password="******";database=" + DataBase + "");
MySqlCommand cmd;
MySqlDataAdapter adptr;
DataTable table;
string sel;
if (radioButton1.Checked == true)
{
sel = "SELECT * FROM `dsdstudentdetails` WHERE `id` LIKE '" + valueTosearch + "%'";
cmd = new MySqlCommand(sel, con);
adptr = new MySqlDataAdapter(cmd);
table = new DataTable();
adptr.Fill(table);
dataGridView1.DataSource = table;
}
if (radioButton2.Checked == true)
{
sel = "SELECT * FROM `dsdstudentdetails` WHERE `name` LIKE '" + valueTosearch + "%'";
cmd = new MySqlCommand(sel, con);
adptr = new MySqlDataAdapter(cmd);
table = new DataTable();
adptr.Fill(table);
dataGridView1.DataSource = table;
}
if (radioButton6.Checked == true)
{
sel = "SELECT * FROM `dsdstudentdetails` WHERE `addres` LIKE '" + valueTosearch + "%'";
cmd = new MySqlCommand(sel, con);
adptr = new MySqlDataAdapter(cmd);
table = new DataTable();
adptr.Fill(table);
dataGridView1.DataSource = table;
}
if (radioButton3.Checked == true)
{
sel = "SELECT * FROM `dsdstudentdetails` WHERE `phoneNumber` LIKE '" + valueTosearch + "%'";
cmd = new MySqlCommand(sel, con);
adptr = new MySqlDataAdapter(cmd);
table = new DataTable();
adptr.Fill(table);
dataGridView1.DataSource = table;
}
if (radioButton4.Checked == true)
{
sel = "SELECT * FROM `dsdstudentdetails` WHERE `studentNumber` LIKE '" + valueTosearch + "%'";
cmd = new MySqlCommand(sel, con);
adptr = new MySqlDataAdapter(cmd);
table = new DataTable();
adptr.Fill(table);
dataGridView1.DataSource = table;
}
if (radioButton5.Checked == true)
{
sel = "SELECT * FROM `dsdstudentdetails` WHERE `date` LIKE '" + valueTosearch + "%'";
cmd = new MySqlCommand(sel, con);
adptr = new MySqlDataAdapter(cmd);
table = new DataTable();
adptr.Fill(table);
dataGridView1.DataSource = table;
}
if (radioButton7.Checked == true)
{
sel = "SELECT * FROM `dsdstudentdetails` WHERE `batch` LIKE '" + valueTosearch + "%'";
cmd = new MySqlCommand(sel, con);
adptr = new MySqlDataAdapter(cmd);
table = new DataTable();
adptr.Fill(table);
dataGridView1.DataSource = table;
}
con.Close();
}catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void comBox_SelectedValueChanged(object sender, EventArgs e)
{
try
{
if (loaded)
{
dbconnection.Close();
dbconnection.Open();
ComboBox comBox = (ComboBox)sender;
switch (comBox.Name)
{
case "comType":
if (loaded)
{
txtType.Text = comType.SelectedValue.ToString();
string query = "select * from factory inner join type_factory on factory.Factory_ID=type_factory.Factory_ID inner join type on type_factory.Type_ID=type.Type_ID where type_factory.Type_ID=" + txtType.Text;
MySqlDataAdapter da = new MySqlDataAdapter(query, dbconnection);
DataTable dt = new DataTable();
da.Fill(dt);
comFactory.DataSource = dt;
comFactory.DisplayMember = dt.Columns["Factory_Name"].ToString();
comFactory.ValueMember = dt.Columns["Factory_ID"].ToString();
comFactory.Text = "";
txtFactory.Text = "";
dbconnection.Open();
query = "select TypeCoding_Method from type where Type_ID=" + txtType.Text;
MySqlCommand com = new MySqlCommand(query, dbconnection);
int TypeCoding_Method = Convert.ToInt32(com.ExecuteScalar());
if (TypeCoding_Method == 1)
{
string query2 = "";
if (txtType.Text == "2" || txtType.Text == "1")
{
query2 = "select * from groupo where Factory_ID=-1";
}
else
{
query2 = "select * from groupo where Factory_ID=" + -Convert.ToInt32(txtType.Text) + " and Type_ID=" + txtType.Text;
}
MySqlDataAdapter da2 = new MySqlDataAdapter(query2, dbconnection);
DataTable dt2 = new DataTable();
da2.Fill(dt2);
comGroup.DataSource = dt2;
comGroup.DisplayMember = dt2.Columns["Group_Name"].ToString();
comGroup.ValueMember = dt2.Columns["Group_ID"].ToString();
comGroup.Text = "";
txtGroup.Text = "";
}
factoryFlage = true;
query = "select * from color where Type_ID=" + comType.SelectedValue.ToString();
da = new MySqlDataAdapter(query, dbconnection);
dt = new DataTable();
da.Fill(dt);
comColor.DataSource = dt;
comColor.DisplayMember = dt.Columns["Color_Name"].ToString();
comColor.ValueMember = dt.Columns["Color_ID"].ToString();
comColor.Text = "";
comFactory.Focus();
}
break;
case "comFactory":
if (factoryFlage)
{
txtFactory.Text = comFactory.SelectedValue.ToString();
string query = "select TypeCoding_Method from type where Type_ID=" + txtType.Text;
MySqlCommand com = new MySqlCommand(query, dbconnection);
int TypeCoding_Method = (int)com.ExecuteScalar();
if (TypeCoding_Method == 2)
{
string query2f = "select * from groupo where Type_ID=" + txtType.Text + " and Factory_ID=" + txtFactory.Text;
MySqlDataAdapter da2f = new MySqlDataAdapter(query2f, dbconnection);
DataTable dt2f = new DataTable();
da2f.Fill(dt2f);
comGroup.DataSource = dt2f;
comGroup.DisplayMember = dt2f.Columns["Group_Name"].ToString();
comGroup.ValueMember = dt2f.Columns["Group_ID"].ToString();
comGroup.Text = "";
txtGroup.Text = "";
}
groupFlage = true;
string query2 = "select * from size where Factory_ID=" + comFactory.SelectedValue.ToString();
MySqlDataAdapter da2 = new MySqlDataAdapter(query2, dbconnection);
DataTable dt2 = new DataTable();
da2.Fill(dt2);
comSize.DataSource = dt2;
comSize.DisplayMember = dt2.Columns["Size_Value"].ToString();
comSize.ValueMember = dt2.Columns["Size_ID"].ToString();
comSize.Text = "";
comGroup.Focus();
}
break;
case "comGroup":
if (groupFlage)
{
txtGroup.Text = comGroup.SelectedValue.ToString();
string supQuery = "", subQuery1 = "";
if (comType.SelectedValue.ToString() != "")
{
supQuery += " and product.Type_ID=" + comType.SelectedValue.ToString();
}
if (comFactory.SelectedValue.ToString() != "")
{
supQuery += " and product_factory_group.Factory_ID=" + comFactory.SelectedValue.ToString();
subQuery1 += " and Factory_ID=" + comFactory.SelectedValue.ToString();
}
string query3 = "select distinct product.Product_ID ,Product_Name from product inner join product_factory_group on product.Product_ID=product_factory_group.Product_ID where product_factory_group.Group_ID=" + comGroup.SelectedValue.ToString() + supQuery + " order by product.Product_ID";
MySqlDataAdapter da3 = new MySqlDataAdapter(query3, dbconnection);
DataTable dt3 = new DataTable();
da3.Fill(dt3);
comProduct.DataSource = dt3;
comProduct.DisplayMember = dt3.Columns["Product_Name"].ToString();
comProduct.ValueMember = dt3.Columns["Product_ID"].ToString();
comProduct.Text = "";
txtProduct.Text = "";
string query2 = "select * from size where Group_ID=" + comGroup.SelectedValue.ToString() + subQuery1;
MySqlDataAdapter da2 = new MySqlDataAdapter(query2, dbconnection);
DataTable dt2 = new DataTable();
da2.Fill(dt2);
comSize.DataSource = dt2;
comSize.DisplayMember = dt2.Columns["Size_Value"].ToString();
comSize.ValueMember = dt2.Columns["Size_ID"].ToString();
comSize.Text = "";
comProduct.Focus();
flagProduct = true;
}
break;
case "comProduct":
if (flagProduct)
{
flagProduct = false;
txtProduct.Text = comProduct.SelectedValue.ToString();
comSize.Focus();
}
break;
case "comSize":
comColor.Focus();
break;
case "comColor":
comSort.Focus();
break;
case "comSort":
break;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
dbconnection.Close();
}
public void UsingParametersTwice()
{
if (Version < new Version(4, 1)) return;
execSQL(@"CREATE TABLE IF NOT EXISTS Test (input TEXT NOT NULL,
UNIQUE (input(100)), state INT NOT NULL, score INT NOT NULL)");
MySqlCommand cmd = new MySqlCommand(@"Insert into Test (input,
state, score) VALUES (?input, ?st, ?sc) ON DUPLICATE KEY
UPDATE state=state|?st;", conn);
cmd.Parameters.Add(new MySqlParameter("?input", ""));
cmd.Parameters.Add(new MySqlParameter("?st", Convert.ToInt32(0)));
cmd.Parameters.Add(new MySqlParameter("?sc", Convert.ToInt32(0)));
cmd.Prepare();
cmd.Parameters["?input"].Value = "test";
cmd.Parameters["?st"].Value = 1;
cmd.Parameters["?sc"].Value = 42;
int result = cmd.ExecuteNonQuery();
Assert.AreEqual(1, result);
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual("test", dt.Rows[0]["input"]);
Assert.AreEqual(1, dt.Rows[0]["state"]);
Assert.AreEqual(42, dt.Rows[0]["score"]);
}
private void btn_login_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txt_username.Text))
{
MessageBox.Show("Please enter your username.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
txt_username.Focus();
return;
}
else if (string.IsNullOrEmpty(txt_password.Text))
{
MessageBox.Show("Please enter your password.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
txt_password.Focus();
return;
}
else if (string.IsNullOrEmpty(cb_type.Text))
{
MessageBox.Show("Please select type, you're gonna login as a Project Manager or C.E.O.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
cb_type.Focus();
return;
}
try
{
bool r = validate_login();
if (r)
{
MySqlConnection conn = new MySqlConnection("Server=localhost;Database=salwa_ppcs;Uid=root;Pwd=;");
MySqlDataAdapter sda = new MySqlDataAdapter("Select count(*) from users where username='******' and password='******' and type='" + cb_type.Text + "'", conn);
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt.Rows[0][0].ToString() == "1")
{
MySqlDataAdapter sda2 = new MySqlDataAdapter("Select * from users where username='******' and password='******' and type='" + cb_type.Text + "'", conn);
DataTable dt1 = new DataTable();
sda2.Fill(dt1);
if (dt1.Rows[0][0].ToString() == "1")
{
Project p = new Project(cb_type.Text = "PresidentDirector");
this.Hide();
p.Show();
}
else if (dt1.Rows[0][0].ToString() == "2")
{
Project p = new Project(cb_type.Text = "Project Manager");
this.Hide();
p.Show();
}
}
}
else
{
MessageBox.Show("Username and/or password are incorrect!", "Message", MessageBoxButtons.RetryCancel, MessageBoxIcon.Error);
txt_username.Focus();
return;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void InsertingUnsignedTinyInt()
{
execSQL("DROP TABLE IF EXISTS Test");
execSQL(@"CREATE TABLE Test(id TINYINT UNSIGNED NOT NULL,
id2 INT UNSIGNED, id3 TINYINT UNSIGNED, id4 INT UNSIGNED NOT NULL)");
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?id, ?id2, ?id3, ?id4)", conn);
cmd.Parameters.Add("?id", MySqlDbType.UByte);
cmd.Parameters.Add("?id2", MySqlDbType.UByte);
cmd.Parameters.Add("?id3", MySqlDbType.UByte);
cmd.Parameters.Add("?id4", MySqlDbType.UByte);
cmd.Prepare();
cmd.Parameters[0].Value = 127;
cmd.Parameters[1].Value = 1;
cmd.Parameters[2].Value = 2;
cmd.Parameters[3].Value = 3;
cmd.ExecuteNonQuery();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(127, dt.Rows[0][0]);
Assert.AreEqual(1, dt.Rows[0][1]);
Assert.AreEqual(2, dt.Rows[0][2]);
Assert.AreEqual(3, dt.Rows[0][3]);
}
private void btnSearch_Click(object sender, EventArgs e)
{
try
{
if (comFromStore.Text != "" && comType.Text != "" && comFactory.Text != "")
{
string q1, q2, q3, q4, fQuery = "";
if (comType.Text == "")
{
q1 = "select Type_ID from type";
}
else
{
q1 = comType.SelectedValue.ToString();
}
if (comFactory.Text == "")
{
q2 = "select Factory_ID from factory";
}
else
{
q2 = comFactory.SelectedValue.ToString();
}
if (comProduct.Text == "")
{
q3 = "select Product_ID from product";
}
else
{
q3 = comProduct.SelectedValue.ToString();
}
if (comGroup.Text == "")
{
q4 = "select Group_ID from groupo";
}
else
{
q4 = comGroup.SelectedValue.ToString();
}
if (comSize.Text != "")
{
fQuery += " and size.Size_ID=" + comSize.SelectedValue.ToString();
}
if (comColor.Text != "")
{
fQuery += " and color.Color_ID=" + comColor.SelectedValue.ToString();
}
if (comSort.Text != "")
{
fQuery += " and Sort.Sort_ID=" + comSort.SelectedValue.ToString();
}
dbconnection.Open();
string query = "select data.Data_ID,data.Code as 'الكود',type.Type_Name as 'النوع',concat(product.Product_Name,' - ',type.Type_Name,' - ',factory.Factory_Name,' - ',groupo.Group_Name,' ',COALESCE(color.Color_Name,''),' ',COALESCE(size.Size_Value,''),' ',COALESCE(sort.Sort_Value,'')) as 'الاسم',data.Carton as 'الكرتنة',sum(storage.Total_Meters) as 'الكمية' FROM data LEFT JOIN color ON color.Color_ID = data.Color_ID LEFT JOIN size ON size.Size_ID = data.Size_ID LEFT JOIN sort ON sort.Sort_ID = data.Sort_ID INNER JOIN groupo ON data.Group_ID = groupo.Group_ID INNER JOIN factory ON factory.Factory_ID = data.Factory_ID INNER JOIN product ON product.Product_ID = data.Product_ID INNER JOIN type ON type.Type_ID = data.Type_ID INNER JOIN sellprice ON sellprice.Data_ID = data.Data_ID LEFT JOIN storage ON storage.Data_ID = data.Data_ID where data.Type_ID IN(" + q1 + ") and data.Factory_ID IN(" + q2 + ") and data.Group_ID IN (" + q4 + ") and data.Data_ID=0 group by data.Data_ID";
MySqlDataAdapter da = new MySqlDataAdapter(query, dbconnection);
DataTable dt = new DataTable();
da.Fill(dt);
gridControl1.DataSource = dt;
if (gridView2.RowCount == 0)
{
query = "select data.Data_ID,data.Code as 'الكود',type.Type_Name as 'النوع',concat(product.Product_Name,' - ',type.Type_Name,' - ',factory.Factory_Name,' - ',groupo.Group_Name,' ',COALESCE(color.Color_Name,''),' ',COALESCE(size.Size_Value,''),' ',COALESCE(sort.Sort_Value,'')) as 'الاسم',data.Carton as 'الكرتنة',sum(storage.Total_Meters) as 'الكمية' FROM data LEFT JOIN color ON color.Color_ID = data.Color_ID LEFT JOIN size ON size.Size_ID = data.Size_ID LEFT JOIN sort ON sort.Sort_ID = data.Sort_ID INNER JOIN groupo ON data.Group_ID = groupo.Group_ID INNER JOIN factory ON factory.Factory_ID = data.Factory_ID INNER JOIN product ON product.Product_ID = data.Product_ID INNER JOIN type ON type.Type_ID = data.Type_ID INNER JOIN sellprice ON sellprice.Data_ID = data.Data_ID LEFT JOIN storage ON storage.Data_ID = data.Data_ID where data.Type_ID IN(" + q1 + ") and data.Factory_ID IN(" + q2 + ") and data.Group_ID IN (" + q4 + ") and data.Data_ID=0 group by data.Data_ID";
da = new MySqlDataAdapter(query, dbconnection);
dt = new DataTable();
da.Fill(dt);
gridControl2.DataSource = dt;
gridView2.Columns[0].Visible = false;
gridView2.Columns["الاسم"].Width = 300;
}
query = "SELECT data.Data_ID,data.Code as 'الكود',type.Type_Name as 'النوع',concat(product.Product_Name,' ',COALESCE(color.Color_Name,''),' ',data.Description,' ',groupo.Group_Name,' ',factory.Factory_Name,' ',COALESCE(size.Size_Value,''),' ',COALESCE(sort.Sort_Value,'')) as 'الاسم',data.Carton as 'الكرتنة',sum(storage.Total_Meters) as 'الكمية' FROM data LEFT JOIN color ON color.Color_ID = data.Color_ID LEFT JOIN size ON size.Size_ID = data.Size_ID LEFT JOIN sort ON sort.Sort_ID = data.Sort_ID INNER JOIN groupo ON data.Group_ID = groupo.Group_ID INNER JOIN factory ON factory.Factory_ID = data.Factory_ID INNER JOIN product ON product.Product_ID = data.Product_ID INNER JOIN type ON type.Type_ID = data.Type_ID inner JOIN storage ON storage.Data_ID = data.Data_ID where data.Type_ID IN(" + q1 + ") and data.Factory_ID IN(" + q2 + ") and data.Product_ID IN (" + q3 + ") and data.Group_ID IN (" + q4 + ") " + fQuery + " and storage.Store_ID=" + comFromStore.SelectedValue.ToString() + " group by data.Data_ID order by SUBSTR(data.Code,1,16),color.Color_Name,data.Description,data.Sort_ID";
MySqlCommand comand = new MySqlCommand(query, dbconnection);
MySqlDataReader dr = comand.ExecuteReader();
while (dr.Read())
{
gridView1.AddNewRow();
int rowHandle = gridView1.GetRowHandle(gridView1.DataRowCount);
if (gridView1.IsNewItemRow(rowHandle))
{
gridView1.SetRowCellValue(rowHandle, gridView1.Columns[0], dr["Data_ID"]);
gridView1.SetRowCellValue(rowHandle, gridView1.Columns["الكود"], dr["الكود"]);
gridView1.SetRowCellValue(rowHandle, gridView1.Columns["النوع"], dr["النوع"]);
gridView1.SetRowCellValue(rowHandle, gridView1.Columns["الاسم"], dr["الاسم"]);
gridView1.SetRowCellValue(rowHandle, gridView1.Columns["الكرتنة"], dr["الكرتنة"]);
gridView1.SetRowCellValue(rowHandle, gridView1.Columns["الكمية"], dr["الكمية"]);
}
}
dr.Close();
gridView1.Columns[0].Visible = false;
gridView1.Columns["الاسم"].Width = 300;
if (gridView1.IsLastVisibleRow)
{
gridView1.FocusedRowHandle = gridView1.RowCount - 1;
}
}
else
{
MessageBox.Show("يجب اختيار المخزن واختيار النوع والمصنع على الاقل");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
dbconnection.Close();
}
public void UTF8AsColumnNames()
{
string connStr = GetConnectionString(true) + ";charset=utf8;pooling=false";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlDataAdapter da = new MySqlDataAdapter("select now() as 'Numéro'", c);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual("Numéro", dt.Columns[0].ColumnName);
MySqlCommand cmd = new MySqlCommand("SELECT NOW() AS 'Numéro'", c);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
int ord = reader.GetOrdinal("Numéro");
Assert.AreEqual(0, ord);
}
}
}
public bool ValidarRegistroDuplicado(ProductoLoteEN oRegistroEN, DatosDeConexionEN oDatos, string TipoDeOperacion)
{
oTransaccionesAD = new TransaccionesAD();
try
{
Cnn = new MySqlConnection(TraerCadenaDeConexion(oDatos));
Cnn.Open();
Comando = new MySqlCommand();
Comando.Connection = Cnn;
Comando.CommandType = CommandType.Text;
switch (TipoDeOperacion.Trim().ToUpper())
{
case "AGREGAR":
Consultas = @"SELECT CASE WHEN EXISTS(Select idLoteDelProducto from productolote
Where idProducto = @idProducto and SoloFecha(FechaDeVencimiento) = SoloFecha(@FechaDeVencimiento)
and CantidadDelLote = @CantidadDelLote) THEN 1 ELSE 0 END AS 'RES'";
Comando.Parameters.Add(new MySqlParameter("@FechaDeVencimiento", MySqlDbType.DateTime)).Value = oRegistroEN.FechaDeVencimiento;
Comando.Parameters.Add(new MySqlParameter("@idProducto", MySqlDbType.Int32)).Value = oRegistroEN.oProductoEN.idProducto;
Comando.Parameters.Add(new MySqlParameter("@CantidadDelLote", MySqlDbType.Decimal)).Value = oRegistroEN.CantidadDelLote;
break;
case "ACTUALIZAR":
Consultas = @"SELECT CASE WHEN EXISTS(Select idLoteDelProducto from productolote
Where idProducto = @idProducto and SoloFecha(FechaDeVencimiento) = SoloFecha(@FechaDeVencimiento)
and CantidadDelLote = @CantidadDelLote and idLoteDelProducto <> @idLoteDelProducto) THEN 1 ELSE 0 END AS 'RES'";
Comando.Parameters.Add(new MySqlParameter("@FechaDeVencimiento", MySqlDbType.DateTime)).Value = oRegistroEN.FechaDeVencimiento;
Comando.Parameters.Add(new MySqlParameter("@idProducto", MySqlDbType.Int32)).Value = oRegistroEN.oProductoEN.idProducto;
Comando.Parameters.Add(new MySqlParameter("@CantidadDelLote", MySqlDbType.Decimal)).Value = oRegistroEN.CantidadDelLote;
Comando.Parameters.Add(new MySqlParameter("@idLoteDelProducto", MySqlDbType.Int32)).Value = oRegistroEN.idLoteDelProducto;
break;
default:
throw new ArgumentException("La aperación solicitada no esta disponible");
}
Comando.CommandText = Consultas;
Adaptador = new MySqlDataAdapter();
DT = new DataTable();
Adaptador.SelectCommand = Comando;
Adaptador.Fill(DT);
if (Convert.ToInt32(DT.Rows[0]["RES"].ToString()) > 0)
{
DescripcionDeOperacion = string.Format("Ya existe información del Registro dentro de nuestro sistema: {0} {1}", Environment.NewLine, InformacionDelRegistro(oRegistroEN));
this.Error = DescripcionDeOperacion;
return(true);
}
return(false);
}
catch (Exception ex)
{
this.Error = ex.Message;
DescripcionDeOperacion = string.Format("Se produjo el seguiente error: '{2}' al validar el registro. {0} {1} ", Environment.NewLine, InformacionDelRegistro(oRegistroEN), ex.Message);
//Agregamos la Transacción....
TransaccionesEN oTran = InformacionDelaTransaccion(oRegistroEN, "VALIDAR", "REGISTRO DUPLICADO DENTRO DE LA BASE DE DATOS", "ERROR");
oTransaccionesAD.Agregar(oTran, oDatos);
return(false);
}
finally
{
if (Cnn != null)
{
if (Cnn.State == ConnectionState.Open)
{
Cnn.Close();
}
}
Cnn = null;
Comando = null;
Adaptador = null;
oTransaccionesAD = null;
}
}
/// <summary>
/// Get Store Attachment Settings
/// </summary>
/// <param name="TenantID"></param>
/// <param name="CreatedBy"></param>
/// <returns></returns>
public AttachmentSettingResponseModel GetStoreAttachmentSettings(int TenantId, int CreatedBy)
{
AttachmentSettingResponseModel obj = new AttachmentSettingResponseModel();
DataSet ds = new DataSet();
try
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("SP_GetStoreAttachmentSettings", conn)
{
CommandType = CommandType.StoredProcedure
};
cmd.Parameters.AddWithValue("@_TenantId", TenantId);
MySqlDataAdapter da = new MySqlDataAdapter
{
SelectCommand = cmd
};
da.Fill(ds);
List <StoreAttachmentFileFormat> storeattachmentfileformat = new List <StoreAttachmentFileFormat>();
List <ArrachementSize> arrachementsize = new List <ArrachementSize>();
List <AttachmentSettings> attachmentsettings = new List <AttachmentSettings>();
if (ds != null && ds.Tables != null)
{
if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
{
storeattachmentfileformat = ds.Tables[0].AsEnumerable().Select(r => new StoreAttachmentFileFormat()
{
FormatID = Convert.ToInt32(r.Field <object>("FormatID")),
FileFormaName = r.Field <object>("FileFormaName") == DBNull.Value ? string.Empty : Convert.ToString(r.Field <object>("FileFormaName")),
}).ToList();
}
if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0)
{
arrachementsize = ds.Tables[1].AsEnumerable().Select(r => new ArrachementSize()
{
Numb = Convert.ToInt32(r.Field <object>("numb")),
NumbMB = r.Field <object>("numbMB") == DBNull.Value ? string.Empty : Convert.ToString(r.Field <object>("numbMB")),
}).ToList();
}
if (ds.Tables[2] != null && ds.Tables[2].Rows.Count > 0)
{
attachmentsettings = ds.Tables[2].AsEnumerable().Select(r => new AttachmentSettings()
{
SettingID = Convert.ToInt32(r.Field <object>("SettingID")),
AttachmentSize = Convert.ToInt32(r.Field <object>("AttachmentSize")),
FileFomatID = Convert.ToInt32(r.Field <object>("FileFomatID")),
CreatedBy = Convert.ToInt32(r.Field <object>("CreatedBy")),
}).ToList();
}
}
obj.StoreAttachmentFileFormatList = storeattachmentfileformat;
obj.ArrachementSizeList = arrachementsize;
obj.AttachmentSettingsList = attachmentsettings;
}
catch (Exception)
{
throw;
}
finally
{
if (ds != null)
{
ds.Dispose();
}
conn.Close();
}
return(obj);
}
private void rezultategrid_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
id_cod = Convert.ToInt32(rezultategrid.Rows[e.RowIndex].Cells["cod"].Value.ToString());
MySqlConnection con = new MySqlConnection(conn);
MySqlCommand cmd = new MySqlCommand();
con.Open();
cmd.Connection = con;
cmd.Parameters.Clear();
cmd.CommandText = "select * from inscrieri where codinscriere=@cod";
cmd.Parameters.AddWithValue("@cod", id_cod);
cmd.ExecuteNonQuery();
DataTable dt = new DataTable();
DataTable dt1 = new DataTable();
MySqlDataAdapter da1 = new MySqlDataAdapter(cmd);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
da1.Fill(ds);
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
student.Text = dr["nume"].ToString() + " " + dr["prenume"].ToString();
telefon.Text = dr["telefon"].ToString();
email.Text = dr["email"].ToString();
label1.Text = dr["nume"].ToString();
label2.Text = dr["prenume"].ToString();
}
MySqlCommand cmd2 = new MySqlCommand();
cmd2.Connection = con;
cmd2.Parameters.Clear();
cmd2.CommandText = "select denumire , coduniversitate from universitati";
cmd2.ExecuteNonQuery();
DataTable dtt = new DataTable();
DataTable dtt1 = new DataTable();
MySqlDataAdapter daa1 = new MySqlDataAdapter(cmd2);
MySqlDataAdapter daa = new MySqlDataAdapter(cmd2);
DataSet dss = new DataSet();
daa1.Fill(dss);
daa.Fill(dtt);
foreach (DataRow dr in dtt.Rows)
{
LstUniv.Add(Convert.ToInt32(dr["coduniversitate"]));
dropuniv.AddItem(dr["denumire"].ToString());
}
panel.Visible = true;
panel.BringToFront();
}
