/// <summary>
/// Método privado para processamento do método 'user.resetpassword'
/// </summary>
/// <param name="sqlConnection">Conexão com o banco de dados MS-SQL</param>
/// <param name="parameters">Dicionário (String, Object) contendo todos os parâmetros necessários</param>
private List <Object> list(SqlConnection sqlConnection, Dictionary <String, Object> parameters)
{
List <Object> result = new List <Object>();
String text = "";
if (parameters.ContainsKey("text"))
{
text = (String)parameters["text"];
}
if (String.IsNullOrWhiteSpace(text))
{
text = "";
}
DbParameterCollection par = new DbParameterCollection();
par.Add("@enterprise_id", typeof(Int64)).Value = this._enterpriseId;
par.Add("@text", typeof(String), text.Length).Value = text;
Int32 page = 1;
Int32 pageSize = 10;
if (parameters.ContainsKey("page"))
{
Int32.TryParse(parameters["page"].ToString(), out page);
}
if (parameters.ContainsKey("page_size"))
{
Int32.TryParse(parameters["page_size"].ToString(), out pageSize);
}
if (pageSize < 1)
{
pageSize = 1;
}
if (page < 1)
{
page = 1;
}
Int32 rStart = ((page - 1) * pageSize) + 1;
Int32 rEnd = rStart + (pageSize - 1);
String sql = "";
sql += "WITH result_set AS (";
sql += " SELECT ";
sql += " ROW_NUMBER() OVER (ORDER BY f.name) AS [row_number], f.*, ";
sql += " ignore_qty = (select COUNT(distinct i1.filter_id) from resource_plugin_ignore_filter i1 with(nolock) where i1.filter_id = f.id), ";
sql += " lock_qty = (select COUNT(distinct l1.filter_id) from resource_plugin_lock_filter l1 with(nolock) where l1.filter_id = f.id),";
sql += " role_qty = (select COUNT(distinct r1.filter_id) from resource_plugin_role_filter r1 with(nolock) where r1.filter_id = f.id) ";
sql += " from filters f ";
sql += " where (f.enterprise_id = @enterprise_id " + (String.IsNullOrWhiteSpace(text) ? "" : " and f.name like '%'+@text+'%'") + ")";
sql += ") SELECT";
sql += " *";
sql += " FROM";
sql += " result_set";
sql += " WHERE";
sql += " [row_number] BETWEEN " + rStart + " AND " + rEnd;
DataTable dtPlugins = ExecuteDataTable(sqlConnection, sql, CommandType.Text, par, null);
if ((dtPlugins != null) && (dtPlugins.Rows.Count > 0))
{
foreach (DataRow dr1 in dtPlugins.Rows)
{
Dictionary <string, object> newItem = new Dictionary <string, object>();
newItem.Add("enterprise_id", dr1["enterprise_id"]);
newItem.Add("filter_id", dr1["id"]);
newItem.Add("name", dr1["name"]);
newItem.Add("ignore_qty", dr1["ignore_qty"]);
newItem.Add("lock_qty", dr1["lock_qty"]);
newItem.Add("role_qty", dr1["role_qty"]);
newItem.Add("create_date", (dr1["create_date"] != DBNull.Value ? (Int32)((((DateTime)dr1["create_date"]) - new DateTime(1970, 1, 1)).TotalSeconds) : 0));
//Lista as condições
List <Dictionary <String, Object> > conditions = new List <Dictionary <string, object> >();
FilterRule f = new FilterRule(dr1["name"].ToString());
DataTable dt2 = ExecuteDataTable(sqlConnection, "select f.*, f1.name field_name, f1.data_type from filters_conditions f with(nolock) inner join field f1 with(nolock) on f1.id = f.field_id where f.filter_id = " + dr1["id"] + " order by f.group_id, f1.name");
if ((dt2 != null) || (dt2.Rows.Count > 0))
{
foreach (DataRow dr2 in dt2.Rows)
{
Dictionary <string, object> c1 = new Dictionary <string, object>();
c1.Add("group_id", dr2["group_id"].ToString());
c1.Add("group_selector", dr2["group_selector"].ToString());
c1.Add("field_id", (Int64)dr2["field_id"]);
c1.Add("field_name", dr2["field_name"].ToString());
c1.Add("data_type", dr2["data_type"].ToString());
c1.Add("text", dr2["text"].ToString());
c1.Add("condition", dr2["condition"].ToString());
c1.Add("selector", dr2["selector"].ToString());
conditions.Add(c1);
f.AddCondition(dr2["group_id"].ToString(), dr2["group_selector"].ToString(), (Int64)dr2["field_id"], dr2["field_name"].ToString(), dr2["data_type"].ToString(), dr2["text"].ToString(), dr2["condition"].ToString(), dr2["selector"].ToString());
}
}
newItem.Add("conditions_description", f.ToString());
newItem.Add("conditions", conditions);
result.Add(newItem);
}
}
return(result);
}
/// <summary>
/// Método privado para processamento do método 'user.resetpassword'
/// </summary>
/// <param name="sqlConnection">Conexão com o banco de dados MS-SQL</param>
/// <param name="parameters">Dicionário (String, Object) contendo todos os parâmetros necessários</param>
private Dictionary <String, Object> change(SqlConnection sqlConnection, Dictionary <String, Object> parameters)
{
Dictionary <String, Object> result = new Dictionary <String, Object>();
if (!parameters.ContainsKey("filterid"))
{
Error(ErrorType.InvalidRequest, "Parameter filterid is not defined.", "", null);
return(null);
}
String filter = parameters["filterid"].ToString();
if (String.IsNullOrWhiteSpace(filter))
{
Error(ErrorType.InvalidRequest, "Parameter filterid is not defined.", "", null);
return(null);
}
Int64 filterid = 0;
try
{
filterid = Int64.Parse(filter);
}
catch
{
Error(ErrorType.InvalidRequest, "Parameter filterid is not a long integer.", "", null);
return(null);
}
DbParameterCollection par = new DbParameterCollection();
par.Add("@enterprise_id", typeof(Int64)).Value = this._enterpriseId;
par.Add("@filter_id", typeof(Int64)).Value = filterid;
DataTable dtFilter = ExecuteDataTable(sqlConnection, "select f.* from filters f with(nolock) where f.enterprise_id = @enterprise_id and f.id = @filter_id", CommandType.Text, par, null);
if (dtFilter == null)
{
Error(ErrorType.InternalError, "", "", null);
return(null);
}
if (dtFilter.Rows.Count == 0)
{
Error(ErrorType.InvalidRequest, "Filter not found.", "", null);
return(null);
}
List <String> log = new List <String>();
Boolean updateName = false;
Boolean updateConditions = false;
FilterRule filterData = getFilterData(sqlConnection, dtFilter.Rows[0]["name"].ToString(), parameters, log, out updateName, out updateConditions);
if (filterData == null)
{
return(null);
}
if (updateName || updateConditions)
{
SqlTransaction trans = sqlConnection.BeginTransaction();
try
{
if (updateName)
{
if (filterData.FilterName != dtFilter.Rows[0]["name"].ToString())
{
par.Add("@name", typeof(String)).Value = filterData.FilterName;
log.Add("Name changed from '" + dtFilter.Rows[0]["name"] + "' to '" + filterData.FilterName + "'");
ExecuteNonQuery(sqlConnection, "update filters set name = @name where id = @filter_id", CommandType.Text, par, trans);
}
}
if (updateConditions && filterData.FilterGroups.Count > 0)
{
//Busca todas as regras deste filtro no DB
DataTable dtFilterConditions = ExecuteDataTable(sqlConnection, "select fc.* from filters_conditions fc with(nolock) where fc.filter_id = @filter_id", CommandType.Text, par, trans);
List <String> contains = new List <String>();
List <DbParameterCollection> newItems = new List <DbParameterCollection>();
foreach (FilterGroup g in filterData.FilterGroups)
{
foreach (FilterCondition f in g.FilterRules)
{
Boolean addNew = false;
if ((dtFilterConditions != null) && (dtFilterConditions.Rows.Count > 0))
{
String s = "group_id = '" + g.GroupId + "' and field_id = " + f.FieldId + " and text = '" + f.DataString + "' and condition = '" + f.ConditionType.ToString() + "'";
DataRow[] sel = dtFilterConditions.Select(s);
if (sel.Length > 0)
{
contains.Add(sel[0]["id"].ToString());
//Atualiza
if ((sel[0]["group_selector"].ToString().ToLower() != g.Selector.ToString().ToLower()) || (sel[0]["selector"].ToString().ToLower() != f.Selector.ToString().ToLower()))
{
DbParameterCollection p3 = new DbParameterCollection();
p3.Add("@condition_id", typeof(Int64)).Value = (Int64)sel[0]["id"];
p3.Add("@group_selector", typeof(String)).Value = g.Selector.ToString();
p3.Add("@selector", typeof(String)).Value = f.Selector.ToString();
log.Add("Condition updated: group = " + g.GroupId + ", selector = " + f.Selector.ToString() + ", condition = " + f.ToString());
ExecuteNonQuery(sqlConnection, "update filters_conditions set [group_selector] = @group_selector, [selector] = @selector where id = @condition_id", CommandType.Text, p3, trans);
}
}
else
{
addNew = true;
}
}
else
{
addNew = true;
}
//Adiciona a condição
if (addNew)
{
DbParameterCollection p2 = new DbParameterCollection();
p2.Add("@filter_id", typeof(Int64)).Value = filterid;
p2.Add("@group_id", typeof(String)).Value = g.GroupId;
p2.Add("@group_selector", typeof(String)).Value = g.Selector.ToString();
p2.Add("@field_id", typeof(String)).Value = f.FieldId;
p2.Add("@text", typeof(String)).Value = f.DataString;
p2.Add("@condition", typeof(String)).Value = f.ConditionType.ToString();
p2.Add("@selector", typeof(String)).Value = f.Selector.ToString();
newItems.Add(p2);
log.Add("Condition inserted: group = " + g.GroupId + ", condition = " + f.ToString());
}
}
}
log.Add("");
//Deleta as condições que não estão sendo utilizadas
if (contains.Count > 0)
{
DataTable dtFc = ExecuteDataTable(sqlConnection, "select f.*, f1.name field_name, f1.data_type from filters_conditions f with(nolock) inner join field f1 with(nolock) on f1.id = f.field_id where f.filter_id = @filter_id and f.id not in (" + String.Join(",", contains) + ")", CommandType.Text, par, trans);
if ((dtFc != null) && (dtFc.Rows.Count > 0))
{
FilterRule fdTmp = new FilterRule("");
foreach (DataRow dr2 in dtFc.Rows)
{
fdTmp.AddCondition(dr2["group_id"].ToString(), dr2["group_selector"].ToString(), (Int64)dr2["field_id"], dr2["field_name"].ToString(), dr2["data_type"].ToString(), dr2["text"].ToString(), dr2["condition"].ToString(), dr2["selector"].ToString());
}
foreach (FilterGroup g in fdTmp.FilterGroups)
{
foreach (FilterCondition f in g.FilterRules)
{
log.Add("Condition deleted: group = " + g.GroupId + ", condition = " + f.ToString());
}
}
}
ExecuteNonQuery(sqlConnection, "delete from filters_conditions where filter_id = @filter_id and id not in (" + String.Join(",", contains) + ")", CommandType.Text, par, trans);
}
else
{
ExecuteNonQuery(sqlConnection, "delete from filters_conditions where filter_id = @filter_id", CommandType.Text, par, trans);
}
foreach (DbParameterCollection p2 in newItems)
{
ExecuteNonQuery(sqlConnection, "insert into filters_conditions ([filter_id] ,[group_id] ,[group_selector] ,[field_id] ,[text] ,[condition] ,[selector]) VALUES (@filter_id,@group_id,@group_selector,@field_id,@text,@condition,@selector)", CommandType.Text, p2, trans);
}
}
log.Add("");
log.Add("Filtro:");
log.Add(filterData.ToString());
AddUserLog(sqlConnection, LogKey.Filter_Changed, null, "API", UserLogLevel.Info, 0, this._enterpriseId, 0, 0, 0, 0, 0, "Filter changed", String.Join("\r\n", log), acl.EntityId, trans);
trans.Commit();
trans = null;
}
catch (Exception ex)
{
Error(ErrorType.InvalidRequest, "Error on update filter", "", null);
return(null);
}
finally
{
//Saída sem aviso, ou seja, erro
if (trans != null)
{
trans.Rollback();
}
}
}
return(get(sqlConnection, parameters));
}
/// <summary>
/// Método privado para processamento do método 'user.resetpassword'
/// </summary>
/// <param name="sqlConnection">Conexão com o banco de dados MS-SQL</param>
/// <param name="parameters">Dicionário (String, Object) contendo todos os parâmetros necessários</param>
private Dictionary <String, Object> get(SqlConnection sqlConnection, Dictionary <String, Object> parameters)
{
Dictionary <String, Object> result = new Dictionary <String, Object>();
if (!parameters.ContainsKey("filterid"))
{
Error(ErrorType.InvalidRequest, "Parameter filterid is not defined.", "", null);
return(null);
}
String filter = parameters["filterid"].ToString();
if (String.IsNullOrWhiteSpace(filter))
{
Error(ErrorType.InvalidRequest, "Parameter filterid is not defined.", "", null);
return(null);
}
Int64 filterid = 0;
try
{
filterid = Int64.Parse(filter);
}
catch
{
Error(ErrorType.InvalidRequest, "Parameter filterid is not a long integer.", "", null);
return(null);
}
DbParameterCollection par = new DbParameterCollection();
par.Add("@enterprise_id", typeof(Int64)).Value = this._enterpriseId;
par.Add("@filter_id", typeof(Int64)).Value = filterid;
DataTable dtFilter = ExecuteDataTable(sqlConnection, "select f.*, ignore_qty = (select COUNT(distinct i1.filter_id) from resource_plugin_ignore_filter i1 with(nolock) where i1.filter_id = f.id), lock_qty = (select COUNT(distinct l1.filter_id) from resource_plugin_lock_filter l1 with(nolock) where l1.filter_id = f.id), role_qty = (select COUNT(distinct r1.filter_id) from resource_plugin_role_filter r1 with(nolock) where r1.filter_id = f.id) from filters f with(nolock) where f.enterprise_id = @enterprise_id and f.id = @filter_id", CommandType.Text, par, null);
if (dtFilter == null)
{
Error(ErrorType.InternalError, "", "", null);
return(null);
}
if (dtFilter.Rows.Count == 0)
{
Error(ErrorType.InvalidRequest, "Filter not found.", "", null);
return(null);
}
DataRow dr1 = dtFilter.Rows[0];
Dictionary <string, object> newItem = new Dictionary <string, object>();
newItem.Add("enterprise_id", dr1["enterprise_id"]);
newItem.Add("filter_id", dr1["id"]);
newItem.Add("name", dr1["name"]);
newItem.Add("ignore_qty", dr1["ignore_qty"]);
newItem.Add("lock_qty", dr1["lock_qty"]);
newItem.Add("role_qty", dr1["role_qty"]);
newItem.Add("create_date", (dr1["create_date"] != DBNull.Value ? (Int32)((((DateTime)dr1["create_date"]) - new DateTime(1970, 1, 1)).TotalSeconds) : 0));
//Lista as condições
List <Dictionary <String, Object> > conditions = new List <Dictionary <string, object> >();
FilterRule f = new FilterRule(dr1["name"].ToString());
DataTable dt2 = ExecuteDataTable(sqlConnection, "select f.*, f1.name field_name, f1.data_type from filters_conditions f with(nolock) inner join field f1 with(nolock) on f1.id = f.field_id where f.filter_id = " + dr1["id"] + " order by f.group_id, f1.name");
if ((dt2 != null) || (dt2.Rows.Count > 0))
{
foreach (DataRow dr2 in dt2.Rows)
{
Dictionary <string, object> c1 = new Dictionary <string, object>();
c1.Add("group_id", dr2["group_id"].ToString());
c1.Add("group_selector", dr2["group_selector"].ToString());
c1.Add("field_id", (Int64)dr2["field_id"]);
c1.Add("field_name", dr2["field_name"].ToString());
c1.Add("data_type", dr2["data_type"].ToString());
c1.Add("text", dr2["text"].ToString());
c1.Add("condition", dr2["condition"].ToString());
c1.Add("selector", dr2["selector"].ToString());
conditions.Add(c1);
f.AddCondition(dr2["group_id"].ToString(), dr2["group_selector"].ToString(), (Int64)dr2["field_id"], dr2["field_name"].ToString(), dr2["data_type"].ToString(), dr2["text"].ToString(), dr2["condition"].ToString(), dr2["selector"].ToString());
}
}
newItem.Add("conditions_description", f.ToString());
newItem.Add("conditions", conditions);
result.Add("info", newItem);
return(result);
}
