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