public bool checkLogin(string userName, string password)
{
bool status = false;
string query = "SELECT * FROM login where user_name= '" + userName + "' and user_password = '******'";
if (cn.open_connection() == true)
{
MySqlCommand cmd = new MySqlCommand(query, cn.connection);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
status = true;
}
reader.Close();
cn.close_connection();
}
return(status);
}
public void SaveData()
{
string query1 = "INSERT into login (user_name, user_password, user_type) ";
query1 += "VALUES (@user_name, @user_password, @user_type);";
//open connection
if (cn.open_connection() == true)
{
//create command and assign the query and connection from the constructor
MySqlCommand cmd1 = new MySqlCommand(query1, cn.connection);
cmd1.Parameters.AddWithValue("@user_name", userName);
cmd1.Parameters.AddWithValue("@user_password", password);
cmd1.Parameters.AddWithValue("@user_type", userType);
cmd1.ExecuteNonQuery();
cn.close_connection();
}
string query2 = "INSERT into user_info (user_name, first_name, last_name, institution_name, email) ";
query2 += "VALUES (@user_name, @first_name, @last_name, @institution_name, @email);";
//open connection
if (cn.open_connection() == true)
{
//create command and assign the query and connection from the constructor
MySqlCommand cmd2 = new MySqlCommand(query2, cn.connection);
cmd2.Parameters.AddWithValue("@user_name", userName);
cmd2.Parameters.AddWithValue("@first_name", firstName);
cmd2.Parameters.AddWithValue("@last_name", lastName);
cmd2.Parameters.AddWithValue("@institution_name", institution);
cmd2.Parameters.AddWithValue("@email", email);
cmd2.ExecuteNonQuery();
cn.close_connection();
}
}
public string createJobID(string user_name)
{
string job_id = "";
string query = "select max(id)+1 as id from data_master";
if (cn.open_connection() == true)
{
MySqlCommand cmd = new MySqlCommand(query, cn.connection);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
job_id = user_name + Convert.ToString(reader["id"]);
}
reader.Close();
cn.close_connection();
}
return(job_id);
}
public void setNodeAndFlowValues(HtmlTextArea nodeData, HtmlTextArea flowData, string year, string country)
{
dbConnect cn = new dbConnect();
string query1 = "SELECT p.process_name, p.color, p.angle, p.width, p.height, p.xPos, p.yPos FROM process_list p ";
Random rnd = new Random();
string color_code = "";
string all_node_value = "";
if (cn.open_connection() == true)
{
MySqlCommand cmd = new MySqlCommand(query1, cn.connection);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string single_node_value = "";
for (int i = 0; i < reader.FieldCount; i++)
{
if (i == 0)
{
single_node_value += "[" + Convert.ToString(reader.GetValue(i)) + "]" + " ";
}
else
{
single_node_value += Convert.ToString(reader.GetValue(i)) + " ";
}
}
all_node_value += single_node_value + "\r\n";
}
reader.Close();
cn.close_connection();
}
nodeData.InnerText = all_node_value;
string filter = "";
string mselectedCountryISO = "";
string query2 = "select c.ISO_code as ISO_code from countries c where c.country_name = '" + country + "'";
if (cn.open_connection() == true)
{
MySqlCommand cmd = new MySqlCommand(query2, cn.connection);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
mselectedCountryISO = Convert.ToString(reader["ISO_code"]);
}
reader.Close();
cn.close_connection();
}
if (year == "" && mselectedCountryISO == "")
{
filter = " ";
}
else if (year == "" && mselectedCountryISO != "")
{
filter = "where (f.region_source in (" + mselectedCountryISO + ") or f.region_target in (" + mselectedCountryISO + "))";
}
else if (year != "" && mselectedCountryISO == "")
{
filter = "where f.year in(" + year + ") ";
}
else
{
filter = "where f.year in(" + year + ") and (f.region_source in (" + mselectedCountryISO + ") or f.region_target in (" + mselectedCountryISO + ")) ";
}
//http://localhost:49645/frmCircularSankey.aspx?D=1&year=2003&country=Germany
string query3 = "SELECT distinct f.process_id_source PIDS, (select p.process_name from process_list p where p.process_id = PIDS) as PNS, ";
query3 += "f.process_id_target PIDT, (select p.process_name from process_list p where p.process_id = PIDT) as PNT, f.region_source CICS, c.country_name CS, f.region_target CICT, ";
query3 += " c.country_name CT, f.year T, f.value V, f.unit UID FROM flows f inner join countries c on(f.region_source = c.ISO_code or f.region_target = c.ISO_code) ";
query3 += " " + filter + " ";
string all_flow_value = "";
if (cn.open_connection() == true)
{
MySqlCommand cmd = new MySqlCommand(query3, cn.connection);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string CultureName = Thread.CurrentThread.CurrentCulture.Name;
CultureInfo ci = new CultureInfo(CultureName);
if (ci.NumberFormat.NumberDecimalSeparator != ".")
{
// Forcing use of decimal separator for numerical values
ci.NumberFormat.NumberDecimalSeparator = ".";
Thread.CurrentThread.CurrentCulture = ci;
}
color_code = "[(" + rnd.Next(0, 255) + "," + rnd.Next(0, 255) + "," + rnd.Next(0, 255) + ")]";
all_flow_value += "[" + (string)reader["PNS"] + "]" + " " + "[" + (Double)reader["V"] + "]" + " " + "[(39,185,0)]" + " [ab] " + "[" + (string)reader["PNT"] + "]" + "\r\n";
}
reader.Close();
cn.close_connection();
}
flowData.InnerText = all_flow_value;
}