public string SummaryTableCsv(string region, string city, string center,
string code, int?actionType, int failRate0, int failRate1, int clientCount0, int clientCount1)
{
LoadData();
var items = new List <dynamic>();
using (var connection = new NpgsqlConnection(_connectionString)) {
var whereItems = new List <string>();
if (!string.IsNullOrEmpty(region))
{
whereItems.Add("ct.provincia = '" + region + "'");
}
if (!string.IsNullOrEmpty(city))
{
whereItems.Add("ct.municipio = '" + city + "'");
}
if (!string.IsNullOrEmpty(center))
{
whereItems.Add("ct.centroresponsable = '" + center + "'");
}
if (!string.IsNullOrEmpty(code))
{
whereItems.Add("ct.code = '" + code + "'");
}
if (actionType.HasValue && actionType.Value > 0)
{
whereItems.Add("sum.action_required = '" + (actionType.Value == 1 ? "Regulatory Check" : actionType.Value == 2 ? "Maintenance Required" : "No action Required") + "'");
}
if (failRate0 > 0)
{
whereItems.Add("sum.failure_probability >= " + failRate0 / 100.0);
}
if (failRate1 < 100)
{
whereItems.Add("sum.failure_probability <= " + failRate1 / 100.0);
}
whereItems.Add("sum.num_clients >= " + clientCount0);
whereItems.Add("sum.num_clients <= " + clientCount1);
connection.Open();
var text =
@"select ct.code,
sum.num_clients, sum.type_construct, sum.num_transf, sum.num_exits, sum.box_age, sum.cell_functional_type,
sum.max_ima, sum.min_ima_s, sum.transf_age,
sum.transf_fail_rate, sum.box_fail_rate, sum.exist_fail_rate, sum.constr_fail_rate,
sum.last_36m_failure, sum.last_48m_failure, sum.failure_probability, sum.action_required
from mp_gs_cts_gps_coordinates ct
join mp_gs_summry sum on sum.code = ct.code
where ";
var command = new NpgsqlCommand(text + string.Join(" and ", whereItems) + " order by sum.failure_probability desc", connection);
var reader = command.ExecuteReader();
while (reader.Read())
{
var ctCode = reader["code"] as string;
var ct = _cts.FirstOrDefault(o => o.Code == ctCode);
if (ct != null)
{
var sum = new MpgsCtsSummary {
Code = ctCode,
ClientCount = (int)(double)reader["num_clients"],
ConstructType = reader["type_construct"] as string,
TransfCount = (int)(double)reader["num_transf"],
ExitCount = (int)(double)reader["num_exits"],
BoxAge = (double)reader["box_age"],
CellFunctional = (int)(double)reader["cell_functional_type"],
MaxIma = (double)reader["max_ima"],
MinIma = (double)reader["min_ima_s"],
TransfAge = Math.Round((double)reader["transf_age"], 1),
TransfFailRate = Math.Round((double)reader["transf_fail_rate"] * 100, 1),
BoxFailRate = Math.Round((double)reader["box_fail_rate"] * 100, 1),
ExitFailRate = Math.Round((double)reader["exist_fail_rate"] * 100, 1),
ConstrFailRate = Math.Round((double)reader["constr_fail_rate"] * 100, 1),
FailRate36 = Math.Round((double)reader["last_36m_failure"] * 100, 1),
FailRate48 = Math.Round((double)reader["last_48m_failure"] * 100, 1),
FailRate = Math.Round((double)reader["failure_probability"] * 100, 1),
Action = reader["action_required"] as string
};
sum.ActionType = sum.Action == "Regulatory Check" ? 1 : sum.Action == "Maintenance Required" ? 2 : 3;
ct.ClientCount = sum.ClientCount;
ct.FailRate = sum.FailRate;
ct.ActionType = sum.ActionType;
items.Add(new { ct, sum });
}
}
reader.Close();
}
var builder = new StringBuilder();
builder.AppendLine("Code,# clients,Type Constr,# transf,# exits,Box Age,Tp function,Max. I,Min. I,Transf. Age,Transf. F. Rate,Exits F. Rate,Box F. Rate,Constr F. Rate,Last 36m failure,Last 48m failure,Failure Prob,Action required");
foreach (var item in items)
{
builder.Append(item.ct.Code + ",");
builder.Append(item.ct.ClientCount + ",");
builder.Append(item.sum.ConstructType + ",");
builder.Append(item.sum.TransfCount + ",");
builder.Append(item.sum.ExitCount + ",");
builder.Append(item.sum.BoxAge + ",");
builder.Append(item.sum.CellFunctional + ",");
builder.Append(item.sum.MaxIma + ",");
builder.Append(item.sum.MinIma + ",");
builder.Append(item.sum.TransfAge + ",");
builder.Append(item.sum.TransfFailRate + ",");
builder.Append(item.sum.ExitFailRate + ",");
builder.Append(item.sum.BoxFailRate + ",");
builder.Append(item.sum.ConstrFailRate + ",");
builder.Append(item.sum.FailRate36 + ",");
builder.Append(item.sum.FailRate48 + ",");
builder.Append(item.sum.FailRate + ",");
builder.AppendLine(item.sum.Action);
}
return(builder.ToString());
}
public dynamic SummaryTable(string region, string city, string center,
string code, int?actionType, int failRate0, int failRate1, int clientCount0, int clientCount1, int page)
{
LoadData();
var pageSize = 50;
var items = new List <dynamic>();
long count = 0;
using (var connection = new NpgsqlConnection(_connectionString)) {
var whereItems = new List <string>();
if (!string.IsNullOrEmpty(region))
{
whereItems.Add("ct.provincia = '" + region + "'");
}
if (!string.IsNullOrEmpty(city))
{
whereItems.Add("ct.municipio = '" + city + "'");
}
if (!string.IsNullOrEmpty(center))
{
whereItems.Add("ct.centroresponsable = '" + center + "'");
}
if (!string.IsNullOrEmpty(code))
{
whereItems.Add("ct.code = '" + code + "'");
}
if (actionType.HasValue && actionType.Value > 0)
{
whereItems.Add("sum.action_required = '" + (actionType.Value == 1 ? "Regulatory Check" : actionType.Value == 2 ? "Maintenance Required" : "No action Required") + "'");
}
if (failRate0 > 0)
{
whereItems.Add("sum.failure_probability >= " + failRate0 / 100.0);
}
if (failRate1 < 100)
{
whereItems.Add("sum.failure_probability <= " + failRate1 / 100.0);
}
whereItems.Add("sum.num_clients >= " + clientCount0);
whereItems.Add("sum.num_clients <= " + clientCount1);
connection.Open();
var countText =
@"select count(1) as count
from mp_gs_cts_gps_coordinates ct
join mp_gs_summry sum on sum.code = ct.code
where ";
var countCommand = new NpgsqlCommand(countText + string.Join(" and ", whereItems), connection);
var reader = countCommand.ExecuteReader();
while (reader.Read())
{
count = (long)reader["count"];
}
reader.Close();
var text =
@"select ct.code,
sum.num_clients, sum.type_construct, sum.num_transf, sum.num_exits, sum.box_age, sum.cell_functional_type,
sum.max_ima, sum.min_ima_s, sum.transf_age,
sum.transf_fail_rate, sum.box_fail_rate, sum.exist_fail_rate, sum.constr_fail_rate,
sum.last_36m_failure, sum.last_48m_failure, sum.failure_probability, sum.action_required
from mp_gs_cts_gps_coordinates ct
join mp_gs_summry sum on sum.code = ct.code
where ";
var command = new NpgsqlCommand(text + string.Join(" and ", whereItems) + " order by sum.failure_probability desc limit " + pageSize + " offset " + (pageSize * page), connection);
reader = command.ExecuteReader();
while (reader.Read())
{
var ctCode = reader["code"] as string;
var ct = _cts.FirstOrDefault(o => o.Code == ctCode);
if (ct != null)
{
var sum = new MpgsCtsSummary {
Code = ctCode,
ClientCount = (int)(double)reader["num_clients"],
ConstructType = reader["type_construct"] as string,
TransfCount = (int)(double)reader["num_transf"],
ExitCount = (int)(double)reader["num_exits"],
BoxAge = (double)reader["box_age"],
CellFunctional = (int)(double)reader["cell_functional_type"],
MaxIma = (double)reader["max_ima"],
MinIma = (double)reader["min_ima_s"],
TransfAge = Math.Round((double)reader["transf_age"], 1),
TransfFailRate = Math.Round((double)reader["transf_fail_rate"] * 100, 1),
BoxFailRate = Math.Round((double)reader["box_fail_rate"] * 100, 1),
ExitFailRate = Math.Round((double)reader["exist_fail_rate"] * 100, 1),
ConstrFailRate = Math.Round((double)reader["constr_fail_rate"] * 100, 1),
FailRate36 = Math.Round((double)reader["last_36m_failure"] * 100, 1),
FailRate48 = Math.Round((double)reader["last_48m_failure"] * 100, 1),
FailRate = Math.Round((double)reader["failure_probability"] * 100, 1),
Action = reader["action_required"] as string
};
sum.ActionType = sum.Action == "Regulatory Check" ? 1 : sum.Action == "Maintenance Required" ? 2 : 3;
ct.ClientCount = sum.ClientCount;
ct.FailRate = sum.FailRate;
ct.ActionType = sum.ActionType;
items.Add(new { ct, sum });
}
}
reader.Close();
}
return(new { count, items, pageCount = Math.Ceiling(count / (double)pageSize) });
}
