public LocationStatusList getLocationsHeatmapSags(string targetDateFrom, string targetDateTo, string userName)
{
SqlConnection conn = null;
SqlDataReader rdr = null;
LocationStatusList locationStates = new LocationStatusList { };
try
{
conn = new SqlConnection(connectionstring);
conn.Open();
SqlCommand cmd = new SqlCommand("dbo.selectMeterLocationsMinimumSags", conn);
cmd.Parameters.Add(new SqlParameter("@EventDateFrom", targetDateFrom));
cmd.Parameters.Add(new SqlParameter("@EventDateTo", targetDateTo));
cmd.Parameters.Add(new SqlParameter("@username", userName));
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 300;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
locationStatus ourStatus = new locationStatus();
ourStatus.location = new siteGeocoordinates();
ourStatus.location.latitude = ourStatus.Latitude = (double)rdr["Latitude"];
ourStatus.location.longitude = ourStatus.Longitude = (double)rdr["Longitude"];
ourStatus.name = (String)rdr["name"];
ourStatus.status = (int)rdr["Event_Count"];
ourStatus.id = (int)rdr["id"];
locationStates.Locations.Add(ourStatus);
}
}
finally
{
if (conn != null)
{
conn.Close();
}
if (rdr != null)
{
rdr.Close();
}
}
return (locationStates);
}
public LocationStatusList getLocationsFaults(string targetDateFrom, string targetDateTo, string userName )
{
SqlConnection conn = null;
SqlDataReader rdr = null;
LocationStatusList locationStates = new LocationStatusList();
locationStates.ColorRange = new double[] { 4278190335, 4294905584, 0, 4294912000, 0, 4294940160, 0, 4294967040, 0, 4278241294, 4278255604 };
locationStates.ColorDomain = new double[] { 0, 0, 49, 50, 69, 70, 89, 90, 97, 98, 100 };
try
{
conn = new SqlConnection(connectionstring);
conn.Open();
SqlCommand cmd = new SqlCommand("dbo.selectMeterLocationsFaults", conn);
cmd.Parameters.Add(new SqlParameter("@EventDateFrom", targetDateFrom));
cmd.Parameters.Add(new SqlParameter("@EventDateTo", targetDateTo));
cmd.Parameters.Add(new SqlParameter("@username", userName));
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 300;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
locationStatus ourStatus = new locationStatus();
ourStatus.location = new siteGeocoordinates();
ourStatus.location.latitude = ourStatus.Latitude = (double)rdr["Latitude"];
ourStatus.location.longitude = ourStatus.Longitude = (double)rdr["Longitude"];
ourStatus.name = (String)rdr["name"];
ourStatus.status = (int)rdr["Event_Count"];
ourStatus.id = (int)rdr["id"];
ourStatus.data.Add(ourStatus.status);
locationStates.Locations.Add(ourStatus);
}
}
finally
{
if (conn != null)
{
conn.Close();
}
if (rdr != null)
{
rdr.Close();
}
}
return (locationStates);
}
public LocationStatusList getLocationsEventsHeatmapCounts(string targetDateFrom, string targetDateTo, string userName, string severityFilter)
{
SqlConnection conn = null;
SqlDataReader rdr = null;
LocationStatusList locationStates = new LocationStatusList();
locationStates.ColorDomain = new double[] {};
locationStates.ColorRange = new double[] {};
try
{
conn = new SqlConnection(connectionstring);
conn.Open();
SqlCommand cmd = new SqlCommand("dbo.selectMeterLocationsEvents", conn);
//SqlCommand cmd = new SqlCommand("dbo.selectMeterLocationsMinimumSags", conn);
cmd.Parameters.Add(new SqlParameter("@EventDateFrom", targetDateFrom));
cmd.Parameters.Add(new SqlParameter("@EventDateTo", targetDateTo));
cmd.Parameters.Add(new SqlParameter("@username", userName));
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 300;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
locationStatus ourStatus = new locationStatus();
ourStatus.location = new siteGeocoordinates();
ourStatus.location.latitude = ourStatus.Latitude = (double)rdr["Latitude"];
ourStatus.location.longitude = ourStatus.Longitude = (double)rdr["Longitude"];
ourStatus.name = (String)rdr["name"];
IDictionary<string, int> dict = new Dictionary<string, int>();
dict["Interruption"] = 1;
dict["Fault"] = 1;
dict["Sag"] = 1;
dict["Transient"] = 1;
dict["Swell"] = 1;
dict["Other"] = 1;
if (severityFilter == "undefined")
severityFilter = "Interruption,Fault,Sag,Transient,Swell,Other";
string[] codes = severityFilter.Split(',');
int sum = 0;
foreach (string s in codes)
{
if (s != "")
{
sum += (int)rdr[s] * dict[s];
ourStatus.data.Add((int)rdr[s]);
}
}
ourStatus.status = sum;
ourStatus.id = (int)rdr["id"];
locationStates.Locations.Add(ourStatus);
}
}
finally
{
if (conn != null)
{
conn.Close();
}
if (rdr != null)
{
rdr.Close();
}
}
return (locationStates);
}
public LocationStatusList getLocationsDisturbancesHeatmapCounts(string targetDateFrom, string targetDateTo, string userName, string severityFilter )
{
SqlConnection conn = null;
SqlDataReader rdr = null;
LocationStatusList locationStates = new LocationStatusList();
try
{
conn = new SqlConnection(connectionstring);
conn.Open();
SqlCommand cmd = new SqlCommand("dbo.selectMeterLocationsDisturbances", conn);
//SqlCommand cmd = new SqlCommand("dbo.selectMeterLocationsMinimumSags", conn);
cmd.Parameters.Add(new SqlParameter("@EventDateFrom", targetDateFrom));
cmd.Parameters.Add(new SqlParameter("@EventDateTo", targetDateTo));
cmd.Parameters.Add(new SqlParameter("@username", userName));
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 300;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
locationStatus ourStatus = new locationStatus();
ourStatus.location = new siteGeocoordinates();
ourStatus.location.latitude = ourStatus.Latitude = (double)rdr["Latitude"];
ourStatus.location.longitude = ourStatus.Longitude = (double)rdr["Longitude"];
ourStatus.name = (String)rdr["name"];
if (severityFilter == "undefined")
severityFilter = "5,4,3,2,1,0";
string[] codes = severityFilter.Split(',');
int sum = 0;
foreach (string s in codes)
{
if(s != "")
sum += (int)rdr[s] * (int.Parse(s) + 1);
}
ourStatus.status = sum;
ourStatus.id = (int)rdr["id"];
locationStates.Locations.Add(ourStatus);
}
}
finally
{
if (conn != null)
{
conn.Close();
}
if (rdr != null)
{
rdr.Close();
}
}
return (locationStates);
}
public LocationStatusList getHeatmapLocationsTrending(string targetDateFrom, string meterIDs, string userName)
{
SqlConnection conn = null;
SqlDataReader rdr = null;
LocationStatusList locationStates = new LocationStatusList();
Random rand = new Random();
try
{
conn = new SqlConnection(connectionstring);
conn.Open();
SqlCommand cmd = new SqlCommand("dbo.selectHeatmapMeterLocationsTrending", conn);
cmd.Parameters.Add(new SqlParameter("@EventDate", targetDateFrom));
cmd.Parameters.Add(new SqlParameter("@MeterID", meterIDs));
cmd.Parameters.Add(new SqlParameter("@username", userName));
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 300;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
locationStatus ourStatus = new locationStatus();
ourStatus.location = new siteGeocoordinates();
ourStatus.location.latitude = ourStatus.Latitude = (double)rdr["Latitude"];
ourStatus.location.longitude = ourStatus.Longitude = (double)rdr["Longitude"];
ourStatus.id = (int)rdr["MeterID"];
ourStatus.status = (int)rdr["Value"];
ourStatus.datetime = (String)((DateTime)rdr["thedate"]).ToString("MM/dd/yy HH:mm:ss");
locationStates.Locations.Add(ourStatus);
}
}
finally
{
if (conn != null)
{
conn.Close();
}
if (rdr != null)
{
rdr.Close();
}
}
return (locationStates);
}
public LocationStatusList getLocationsTrending(string targetDateFrom, string targetDateTo, string userName)
{
SqlConnection conn = null;
SqlDataReader rdr = null;
LocationStatusList locationStates = new LocationStatusList { };
try
{
conn = new SqlConnection(connectionstring);
conn.Open();
SqlCommand cmd = new SqlCommand("dbo.selectMeterLocationsTrending", conn);
cmd.Parameters.Add(new SqlParameter("@EventDateFrom", targetDateFrom));
cmd.Parameters.Add(new SqlParameter("@EventDateTo", targetDateTo));
cmd.Parameters.Add(new SqlParameter("@username", userName));
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 300;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
locationStatus ourStatus = new locationStatus();
ourStatus.location = new siteGeocoordinates();
ourStatus.location.latitude = ourStatus.Latitude = (double)rdr["Latitude"];
ourStatus.location.longitude = ourStatus.Longitude = (double)rdr["Longitude"];
ourStatus.name = (String)rdr["name"];
ourStatus.id = (int)rdr["id"];
string severityFilter = "Alarm,Offnormal";
string[] codes = severityFilter.Split(',');
int sum = 0;
foreach (string s in codes)
{
if (s != "")
{
sum += (int)rdr[s];
ourStatus.data.Add((int)rdr[s]);
}
}
ourStatus.status = sum;
locationStates.Locations.Add(ourStatus);
}
}
finally
{
if (conn != null)
{
conn.Close();
}
if (rdr != null)
{
rdr.Close();
}
}
return (locationStates);
}