private object Filtrado(SQL_Connector conn, object input, BackgroundWorker bg)
{
string keyParamSearch = "@Buscador",
keyParamUser = "******",
keyParamIDDR = "@IdDR",
idDR = ddlDRSembrado.SelectedValue.Split('-')[0];
Dictionary<string, object> parameters = new Dictionary<string, object>()
{
{ keyParamSearch, '%'+txtSearch.Text+'%' },
{ keyParamUser, input.ToString() },
{ keyParamIDDR, idDR }
};
string query = "",
where = "";
where = string.Format(" WHERE CLA_USUARIO = {0} AND ID_DR_SEMBRADO = {1} AND NOMBRE LIKE {2} ORDER BY ID_SEMBRADO", keyParamUser, keyParamIDDR, keyParamSearch);
query = Queries.GetFraccsUsuario + where;
DataSet dtSet = conn.SelectTables(query, parameters);
if (dtSet.Tables.Count > 0)
return dtSet.Tables[0];
else
return null;
}
private object UpdateUser(SQL_Connector conn, object input, BackgroundWorker bg)
{
int idDR = (int)input;
string userToUpdate = GetUserParam(),
msg = "",
mainUser = Session[Constant.KeyUserSession].ToString();
Dictionary<string, object> dicParams = new Dictionary<string, object>()
{
{"@IdDR",idDR },
{"@ClaUsuario",userToUpdate },
{"@Estatus", cActivo.Checked },
{"@SincSembrado ", cSincroniza.Checked },
{ "@UsuarioMod",(mainUser.Split('-')[0]).ToUpper() },
{"@FechaMod", DateTime.Now }
};
int rowsAffected = conn.Command(Queries.UpdateDRUsuario, dicParams);
if (rowsAffected == 1)
msg = "Se actualizó 1 registro de exitosamente";
else
msg = "Se actualizaron " + rowsAffected +" registros exitosamente";
return msg;
}
private object SelectFraccs(SQL_Connector conn, object input, BackgroundWorker bg)
{
if(input != null && HasUserParam())
{
int idDR = (int)input;
string Usuario = GetUserParam(),
keyParamUsuario = "@Usuario",
keyParamId = "@IdDR",
query = Queries.GetFraccsUsuario + string.Format(" WHERE CLA_USUARIO = {0} AND ID_DR_SEMBRADO = {1}", keyParamUsuario, keyParamId);
Dictionary<string, object> dictionaryParams = new Dictionary<string, object>()
{
{ keyParamUsuario, Usuario },
{ keyParamId, idDR }
};
DataSet dtSet = conn.SelectTables(query, dictionaryParams);
if (dtSet.Tables.Count > 0)
return dtSet.Tables[0];
}
return null;
}
private object EjecutaQuery(SQL_Connector conn, object input, BackgroundWorker bg)
{
string query = "",
paramUsuario = "@ClaUsuario",
paramIdMachote = "@IdMachote",
Usuario = this.Master.GetSessionUser();
Dictionary <string, object> dicParams = new Dictionary <string, object>()
{
{ paramUsuario, Usuario }
};
query = string.Join(" ", Queries.GetDescViviendas,
Queries.GetDRUsuario + " WHERE CLA_USUARIO = " + paramUsuario,
Queries.GetTipoBloque,
Queries.GetVariables);
if (input != null)
{
int idMachote = (int)input;
//Agrego el parámetro para la consulta
dicParams.Add(paramIdMachote, idMachote);
query = query + string.Format(" {0} WHERE CLA_USUARIO = {1} AND ID_MACHOTE = {2}", Queries.GetEncMachotes, paramUsuario, paramIdMachote);
query = query + string.Format(" {0} WHERE ID_MACHOTE = {1} ORDER BY ORDEN", Queries.GetBloquesBase, paramIdMachote);
}
return(conn.SelectTables(query, dicParams));
}
private object Filtrado(SQL_Connector conn, object input, BackgroundWorker bg)
{
string keyParamBuscar = "@Buscador",
keyParamClaUsuario = "@ClaUsuario";
Dictionary <string, object> parameters = new Dictionary <string, object>()
{
{ keyParamBuscar, '%' + (txtSearch.Text ?? "") + '%' },
{ keyParamClaUsuario, input.ToString() }
};
string query = "",
where = "";
where = string.Format(" WHERE CLA_USUARIO = {0} AND ID_MACHOTE IN ({1}) AND (ENC_MACHOTE like {2} OR NOM_DR like {2} OR CANT_VIVS like {2}) ORDER BY ID_MACHOTE", keyParamClaUsuario, Queries.GetDistMachotesGenerados, keyParamBuscar);
query = Queries.GetEncMachotes + where;
DataSet dtSet = conn.SelectTables(query, parameters);
if (dtSet.Tables.Count > 0)
{
return(dtSet.Tables[0]);
}
else
{
return(null);
}
}
private object obtieneReportes(SQL_Connector conn, object input, BackgroundWorker bg)
{
DataSet dtSet = new DataSet();
if (input != null)
{
string paramUser = "******", query = "";
Dictionary <string, object> dicParams = new Dictionary <string, object>()
{
{ paramUser, input.ToString() }
};
query = Queries.GetEncMachotes + string.Format(" WHERE CLA_USUARIO = {0} AND ID_MACHOTE IN ({1})", paramUser, Queries.GetDistMachotesGenerados);
dtSet = conn.SelectTables(query, dicParams);
}
if (dtSet.Tables.Count > 0)
{
return(dtSet.Tables[0]);
}
else
{
return(null);
}
}
private object ActualizaVar(SQL_Connector conn, object input, BackgroundWorker bg)
{
string Usuario = this.Master.GetSessionUser(),
msg = "";
Dictionary <string, object> dictionaryParams = new Dictionary <string, object>()
{
{ "@NomCorto", txtNomCorto.Value ?? "" },
{ "@NomVar", txtNomVariable.Value ?? "" },
{ "@Valor", txtValor.Value ?? "" },
{ "@Estatus", cActivo.Checked ? 1 : 0 },
{ "@EsCalculado", cCalculado.Checked ? 1 : 0 },
{ "@ConvLetra", cConvLetra.Checked ? 1 : 0 },
{ "@IdTipoBloque", cTipoBloque.SelectedValue },
{ "@IdUnidad", cUnidad.SelectedValue },
{ "@UsuarioMod", Usuario },
{ "@FechaMod", DateTime.Now },
{ "@IdVar", (int)input }
};
int rowsAffected = conn.Command(Queries.UpdateVars, dictionaryParams);
if (rowsAffected == 1)
{
msg = "Se actualizó 1 registro";
}
else
{
msg = "Se actualizaron " + rowsAffected + "registros";
}
return(msg);
}
private object UpsertMachote(SQL_Connector conn, object input, BackgroundWorker bg)
{
string procedure = "sp_MergeMachotes",
returnMsg = "";
Dictionary <string, object> dicParameters = new Dictionary <string, object>()
{
{ "@NomPrototipo", txtPrototipo.Value },
{ "@CantVivs", ddlCantVivs.SelectedValue },
{ "@IdDR", ddlUEN.SelectedValue },
{ "@Estatus", cActivo.Checked },
{ "@Descripcion", txtDescripcion.Text.Replace(Environment.NewLine, "") },
{ "@PropiedadPrivada", txtAreaPP.Text.Replace(Environment.NewLine, "") },
{ "@PropiedadComun", txtAreaPC.Text.Replace(Environment.NewLine, "") },
{ "@ClaUsuario", this.Master.GetSessionUser() },
{ "@IdMachote", DBNull.Value }
};
if (input != null)
{
dicParameters["@IdMachote"] = (int)input;
}
int rowsAffected = conn.spMachotes(procedure, dicParameters, out returnMsg);
return(returnMsg);
}
private object InsertaVar(SQL_Connector conn, object input, BackgroundWorker bg)
{
string Usuario = this.Master.GetSessionUser(),
msg = "";
Dictionary <string, object> dictionaryParams = new Dictionary <string, object>()
{
{ "@NomCorto", txtNomCorto.Value ?? "" },
{ "@NomVar", txtNomVariable.Value ?? "" },
{ "@Valor", txtValor.Value ?? "" },
{ "@Estatus", cActivo.Checked ? 1 : 0 },
{ "@EsCalculado", 0 },
{ "@ConvLetra", cConvLetra.Checked ? 1 : 0 },
{ "@IdTipoBloque", cTipoBloque.SelectedValue },
{ "@IdUnidad", cUnidad.SelectedValue },
{ "@UsuarioMod", Usuario },
{ "@FechaMod", DateTime.Now }
};
int idInserted = conn.Command(Queries.InsertVars, dictionaryParams, true);
if (idInserted > 0)
{
msg = "Se insertó el registro de manera correcta";
txtIdVar.Value = idInserted.ToString();
headVar.Text = txtNomVariable.Value;
}
else
{
msg = "No se pudo insertar el registro";
}
return(msg);
}
private object Filtrado(SQL_Connector conn, object input, BackgroundWorker bg)
{
string keyParam = "@Buscador";
Dictionary <string, object> parameters = new Dictionary <string, object>()
{
{ keyParam, ('%' + txtSearch.Text ?? "") + '%' }
};
string query = "";
query = Queries.GetUsuariosConteo + " WHERE CLA_USUARIO LIKE " + keyParam + " ORDER BY CLA_USUARIO";
return(conn.SelectTables(query, parameters));
}
private void Bg_DoWork(object sender, DoWorkEventArgs e)
{
string connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SQL_Connector conn = new SQL_Connector(connectionString))
{
try
{
e.Result = this.Action(conn, this.Input, this.Bg);
}
catch (Exception)
{
}
}
}
private object Filtrado(SQL_Connector conn, object input, BackgroundWorker bg)
{
string keyParam = "@Buscador";
Dictionary <string, object> parameters = new Dictionary <string, object>()
{
{ keyParam, '%' + (txtSearch.Text ?? "") + '%' }
};
string query = "",
where = "";
where = string.Format(" WHERE NOM_CORTO like {0} OR NOM_VAR like {0} OR NOM_TIPO_BLOQUE like {0} OR DESC_UNIDAD like {0} ORDER BY ID_VAR", keyParam);
query = Queries.GetVariables + where;
return(conn.SelectTables(query, parameters));
}
private object SelectDirRegional(SQL_Connector conn, object input, BackgroundWorker bg)
{
string usuarioBusqueda = input.ToString(),
keyUserParam = "@Usuario",
query = Queries.GetDRUsuario + " WHERE CLA_USUARIO = " + keyUserParam + " ORDER BY ID_DR_SEMBRADO";
Dictionary<string, object> dictionaryParam = new Dictionary<string, object>()
{
{keyUserParam, usuarioBusqueda}
};
DataSet dtSet = conn.SelectTables(query, dictionaryParam);
if (dtSet.Tables.Count > 0)
return dtSet.Tables[0];
else
return null;
}
private object GenerarWord(SQL_Connector conn, object input, BackgroundWorker bg)
{
int idMachote = (int)input;
string paramIdMachote = "@IdMachote",
query = "";
Dictionary <string, object> dicParams = new Dictionary <string, object>()
{
{ paramIdMachote, idMachote }
};
query = Queries.GetMachotesCalc + " WHERE ID_MACHOTE = " + paramIdMachote + " ORDER BY ORDEN ASC";
DataSet dtSet = conn.SelectTables(query, dicParams);
if (dtSet.Tables.Count > 0)
{
DataTable dt = dtSet.Tables[0];
if (dt.Rows.Count > 0)
{
string encMachote = dt.Rows[0]["ENC_MACHOTE"].ToString(),
fileCreated = "";
if (Office.FileFolderExist())
{
fileCreated = Office.ExportToWord(dt, encMachote);
}
return(fileCreated);
}
else
{
return(null);
}
}
else
{
return(null);
}
}
private void RevisarPermisos()
{
string connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SQL_Connector conn = new SQL_Connector(connectionString))
{
List <string> resultRows = new List <string>();
conn.Select(Queries.GetAdmins + string.Format(" WHERE CLA_USUARIO = '{0}' AND ESTATUS = 1", this.GetSessionUser()), out resultRows, '|');
if (resultRows.Count > 0)
{
Session[Constant.KeyEsAdmin] = true;
}
else
{
Session[Constant.KeyEsAdmin] = false;
HideAdminLink();
}
}
}
private object EjecutaQuery(SQL_Connector conn, object input, BackgroundWorker bg)
{
Dictionary <string, object> paramId = new Dictionary <string, object>();
string queryCatalogos = string.Join(" ", Queries.GetUnidades, Queries.GetBloques);
string nameIdParam = "@Id";
if (input != null)
{
string queryOneVar = Queries.GetVariables + " WHERE ID_VAR = " + nameIdParam;
queryCatalogos = queryCatalogos + " " + queryOneVar;
paramId.Add(nameIdParam, IdParameter.ToString());
return(conn.SelectTables(queryCatalogos, paramId));
}
return(conn.SelectTables(queryCatalogos));
}
private object ActualizaVars(SQL_Connector conn, object input, BackgroundWorker bg)
{
string paramIdTipoBloque = "@IdTipoBloque", query = "";
Dictionary <string, object> dicParams = new Dictionary <string, object>()
{
{ paramIdTipoBloque, Convert.ToInt32(input) }
};
query = Queries.GetVariables + " WHERE ID_TIPO_BLOQUE = " + paramIdTipoBloque;
DataSet dtSet = conn.SelectTables(query, dicParams);
if (dtSet.Tables.Count > 0)
{
return(dtSet.Tables[0]);
}
else
{
return(null);
}
}
private object obtenerEncabezados(SQL_Connector conn, object input, BackgroundWorker bg)
{
string keyParamUsuario = "@ClaUsuario",
query = "";
Dictionary <string, object> dicParams = new Dictionary <string, object>()
{
{ keyParamUsuario, input.ToString() }
};
query = Queries.GetEncMachotes + " WHERE CLA_USUARIO = " + keyParamUsuario + " ORDER BY ID_MACHOTE";
DataSet dtSet = conn.SelectTables(query, dicParams);
if (dtSet.Tables.Count > 0)
{
return(dtSet.Tables[0]);
}
else
{
return(null);
}
}
private object obtieneVariables(SQL_Connector conn, object input, BackgroundWorker bg)
{
return(conn.SelectTables(Queries.GetVariables));
}
private object EjecutarQuery(SQL_Connector conn, object input, BackgroundWorker bg)
{
return(conn.SelectTables(Queries.GetUsuariosConteo + " ORDER BY CLA_USUARIO"));
}
