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;
}