/// <summary>
/// GET: Clients
/// </summary>
/// <param name="sortOrder">Indicates the desired ordering of records</param>
/// <param name="currentFilter">Indicates the filter to apply</param>
/// <param name="searchString">Indicates the filter currently applied</param>
/// <param name="page">Number of page to return</param>
/// <returns></returns>
public ActionResult Index(string sortOrder,
string currentFilter,
string searchString,
int?page)
{
Clients item;
List <Clients> items = new List <Clients>();
//Send parameters to the view
//...sort
ViewData["CompanySort"] = (String.IsNullOrEmpty(sortOrder)) ? "company_desc" : "";
ViewData["FeAltaSort"] = (sortOrder == "fe_alta_asc") ? "fe_alta_desc" : "fe_alta_asc";
ViewData["SchemaSort"] = (sortOrder == "schema_asc") ? "schema_desc" : "schema_asc";
ViewData["CurrentSort"] = sortOrder;
//...filter
if (searchString != null)
{
page = 1; //Restore paging if you are doing a search
}
else
{
searchString = currentFilter;
}
ViewData["CurrentFilter"] = searchString;
if (searchString != null)
{
searchString = searchString.ToUpper();
}
//Set the filter for the query
string where = " ";
if (!String.IsNullOrEmpty(searchString))
{
where = $@" where upper(c.company_name) like '%{searchString}%'
or upper(c.phone) like '%{searchString}%'
or upper(c.db_schema) like '%{searchString}%'
or upper(c.db_name) like '%{searchString}%'
or upper(l.name) like '%{searchString}%'
or upper(p.name) like '%{searchString}%'
";
}
//Set the order for the query
string orderby = " ";
switch (sortOrder)
{
case "company_desc":
orderby = "order by company_name desc;";
break;
case "fe_alta_asc":
orderby = "order by add_date, company_name;";
break;
case "fe_alta_desc":
orderby = "order by add_date desc, company_name;";
break;
case "schema_asc":
orderby = "order by db_schema, company_name;";
break;
case "schema_desc":
orderby = "order by db_schema desc, company_name;";
break;
default: //company asc
orderby = "order by company_name;";
break;
}
//Load the list
sqlcmd = $@"select c.*, l.name as licence_type_name, p.name as package_name
from public.administration_client c
left join public.adminview_licence_types l on c.licence_type = l.id
left join public.administration_package p on c.package_id = p.id
{where} {orderby}";
using (var cnn = pgSQL.GetConnection())
using (var cmd = new NpgsqlCommand(sqlcmd, cnn))
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
item = new Clients();
item.Id = Convert.ToInt32(reader["Id"]);
item.CompanyName = (string)reader["company_name"];
item.Country = (string)reader["country"];
item.Language = (string)reader["language"];
item.AddDate = Convert.ToDateTime(reader["add_date"]).Date;
if (reader["due_date"] != DBNull.Value)
{
item.DueDate = Convert.ToDateTime(reader["due_date"]).Date;
}
if (reader["renew_date"] != DBNull.Value)
{
item.RenewalDate = Convert.ToDateTime(reader["renew_date"]).Date;
}
item.YearPayment = Convert.ToBoolean(reader["year_payment"]);
item.LicenceTypeName = (string)reader["licence_type_name"];
if (reader["package_id"] != DBNull.Value)
{
item.PackageID = Convert.ToInt32(reader["package_id"]);
item.PackageName = (string)reader["package_name"];
}
item.DbSchema = (string)reader["db_schema"];
item.DbName = (string)reader["db_name"];
item.ValidClient = Convert.ToBoolean(reader["valid_client"]);
items.Add(item);
item = null;
}
}
//Returns the sorted and paged selection according to the pageSize set
return(View(Paginacion <Clients> .Create(items, page ?? 1, pageSize)));
}