public DataTable GetAllZoneByDivisionAndCVe()
{
try
{
string query = string.Format("SELECT count(1) existe " +
"FROM ContactosCFE c " +
"WHERE " +
" c.Activo = @Activo " +
"and(" +
" (" +
" rtrim(ltrim(Nombre)) = upper(@strName) " +
" and rtrim(ltrim(ApPaterno)) = upper(@strFirstName) " +
" and rtrim(ltrim(ApMaterno)) = upper(@strLastName) " +
" ) " +
" or rtrim(ltrim(Correo)) = upper(@strEmail) " +
" ) " +
"");
SqlParameter[] sqlParameters = new SqlParameter[5];
sqlParameters[0] = new SqlParameter("@Activo", SqlDbType.SmallInt);
sqlParameters[0].Value = Convert.ToString(intActivo);
sqlParameters[1] = new SqlParameter("@strName", SqlDbType.NChar);
sqlParameters[1].Value = Convert.ToString(strName);
sqlParameters[2] = new SqlParameter("@strFirstName", SqlDbType.NChar);
sqlParameters[2].Value = Convert.ToString(strFirstName);
sqlParameters[3] = new SqlParameter("@strLastName", SqlDbType.NChar);
sqlParameters[3].Value = Convert.ToString(strLastName);
sqlParameters[4] = new SqlParameter("@strEmail", SqlDbType.NChar);
sqlParameters[4].Value = Convert.ToString(strEmail);
con.dbConnection();
Allzone = con.executeSelectQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GetAllZoneByDivisionAndCVeID";
clsError.LogWrite();
}
return(Allzone);
}
/// <summary>
/// Create table divison schema just columns
/// </summary>
/// <returns></returns>
public StringBuilder CreateTableHTML4Contacts(DataTable dtContact)
{
Class.Encrypt oclsEncrypt = new Class.Encrypt();
//Building an HTML string.
StringBuilder html = new StringBuilder();
try
{
string strRPU = string.Empty;
html.Append(" <thead>");
//Building the Header row.
html.Append("<tr>");
foreach (DataColumn column in dtContact.Columns)
{
html.Append("<th>");
html.Append(column.ColumnName);
html.Append("</th>");
}
html.Append("</tr>");
html.Append("</thead>");
html.Append("<tbody id='myTable'> ");
//Building the Data rows.
foreach (DataRow row in dtContact.Rows)
{
html.Append("<tr>");
foreach (DataColumn column in dtContact.Columns)
{
html.Append("<td>");
html.Append(row[column.ColumnName]);
html.Append("</td>");
}
html.Append("</tr>");
}
html.Append("</tbody>");
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "CreateTableHTML";
clsError.LogWrite();
}
return(html);
}
/// <summary>
/// Update Zone into the database
/// </summary>
/// <returns></returns>
public Boolean UpdateZone()
{
Boolean msg = true;
try
{
//string query = string.Format("Update Zona SET CveZona = upper(@CveZona), Observaciones = upper(@strObservation) where Activo = @Activo and Division= @Division and Zona=upper(@Zone) ");
string query = string.Format("UPDATE Zonas " +
" SET CveZona = upper(@CveZona)" +
" , Zona = upper(@Zone)" +
" , Observaciones = upper(@strObservation) " +
" WHERE Activo = @Activo " +
" AND idzona = @IdZone ");
SqlParameter[] sqlParameters = new SqlParameter[5];
sqlParameters[0] = new SqlParameter("@CveZona", SqlDbType.NVarChar);
sqlParameters[0].Value = Convert.ToString(strCveZone);
sqlParameters[1] = new SqlParameter("@Zone", SqlDbType.NVarChar);
sqlParameters[1].Value = Convert.ToString(strZone);
sqlParameters[2] = new SqlParameter("@strObservation", SqlDbType.NVarChar);
sqlParameters[2].Value = Convert.ToString(strObservation);
sqlParameters[3] = new SqlParameter("@Activo", SqlDbType.SmallInt);
sqlParameters[3].Value = Convert.ToString(intActivo);
//sqlParameters[4] = new SqlParameter("@Division", SqlDbType.NVarChar);
//sqlParameters[4].Value = Convert.ToString(strDivision);
sqlParameters[4] = new SqlParameter("@IdZone", SqlDbType.NVarChar);
sqlParameters[4].Value = Convert.ToString(intIdZone);
con.dbConnection();
msg = con.executeUpdateQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "UpdateZone";
clsError.LogWrite();
msg = false;
}
return(msg);
}
public DataTable GetContactsCFE(string strRPU)
{
Class.Business.clsContactCFE oclsCFE = new Class.Business.clsContactCFE();
try
{
dtSerarchRPU = null;
dtSerarchRPU = oclsCFE.GetContactCFEByRPU(strRPU);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "SearchRPU";
clsError.LogWrite();
}
return(dtSerarchRPU);
}
public Boolean Update()
{
Boolean msg = true;
try
{
string query = string.Format("spUpdateConvenio");
SqlParameter[] sqlParameters = new SqlParameter[3];
sqlParameters[0] = new SqlParameter("@Descripcion", SqlDbType.NVarChar);
sqlParameters[0].Value = Convert.ToString(Descripción);
sqlParameters[1] = new SqlParameter("@Estatus", SqlDbType.NVarChar);
sqlParameters[1].Value = Convert.ToString(Estatus);
sqlParameters[2] = new SqlParameter("@IdConvenio", SqlDbType.Int);
sqlParameters[2].Value = Convert.ToString(idConvenio);
//Para barrer el estatus de los conveniones existentes a el esatus anterior
if (Estatus == "Actual")
{
string query2 = string.Format("[spUpdateEstatusConvenio]");
SqlParameter[] sqlParameters2 = new SqlParameter[1];
sqlParameters2[0] = new SqlParameter("@Convenio", SqlDbType.Int);
sqlParameters2[0].Value = Convert.ToInt32(Convenio);
con.dbConnection();
DataTable result = con.executeStoreProcedure(query2, sqlParameters2);
}
con.dbConnection();
DataTable result2 = con.executeStoreProcedure(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "UpdateConvenio";
clsError.LogWrite();
msg = false;
}
return(msg);
}
public Boolean UpdateRUP(String strRPU, String intEStatus, String intGestorMedicion, String intGestorComunicaciones, int conPrelacion, string strIdUsuario, string strComenTMedidor, int TipoComentario)
{
ConnectionDB con = new ConnectionDB();
Boolean bMsg = true;
DataTable dtData;
try
{
string query = string.Format("spUpdate_Rup");
SqlParameter[] sqlParameters = new SqlParameter[8];
sqlParameters[0] = new SqlParameter("@chrIdEstatus", SqlDbType.Int);
sqlParameters[0].Value = intEStatus;
sqlParameters[1] = new SqlParameter("@chrIdGestorComercial", SqlDbType.NVarChar);
sqlParameters[1].Value = intGestorComunicaciones;
sqlParameters[2] = new SqlParameter("@chrIdGestorMedicion", SqlDbType.NVarChar);
sqlParameters[2].Value = intGestorMedicion;
sqlParameters[3] = new SqlParameter("@chrRPU", SqlDbType.NVarChar);
sqlParameters[3].Value = strRPU;
sqlParameters[4] = new SqlParameter("@chrIdPrelacion", SqlDbType.Int);
sqlParameters[4].Value = conPrelacion;
sqlParameters[5] = new SqlParameter("@IdUsuario", SqlDbType.NVarChar);
sqlParameters[5].Value = strIdUsuario;
sqlParameters[6] = new SqlParameter("@Comentario", SqlDbType.VarChar);
sqlParameters[6].Value = strComenTMedidor;
sqlParameters[7] = new SqlParameter("@TipoComentario", SqlDbType.Int);
sqlParameters[7].Value = TipoComentario;
con.dbConnection();
dtData = con.executeStoreProcedure(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "UpdateRUP";
clsError.LogWrite();
bMsg = false;
}
return(bMsg);
}
public DataTable GetCommunicationType()
{
try
{
ConnectionDB con = new ConnectionDB();
string query = string.Format("spBuscarTipoComunicacion");
SqlParameter[] sqlParameters = new SqlParameter[0];
con.dbConnection();
dtData = con.executeStoreProcedure(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GetGroup";
clsError.LogWrite();
}
return(dtData);
}
public StringBuilder CreateTableHTML()
{
//Building an HTML string.
StringBuilder html = new StringBuilder();
try
{
html.Append(" <thead>");
//Building the Header row.
html.Append("<tr>");
foreach (DataColumn column in dtShiping.Columns)
{
html.Append("<th class='text-uppercase'>");
html.Append(column.ColumnName);
html.Append("</th>");
}
html.Append("</tr>");
html.Append("</thead>");
html.Append("<tbody id='myTable'> ");
//Building the Data rows.
foreach (DataRow row in dtShiping.Rows)
{
html.Append("<tr>");
foreach (DataColumn column in dtShiping.Columns)
{
html.Append("<td>");
html.Append(row[column.ColumnName]);
html.Append("</td>");
}
html.Append("</tr>");
}
html.Append("</tbody>");
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "CreateTableHTML";
clsError.LogWrite();
}
return(html);
}
public Boolean UpdateGroup()
{
Boolean msg = true;
try
{
//string query = string.Format("Update Grupos SET Grupo = upper(@Grupo), FechaInicioOperaciones = @InicioOperaciones, IdGMedicion = @IdGMedicion, IdGComercial = @IdGComercial where Activo = @Activo and IdGrupo= @IdGrupo");
string query = string.Format("spUpdateGrupo");
SqlParameter[] sqlParameters = new SqlParameter[6];
sqlParameters[0] = new SqlParameter("@Grupo", SqlDbType.NVarChar);
sqlParameters[0].Value = Convert.ToString(Grupo);
sqlParameters[1] = new SqlParameter("@InicioOperaciones", SqlDbType.NVarChar);
sqlParameters[1].Value = Convert.ToString(InicioOperaciones);
sqlParameters[2] = new SqlParameter("@Activo", SqlDbType.SmallInt);
sqlParameters[2].Value = Convert.ToInt32(Activo);
sqlParameters[3] = new SqlParameter("@IdGrupo", SqlDbType.Int);
sqlParameters[3].Value = Convert.ToInt32(idGrupo);
sqlParameters[4] = new SqlParameter("@IdGMedicion", SqlDbType.Int);
sqlParameters[4].Value = IdMed;
sqlParameters[5] = new SqlParameter("@IdGComercial", SqlDbType.Int);
sqlParameters[5].Value = IdComer;
con.dbConnection();
con.executeStoreProcedure(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "UpdateGroup";
clsError.LogWrite();
msg = false;
}
return(msg);
}
public DataTable SearchRPU(string strRPU)
{
try
{
ConnectionDB con = new ConnectionDB();
string query = string.Format("Select pc.IdGrupo, g.Grupo " +
" , RPU + ' -- ' + PuntoCarga as RPUPuntoCarga " +
" , PuntoCarga AS[Punto de Carga] " +
" , RPU " +
" , Direccion [Dirección]" +
" , pc.IdTarifa, t.Tarifa " +
" , PorteoMaximo AS[Capacidad MW] " +
" , z.IdDIVISION, d.Division [División] " +
" , pc.IdZONA, z.Zona " +
" , pc.FechaCreacion AS[Fecha Alta] " +
" ,e.Estatus , pc.IdEstatus,pc.IdGestorComercial, pc.IdGestorMedicion,pc.ConPrelacion [Con Prelación] " +
// ", pc.IdEstatus, pc.IdGestorComercial, pc.IdGestorMedicion " +
" from PuntosCarga PC with(nolock) " +
" JOIN Grupos g ON pc.IdGrupo = g.idGrupo " +
" JOIN Tarifas t on pc.IdTarifa = t.IdTarifa " +
" JOIN Zonas z on pc.IdZona = z.IdZona " +
" JOIN Divisiones d on z.IdDivision = d.IdDivision " +
" JOIN PuntosCargaEstatus e on pc.IdEstatus = e.IdEstatus " +
" where PC.Activo=@intActive and PC.RPU=@strRPU ");
//query = "spBuscarRPU";
SqlParameter[] sqlParameters = new SqlParameter[2];
sqlParameters[0] = new SqlParameter("@intActive", SqlDbType.SmallInt);
sqlParameters[0].Value = Convert.ToString(intActive);
sqlParameters[1] = new SqlParameter("@strRPU", SqlDbType.NVarChar);
sqlParameters[1].Value = Convert.ToString(strRPU);
con.dbConnection();
dtSerarchRPU = con.executeSelectQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "SearchRPU";
clsError.LogWrite();
}
return(dtSerarchRPU);
}
public Boolean InsertMeters()
{
Boolean msg = true;
SqlDataReader drInsert;
DataTable dtData;
try
{
string query = string.Format("spInsertar_Medicion");
SqlParameter[] sqlParameters = new SqlParameter[8];
sqlParameters[0] = new SqlParameter("@intIdParametrosMedidor", SqlDbType.Int);
sqlParameters[0].Value = Convert.ToString(intIDParametersMeters);
sqlParameters[1] = new SqlParameter("@chrRPU", SqlDbType.NVarChar);
sqlParameters[1].Value = strRPU;
sqlParameters[2] = new SqlParameter("@chrEstatus", SqlDbType.NVarChar);
sqlParameters[2].Value = strStatus;
sqlParameters[3] = new SqlParameter("@chrEmail", SqlDbType.NVarChar);
sqlParameters[3].Value = strEmailusr;
sqlParameters[4] = new SqlParameter("@chrFechaPrevista", SqlDbType.NVarChar);
sqlParameters[4].Value = strDeliveryDate;
sqlParameters[5] = new SqlParameter("@chrFechaInstalacion", SqlDbType.NVarChar);
sqlParameters[5].Value = strInstallationDate;
sqlParameters[6] = new SqlParameter("@smiCheckActivo", SqlDbType.SmallInt);
sqlParameters[6].Value = intCheckActivo;
sqlParameters[7] = new SqlParameter("@chrObservaciones", SqlDbType.NVarChar);
sqlParameters[7].Value = strObservation;
con.dbConnection();
dtData = con.executeStoreProcedure(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "UpdateGroup";
clsError.LogWrite();
msg = false;
}
return(msg);
}
public DataTable ExistsId()
{
try
{
string query = string.Format("select count(1) existe from gestores where Activo = @Activo and ( ( Nombre = @Nombre and ApPaterno = @ApPaterno and ApMaterno = @ApMaterno and cve = @Cve) AND ( NumeroEmpleado = @NumeroEmpleado ) ) and IdGestor <> @Id ");
SqlParameter[] sqlParameters = new SqlParameter[7];
sqlParameters[0] = new SqlParameter("@Activo", SqlDbType.SmallInt);
sqlParameters[0].Value = Convert.ToString(intActivo);
sqlParameters[1] = new SqlParameter("@Nombre", SqlDbType.NVarChar);
sqlParameters[1].Value = Convert.ToString(strName);
sqlParameters[2] = new SqlParameter("@ApPaterno", SqlDbType.NVarChar);
sqlParameters[2].Value = Convert.ToString(strFirstName);
sqlParameters[3] = new SqlParameter("@ApMaterno", SqlDbType.NVarChar);
sqlParameters[3].Value = Convert.ToString(strLastName);
sqlParameters[4] = new SqlParameter("@Cve", SqlDbType.NVarChar);
sqlParameters[4].Value = Convert.ToString(strIniciales);
sqlParameters[5] = new SqlParameter("@NumeroEmpleado", SqlDbType.NVarChar);
sqlParameters[5].Value = Convert.ToString(strNumeroEmpleado);
sqlParameters[6] = new SqlParameter("@Id", SqlDbType.NVarChar);
sqlParameters[6].Value = Convert.ToString(Id);
con.dbConnection();
dtExists = con.executeSelectQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "ExistsCentralID";
clsError.LogWrite();
}
return(dtExists);
}
public Boolean New()
{
Boolean msg = true;
try
{
string query = string.Format("INSERT INTO gestores(NumeroEmpleado, Nombre, ApPaterno, ApMaterno, IdGestorRol, FechaCreacion , Cve)Values(@NumeroEmpleado, upper(@strName),upper(@strFirstName),upper(@strLastName), @strPuesto,GETDATE(), @Cve)");
SqlParameter[] sqlParameters = new SqlParameter[6];
sqlParameters[0] = new SqlParameter("@NumeroEmpleado", SqlDbType.NChar);
sqlParameters[0].Value = Convert.ToString(strNumeroEmpleado);
sqlParameters[1] = new SqlParameter("@strName", SqlDbType.NChar);
sqlParameters[1].Value = Convert.ToString(strName);
sqlParameters[2] = new SqlParameter("@strFirstName", SqlDbType.NChar);
sqlParameters[2].Value = Convert.ToString(strFirstName);
sqlParameters[3] = new SqlParameter("@strLastName", SqlDbType.NChar);
sqlParameters[3].Value = Convert.ToString(strLastName);
sqlParameters[4] = new SqlParameter("@strPuesto", SqlDbType.NChar);
sqlParameters[4].Value = Convert.ToString(strPuesto);
sqlParameters[5] = new SqlParameter("@Cve", SqlDbType.NChar);
sqlParameters[5].Value = Convert.ToString(strIniciales);
con.dbConnection();
msg = con.executeInsertQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "New";
clsError.LogWrite();
msg = false;
}
return(msg);
}
/// <summary>
/// Get all Status data from database
/// </summary>
/// <returns></returns>
public DataTable GetAllZone()
{
try
{
string query = string.Format("select d.IdDivision, d.Division [División], z.IdZona Id, z.cveZona Clave, z.Zona, z.Observaciones from zonas z join divisiones d on d.IdDivision = z.IdDivision where z.Activo = @Activo Order By z.Zona");
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@Activo", SqlDbType.SmallInt);
sqlParameters[0].Value = Convert.ToString(intActivo);
con.dbConnection();
Allzone = con.executeSelectQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GetAllZone";
clsError.LogWrite();
}
return(Allzone);
}
public DataTable GetByRUP(string strRUP)
{
try
{
string query = string.Format("select Titulo + Nombre + ' ' + ApPaterno + ' ' + ApMaterno as Nombre, TelTrabajo [Teléfono], Extencion [Extensión], Celular, Correo [Correo Electrónico] from ContactosCFE c join PuntosCarga p on p.IdZona = c.IdZona where c.Activo = 1 and p.RPU like '%'+@RUP+'%' Order By 2 ");
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@RUP", SqlDbType.NVarChar);
sqlParameters[0].Value = Convert.ToString(strRUP);
con.dbConnection();
Allzone = con.executeSelectQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GetByRUP";
clsError.LogWrite();
}
return(Allzone);
}
public DataTable GeEstatus()
{
try
{
string query = string.Format("SELECT IdEstatus Id ,Descripcion FROM ConveniosEstatus where Activo = @Activo ");
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@Activo", SqlDbType.SmallInt);
sqlParameters[0].Value = 1;
con.dbConnection();
dtConvenio = con.executeSelectQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GeEstatus";
clsError.LogWrite();
}
return(dtConvenio);
}
public Boolean InsertAgreement(string strAgreement, string strRPU, string strChecked, string strEmail, decimal decCharge)
{
//SqlDataReader drInsert;
DataTable dtData;
Boolean bMsg = true;
Class.Encrypt oclsEncrypt = new Class.Encrypt();
string strAgreementDecrypted = string.Empty;
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
try
{
oclsEncrypt.strData = strAgreement;
strAgreementDecrypted = oclsEncrypt.DecryptData();
Int16 intSeleccionado = Convert.ToInt16(strChecked);
string query = string.Format("spInsertar_ConvenioPuntoCarga");
SqlParameter[] sqlParameters = new SqlParameter[5];
sqlParameters[0] = new SqlParameter("@chrRPU", SqlDbType.NVarChar);
sqlParameters[0].Value = strRPU;
sqlParameters[1] = new SqlParameter("@chrConvenio", SqlDbType.NVarChar);
sqlParameters[1].Value = strAgreementDecrypted;
sqlParameters[2] = new SqlParameter("@intSeleccionado", SqlDbType.SmallInt);
sqlParameters[2].Value = intSeleccionado;
sqlParameters[3] = new SqlParameter("@chrEmail", SqlDbType.NVarChar);
sqlParameters[3].Value = strEmail;
sqlParameters[4] = new SqlParameter("@dblTotalCarga", SqlDbType.Float);
sqlParameters[4].Value = decCharge;
con.dbConnection();
dtData = con.executeStoreProcedure(query, sqlParameters);
}
catch (Exception ex)
{
clsError.logMessage = ex.ToString();
clsError.logModule = "InsertAgreement";
clsError.LogWrite();
bMsg = false;
}
return(bMsg);
}
public DataTable GetAllGroup()
{
try
{
ConnectionDB con = new ConnectionDB();
string query = string.Format(" SELECT pc.IdGrupo, g.Grupo " +
" , RPU + ' -- ' + PuntoCarga as RPUPuntoCarga " +
" , PuntoCarga AS[Punto de Carga] " +
" , RPU " +
" , Direccion [Dirección]" +
" , pc.IdTarifa, t.Tarifa " +
" , PorteoMaximo AS[Capacidad MW] " +
" , z.IdDIVISION, d.Division [División] " +
" , pc.IdZONA, z.Zona " +
" , pc.FechaCreacion AS[Fecha Alta] " +
" , e.Estatus , pc.IdEstatus, pc.IdGestorComercial, pc.IdGestorMedicion " +
" FROM PuntosCarga pc with(nolock) " +
" JOIN Grupos g ON pc.IdGrupo = g.idGrupo " +
" JOIN Tarifas t on pc.IdTarifa = t.IdTarifa " +
" JOIN Zonas z on pc.IdZona = z.IdZona " +
" JOIN Divisiones d on z.IdDivision = d.IdDivision " +
" JOIN PuntosCargaEstatus e on pc.IdEstatus = e.IdEstatus " +
" WHERE pc.Activo=@intActive order by 2, 3");
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@intActive", SqlDbType.SmallInt);
sqlParameters[0].Value = Convert.ToString(intActive);
con.dbConnection();
dtAllGroup = con.executeSelectQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GetAllGroup";
clsError.LogWrite();
}
return(dtAllGroup);
}
public DataTable GetAllContactCFEbyRPU()
{
try
{
string query = string.Format("spBuscarContactoRPU");
SqlParameter[] sqlParameters = new SqlParameter[2];
sqlParameters[0] = new SqlParameter("@intActive", SqlDbType.SmallInt);
sqlParameters[0].Value = Convert.ToString(intActivo);
sqlParameters[1] = new SqlParameter("@chrRPU", SqlDbType.NVarChar);
sqlParameters[1].Value = strRPU;
con.dbConnection();
AllDivision = con.executeStoreProcedure(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GetAllContactCFE";
clsError.LogWrite();
}
return(AllDivision);
}
public DataTable GetAllZoneByDivision_DDL()
{
try
{
string query = string.Format("select z.IdZona Id, z.Zona from zonas z where z.Activo = 1 and z.IdDivision=@strDivision Order By z.Zona");
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@strDivision", SqlDbType.NVarChar);
sqlParameters[0].Value = Convert.ToString(strDivision);
con.dbConnection();
Allzone = con.executeSelectQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GetAllZoneByDivision_DDL";
clsError.LogWrite();
}
return(Allzone);
}
public DataTable ExistsGroup()
{
try
{
string query = string.Format("select count(1) existe from Grupos where Activo = @Activo and Grupo=@Group");
SqlParameter[] sqlParameters = new SqlParameter[2];
sqlParameters[0] = new SqlParameter("@Activo", SqlDbType.SmallInt);
sqlParameters[0].Value = Convert.ToString(Activo);
sqlParameters[1] = new SqlParameter("@Group", SqlDbType.NVarChar);
sqlParameters[1].Value = Convert.ToString(Grupo);
con.dbConnection();
dtExistsGroup = con.executeSelectQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GetRPU";
clsError.LogWrite();
}
return(dtExistsGroup);
}
public Boolean NewZoneByDiv()
{
Boolean msg = true;
try
{
string query = string.Format("INSERT INTO Zonas (IdDivision, CveZona, Zona, Observaciones, FechaCreacion) " +
" Values( (SELECT top 1 IdDivision FROM Divisiones WHERE Activo = 1 and CveDivision = upper(@strDivision) ) " +
" , upper(@strCveZona), upper(@strZone), upper(@strObservation), GETDATE() " +
" ) ");
SqlParameter[] sqlParameters = new SqlParameter[4];
sqlParameters[0] = new SqlParameter("@strDivision", SqlDbType.NChar);
sqlParameters[0].Value = Convert.ToString(strDivision);
sqlParameters[1] = new SqlParameter("@strCveZona", SqlDbType.NChar);
sqlParameters[1].Value = Convert.ToString(strCveZone);
sqlParameters[2] = new SqlParameter("@strZone", SqlDbType.NChar);
sqlParameters[2].Value = Convert.ToString(strZone);
sqlParameters[3] = new SqlParameter("@strObservation", SqlDbType.NChar);
sqlParameters[3].Value = Convert.ToString(strObservation);
con.dbConnection();
msg = con.executeInsertQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "NewZone";
clsError.LogWrite();
msg = false;
}
return(msg);
}
public DataTable GetAll()
{
String FullName = string.Empty;
try
{
string query = string.Format("SELECT IdGestorTipo Id, GestorTipo FROM GestorTipos WHERE Activo = @Activo ORDER BY GestorTipo");
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@Activo", SqlDbType.SmallInt);
sqlParameters[0].Value = Convert.ToString(1);
con.dbConnection();
dt = con.executeSelectQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GetAll";
clsError.LogWrite();
}
return(dt);
}
/// <summary>
/// Get all division data from database
/// </summary>
/// <returns></returns>
public DataTable GetAllGroup()
{
String FullName = string.Empty;
try
{
string query = string.Format("SELECT IdDivision Id, convert(varchar(2),CveDivision) Clave, Division [Descripción], FechaCreacion [Fecha de Creación] FROM Divisiones WHERE Activo = @Activo ORDER BY Division");
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@Activo", SqlDbType.SmallInt);
sqlParameters[0].Value = Convert.ToString(Activo);
con.dbConnection();
AllDivision = con.executeSelectQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GetAllGroup";
clsError.LogWrite();
}
return(AllDivision);
}
public DataTable GetAllConvenio(string central)
{
//DataTable AllCentral;
String FullName = string.Empty;
try
{
string query = string.Format("select cv.IdConvenio,cv.Convenio + ' ' + cve.Descripcion as Convenio from Convenios cv inner join Centrales ct on cv.IdCentral = ct.IdCentral join ConveniosEstatus cve on cv.IdEstatus = cve.IdEstatus where ct.IdCentral =@Central and cv.Activo = 1");
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@Central", SqlDbType.Int);
sqlParameters[0].Value = Convert.ToInt32(central);
con.dbConnection();
AllCentral = con.executeSelectQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GetAllCentral";
clsError.LogWrite();
}
return(AllCentral);
}
public DataTable GetArchivosComunicacion(string strRPU)
{
try
{
ConnectionDB con = new ConnectionDB();
string query = string.Format("spBuscarArchivosComunicacion");
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@chrRPU", SqlDbType.NVarChar);
sqlParameters[0].Value = strRPU;
con.dbConnection();
dtData = con.executeStoreProcedure(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GetArchivosComunicacion";
clsError.LogWrite();
}
return(dtData);
}
public DataTable CentralByCentral(int idcentral)
{
String FullName = string.Empty;
try
{
string query = string.Format("select IdCentral,CveCentral,Central from centrales where IdCentral = @IdCENtral");
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@IdCENtral", SqlDbType.Int);
sqlParameters[0].Value = idcentral;
con.dbConnection();
AllCentral = con.executeSelectQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GetAllCentral";
clsError.LogWrite();
}
return(AllCentral);
}
public String ValidateCommunication(List <clsPropertiesCommunications> clsPC, string strRpu, string strEmail)
{
List <clsPropertiesCommunications> oClsPC = new List <clsPropertiesCommunications>();
string strMsg = string.Empty;
Boolean bMsg = true;
try
{
foreach (clsPropertiesCommunications pm in clsPC)
{
bMsg = InsertCommunication(pm.intIDParametersCommunications, strEmail, strRpu, pm.strDeliveryDate, pm.strInstallationDate, pm.strObservation, pm.intCheckActivo, pm.strTypesMeters, pm.strCommunicationClass, pm.strTypesCommunications, pm.strLocalCommunication, pm.strCFECommunication, pm.strActualMeter, pm.strRequiredMeter);
}
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "UpdateGroup";
clsError.LogWrite();
strMsg = string.Empty;
}
return(strMsg);
}
public DataTable GetAllZoneByDivisionAndCVe()
{
try
{
string query = string.Format("select 1 from zonas z where z.Activo = @Activo and Zona = @CveZona ");
SqlParameter[] sqlParameters = new SqlParameter[2];
sqlParameters[0] = new SqlParameter("@Activo", SqlDbType.SmallInt);
sqlParameters[0].Value = Convert.ToString(intActivo);
sqlParameters[1] = new SqlParameter("@CveZona", SqlDbType.NVarChar);
sqlParameters[1].Value = Convert.ToString(strZone);
con.dbConnection();
Allzone = con.executeSelectQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GetAllZoneByDivision";
clsError.LogWrite();
}
return(Allzone);
}
public DataTable GetAllGroup()
{
String FullName = string.Empty;
try
{
string query = string.Format("select IdGrupo Id , Grupo [Descripción], convert(varchar(10), FechaInicioOperaciones,126 ) as [Inicio de Operaciones], FechaCreacion [Fecha de Creación] from Grupos where Activo = @Activo Order By Grupo");
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@Activo", SqlDbType.SmallInt);
sqlParameters[0].Value = Convert.ToString(Activo);
con.dbConnection();
AllGroups = con.executeSelectQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GetRPU";
clsError.LogWrite();
}
return(AllGroups);
}
