public Boolean ValidateParameters()
{
Boolean bMsg = false;
foreach (clsPropertiesMeters pm in LPM)
{
intIDParametersMeters = pm.intIDParametersMeters;
strEmailusr = pm.strEmailusr;
strDeliveryDate = pm.strDeliveryDate;
strInstallationDate = pm.strInstallationDate;
strObservation = pm.strObservation;
intCheckActivo = pm.intCheckActivo;
try
{
InsertMeters();
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "CreateNewMeters";
clsError.LogWrite();
}
}
return(bMsg);
}
public DataTable Import_To_Grid()
{
DataTable dt = new DataTable();
try
{
string conStr = "";
switch (strExtension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"]
.ConnectionString;
break;
case ".xlsx": //Excel 07
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"]
.ConnectionString;
break;
}
conStr = String.Format(conStr, strFilePath, strIsHDR);
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
cmdExcel.Connection = connExcel;
//Get the name of First Sheet
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
connExcel.Close();
//Read Data from First Sheet
connExcel.Open();
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
//string SheetName = "Sheet1$";
cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
connExcel.Close();
//Bind Data to GridView
//GridView1.Caption = (FilePath);
//GridView1.DataSource = dt;
//GridView1.DataBind();
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "Import_To_Grid";
clsError.LogWrite();
}
return(dt);
}
public DataTable GeConveniosPorConvenio()
{
try
{
string query = string.Format("SELECT IdConvenio Id , Convenio, c.Descripcion [Descripción], IdCentral, ce.Descripcion [Estatus] , c.IdEstatus, c.FechaCreacion [Fecha de Creación], c.Activo " +
" FROM Convenios c " +
" JOIN ConveniosEstatus ce " +
" ON c.IdEstatus = ce.IdEstatus " +
" WHERE c.activo = 1 and c.IdConvenio = @IdConvenio " +
" ORDER BY [Fecha de Creación] desc ");
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@IdConvenio", SqlDbType.SmallInt);
sqlParameters[0].Value = idConvenio;
con.dbConnection();
dtConvenio = con.executeSelectQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GeConveniosPorConvenio";
clsError.LogWrite();
}
return(dtConvenio);
}
/// <summary>
/// Insert New Division into datat base
/// </summary>
/// <returns></returns>
public Boolean NewDivision()
{
Boolean msg = true;
DataTable dtData = null;
try
{
//string query = string.Format("INSERT INTO Division(CveDivision, Division, FechaCreacion) Values(@CveDivision, upper(@Division), GETDATE())");
string query = string.Format("spInsertar_catDivision ");
SqlParameter[] sqlParameters = new SqlParameter[2];
sqlParameters[0] = new SqlParameter("@CveDivision", SqlDbType.NChar);
sqlParameters[0].Value = Convert.ToString(CveDivision);
sqlParameters[1] = new SqlParameter("@Division", SqlDbType.NChar);
sqlParameters[1].Value = Convert.ToString(Division);
con.dbConnection();
//msg = con.executeStoreProcedure(query, sqlParameters);
dtData = con.executeStoreProcedure(query, sqlParameters);
msg = true;
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "NewDivision";
clsError.LogWrite();
msg = false;
}
return(msg);
}
/// <summary>
/// Logical Delete data from table Division
/// </summary>
/// <returns></returns>
public Boolean DeleteDivision()
{
Boolean msg = true;
try
{
string query = string.Format("Update Divisiones SET Activo = @Activo, FechaEliminacion= getdate() where IdDivision= @IdDivision ");
SqlParameter[] sqlParameters = new SqlParameter[2];
sqlParameters[0] = new SqlParameter("@Activo", SqlDbType.SmallInt);
sqlParameters[0].Value = Convert.ToString(Activo);
sqlParameters[1] = new SqlParameter("@IdDivision", SqlDbType.Int);
sqlParameters[1].Value = Convert.ToString(idDivision);
con.dbConnection();
msg = con.executeUpdateQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "DeleteDivision";
clsError.LogWrite();
msg = false;
}
return(msg);
}
public DataTable GetAgreement4RPUHistorico(string strRPU, Int16 intActive)
{
SqlDataReader drAgreement;
try
{
dtAgreement4rpu = null;
ConnectionDB con = new ConnectionDB();
string query = string.Format("spBuscarConveniosPorRPUHistorico");
//query = "spBuscarRPU";
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@chrRPU", SqlDbType.NVarChar);
sqlParameters[0].Value = Convert.ToString(strRPU);
con.dbConnection();
dtAgreement4rpu = con.executeStoreProcedure(query, sqlParameters);
//if (drAgreement.HasRows)
//{
// dtAgreement4rpu = new DataTable();
// dtAgreement4rpu.Load(drAgreement);
//}
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GetAgreement4RPUHistorico";
clsError.LogWrite();
}
return(dtAgreement4rpu);
}
public DataTable GetAllZoneByDivisionAndZone()
{
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 and z.IdDivision=@strDivision and ( z.IdZona = @strZone ) Order By z.Zona"); //z.CveZona = @strCveZone or
SqlParameter[] sqlParameters = new SqlParameter[4];
sqlParameters[0] = new SqlParameter("@Activo", SqlDbType.SmallInt);
sqlParameters[0].Value = Convert.ToString(intActivo);
sqlParameters[1] = new SqlParameter("@strDivision", SqlDbType.NVarChar);
sqlParameters[1].Value = Convert.ToString(strDivision);
sqlParameters[2] = new SqlParameter("@strCveZone", SqlDbType.NVarChar);
sqlParameters[2].Value = Convert.ToString(strCveZone);
sqlParameters[3] = new SqlParameter("@strZone", SqlDbType.NVarChar);
sqlParameters[3].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 = "GetAllZoneByDivisionAndZone";
clsError.LogWrite();
}
return(Allzone);
}
public DataTable GetSPCommunicationHistoricPorRPUMed(string strRPU, string intPregunta)
{
try
{
dtrpu = null;
string query = string.Format("spBuscarHistoricoPorRPUPorPreguntaMed");
SqlParameter[] sqlParameters = new SqlParameter[2];
sqlParameters[0] = new SqlParameter("@chrRPU", SqlDbType.NVarChar);
sqlParameters[0].Value = strRPU;
sqlParameters[1] = new SqlParameter("@intPregunta", SqlDbType.NVarChar);
sqlParameters[1].Value = intPregunta;
con.dbConnection();
dtrpu = con.executeStoreProcedure(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "spBuscarHistoricoPorRPUPorPreguntaMed";
clsError.LogWrite();
}
return(dtrpu);
}
/// <summary>
/// Insert New Division into datat base
/// </summary>
/// <returns></returns>
public Boolean NewStatus()
{
Boolean msg = true;
try
{
string query = string.Format("INSERT INTO [PuntosCargaEstatus](Estatus,FechaCreacion, Cve )Values(@Status,GETDATE() , @Cve )");
SqlParameter[] sqlParameters = new SqlParameter[2];
sqlParameters[0] = new SqlParameter("@Status", SqlDbType.NChar);
sqlParameters[0].Value = Convert.ToString(Status);
sqlParameters[1] = new SqlParameter("@Cve", SqlDbType.NChar);
sqlParameters[1].Value = Convert.ToString(Cve);
con.dbConnection();
msg = con.executeInsertQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "NewStatus";
clsError.LogWrite();
msg = false;
}
return(msg);
}
/// <summary>
/// Obtain all contacts from table ContactoCFE
/// </summary>
/// <returns></returns>
public DataTable GetAllContactCFE()
{
String FullName = string.Empty;
try
{
//string query = string.Format("select Titulo, cfe.Division, cfe.Zona, Correo, Nombre, ApPaterno as [Apellido Paterno], ApMaterno as [Apellido Materno], TelTrabajo as [Tel. Trabajo], Extencion as Extension, Celular, cfe.FechaCreacion as Alta from ContactoCFE CFE inner join Zona Z on CFE.Zona = z.Zona where cfe.Activo = @Activo and z.Activo= @Activo Order By Nombre");
string query = string.Format("select IdContactoCFE Id, z.IDDivision IdDivision, d.Division [División], c.IDZona IdZona, z.Zona " +
" , Titulo + ' ' + Nombre + ' ' + ApPaterno + ' ' + ApMaterno as [Nombre Completo] " +
", Titulo, Nombre, ApPaterno, ApMaterno " +
", Puesto , Correo " +
", TelTrabajo[Teléfono de Oficina], Extencion[Ext] " +
", Celular, c.FechaCreacion [Fecha de Creación] " +
" FROM ContactosCFE c" +
" inner join Zonas Z on c.IDZona = z.IdZona "+
" inner join divisiones d on z.IDDivision = d.IdDivision " +
" WHERE c.Activo = @Activo " +
" ORDER BY Nombre ");
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@Activo", SqlDbType.SmallInt);
sqlParameters[0].Value = Convert.ToString(intActivo);
con.dbConnection();
AllDivision = con.executeSelectQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GetAllContactCFE";
clsError.LogWrite();
}
return(AllDivision);
}
public Boolean DeleteContactoCFE()
{
Boolean msg = true;
try
{
string query = string.Format("Update ContactosCFE SET Activo = @intActivo, FechaEliminacion= getdate() where IdContactoCFE=@strID ");//Division= @strDivision and Zona = @strZone and Correo = @strEmail
SqlParameter[] sqlParameters = new SqlParameter[2];
sqlParameters[0] = new SqlParameter("@intActivo", SqlDbType.SmallInt);
sqlParameters[0].Value = Convert.ToString(intActivo);
sqlParameters[1] = new SqlParameter("@strID", SqlDbType.NVarChar);
sqlParameters[1].Value = Convert.ToString(strID);
con.dbConnection();
msg = con.executeUpdateQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "DeleteDivision";
clsError.LogWrite();
msg = false;
}
return(msg);
}
public DataTable GetAllZoneByDivisionAndZone()
{
try
{
string query = string.Format("select IdContactoCFE Id, z.IDDivision IdDivision, d.Division [División], z.Zona " +
", c.Titulo, c.Nombre, c.ApPaterno as [Apellido Paterno], c.ApMaterno as [Apellido Materno] , Puesto" +
", c.TelTrabajo as [Tel. Trabajo], c.Extencion as [Extensión], c.Celular, c.Correo, c.FechaCreacion [Fecha de Creación] " +
"from ContactosCFE c " +
" inner join Zonas Z on c.IDZona = z.IdZona "+
" inner join divisiones d on z.IDDivision = d.IdDivision " +
"where c.Activo = @Activo and c.IdZona = @strZone " +
"Order By z.Zona"); //and Division=@strDivision //Division,
SqlParameter[] sqlParameters = new SqlParameter[3];
sqlParameters[0] = new SqlParameter("@Activo", SqlDbType.SmallInt);
sqlParameters[0].Value = Convert.ToString(intActivo);
sqlParameters[1] = new SqlParameter("@strDivision", SqlDbType.NVarChar);
sqlParameters[1].Value = Convert.ToString(strDivision);
sqlParameters[2] = new SqlParameter("@strZone", SqlDbType.NVarChar);
sqlParameters[2].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 = "GetAllZoneByDivisionAndZone";
clsError.LogWrite();
}
return(Allzone);
}
public DataTable Exists()
{
try
{
string query = string.Format("select count(1) existe from gestores where Activo = @Activo and ( ( Nombre = @Nombre and ApPaterno = @ApPaterno and ApMaterno = @ApMaterno ) or ( NumeroEmpleado = @NumeroEmpleado ) ) ");
SqlParameter[] sqlParameters = new SqlParameter[5];
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("@NumeroEmpleado", SqlDbType.NVarChar);
sqlParameters[4].Value = Convert.ToString(strNumeroEmpleado);
con.dbConnection();
dtExists = con.executeSelectQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "ExistsCentral";
clsError.LogWrite();
}
return(dtExists);
}
public DataTable GetAll()
{
String FullName = string.Empty;
try
{
//string query = string.Format("select Titulo, cfe.Division, cfe.Zona, Correo, Nombre, ApPaterno as [Apellido Paterno], ApMaterno as [Apellido Materno], TelTrabajo as [Tel. Trabajo], Extencion as Extension, Celular, cfe.FechaCreacion as Alta from ContactoCFE CFE inner join Zona Z on CFE.Zona = z.Zona where cfe.Activo = @Activo and z.Activo= @Activo Order By Nombre");
string query = string.Format("SELECT IdGestor Id" +
" , r.IdGestorTipo, t.GestorTipo Tipo, g.IdGestorRol , r.GestorRol Rol" +
" , NumeroEmpleado[Núm de Empleado], g.cve Iniciales, upper(Nombre + ' ' + ApPaterno + ' ' + ApMaterno) as [Nombre Completo], Nombre, ApPaterno, ApMaterno, NumeroEmpleado , g.FechaCreacion, g.cve " +
" FROM Gestores g join [GestorRoles] r on g.IdGestorRol = r.IdGestorRol join[GestorTipos] t on t.IdGestorTipo = r.IdGestorTipo " +
" WHERE g.Activo = @Activo " +
" ORDER BY Nombre + ' ' + ApPaterno + ' ' + ApMaterno ");
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@Activo", SqlDbType.SmallInt);
sqlParameters[0].Value = Convert.ToString(intActivo);
con.dbConnection();
AllDT = con.executeSelectQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GetAll";
clsError.LogWrite();
}
return(AllDT);
}
public DataTable GetLoadingCharge()
{
try
{
ConnectionDB con = new ConnectionDB();
string query = string.Format(" SELECT RPU " +
" , PuntoCarga, RPU + ' -- ' + PuntoCarga as RPUPuntoCarga " +
" , Direccion [Dirección] " +
" , pc.IdTarifa, t.Tarifa " +
" , PorteoMaximo " +
" , pc.idGrupo, g.Grupo " +
" , pc.FechaCreacion " +
" , pc.FechaModificacion " +
" , pc.Activo " +
" FROM PuntosCarga pc with(nolock) " +
" JOIN Grupos g ON pc.IdGrupo = g.idGrupo " +
" JOIN Tarifas t ON pc.IdTarifa = t.IdTarifa " +
" WHERE pc.Activo = @intActive and pc.idGrupo = @strGroup order by PuntoCarga ");
SqlParameter[] sqlParameters = new SqlParameter[2];
sqlParameters[0] = new SqlParameter("@intActive", SqlDbType.SmallInt);
sqlParameters[0].Value = Convert.ToString(intActive);
sqlParameters[1] = new SqlParameter("@strGroup", SqlDbType.NVarChar);
sqlParameters[1].Value = Convert.ToString(strGroup);
con.dbConnection();
dtGroup = con.executeSelectQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GetGroup";
clsError.LogWrite();
}
return(dtGroup);
}
/// <summary>
/// Update Status into the database
/// </summary>
/// <returns></returns>
public Boolean UpdateStatus()
{
Boolean msg = true;
try
{
string query = string.Format("Update PuntosCargaEstatus SET Estatus = @Status where Activo = @Activo and IdEstatus= @IdStatus");
SqlParameter[] sqlParameters = new SqlParameter[3];
sqlParameters[0] = new SqlParameter("@Status", SqlDbType.NVarChar);
sqlParameters[0].Value = Convert.ToString(Status);
sqlParameters[1] = new SqlParameter("@Activo", SqlDbType.SmallInt);
sqlParameters[1].Value = Convert.ToString(Activo);
sqlParameters[2] = new SqlParameter("@IdStatus", SqlDbType.Int);
sqlParameters[2].Value = Convert.ToString(idStatus);
con.dbConnection();
msg = con.executeUpdateQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "UpdateStatus";
clsError.LogWrite();
msg = false;
}
return(msg);
}
public DataTable GetData4ParameterMeterds(string strRPU, Int16 intActive, int idParameterMeter)
{
try
{
dtAgreement4rpu = null;
ConnectionDB con = new ConnectionDB();
string query = string.Format("spBuscarDatosParamMedicion");
//query = "spBuscarRPU";
SqlParameter[] sqlParameters = new SqlParameter[3];
sqlParameters[0] = new SqlParameter("@intActivo", SqlDbType.SmallInt);
sqlParameters[0].Value = Convert.ToString(intActive);
sqlParameters[1] = new SqlParameter("@chrRPU", SqlDbType.NVarChar);
sqlParameters[1].Value = Convert.ToString(strRPU);
sqlParameters[2] = new SqlParameter("@intIdParametroMedicion", SqlDbType.Int);
sqlParameters[2].Value = Convert.ToString(idParameterMeter);
con.dbConnection();
dtAgreement4rpu = con.executeStoreProcedure(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GetAgreement4RPU";
clsError.LogWrite();
}
return(dtAgreement4rpu);
}
public DataTable ExistsCve_Update()
{
try {
string query = string.Format("select count(1) existe from PuntosCargaEstatus where Activo = @Activo and ( Cve = @Cve or Estatus = @Status ) and IdEstatus <> @idStatus ");
SqlParameter[] sqlParameters = new SqlParameter[4];
sqlParameters[0] = new SqlParameter("@Activo", SqlDbType.SmallInt);
sqlParameters[0].Value = Convert.ToString(Activo);
sqlParameters[1] = new SqlParameter("@Cve", SqlDbType.NVarChar);
sqlParameters[1].Value = Convert.ToString(Cve);
sqlParameters[2] = new SqlParameter("@Status", SqlDbType.NVarChar);
sqlParameters[2].Value = Convert.ToString(Status);
sqlParameters[3] = new SqlParameter("@idStatus", SqlDbType.NVarChar);
sqlParameters[3].Value = Convert.ToString(idStatus);
con.dbConnection();
dtExists = con.executeSelectQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "ExistsCentral";
clsError.LogWrite();
}
return(dtExists);
}
/// <summary>
/// Insert New Division into datat base
/// </summary>
/// <returns></returns>
public Boolean NewZone()
{
Boolean msg = true;
try
{
string query = string.Format("INSERT INTO Zonas (IdDivision, CveZona, Zona, Observaciones, FechaCreacion)Values(@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 Boolean NewCentral()
{
Boolean msg = true;
try
{
string query = string.Format("INSERT INTO Centrales(CveCentral,Central,FechaCreacion)Values(upper(@CodeCentral), upper(@Central), GETDATE() )");
SqlParameter[] sqlParameters = new SqlParameter[2];
sqlParameters[0] = new SqlParameter("@CodeCentral", SqlDbType.NChar);
sqlParameters[0].Value = Convert.ToString(CodCentral);
sqlParameters[1] = new SqlParameter("@Central", SqlDbType.NChar);
sqlParameters[1].Value = Convert.ToString(Central);
con.dbConnection();
msg = con.executeInsertQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "NewCentral";
clsError.LogWrite();
msg = false;
}
return(msg);
}
public DataTable getPreguntaMedidor(string RUP, string Pregunta)
{
try
{
ConnectionDB con = new ConnectionDB();
string query = string.Format("buscarPreguntaMedicion");
SqlParameter[] sqlParameters = new SqlParameter[2];
sqlParameters[0] = new SqlParameter("@IdPuntoCarga", SqlDbType.VarChar);
sqlParameters[0].Value = RUP;
sqlParameters[1] = new SqlParameter("@pregunta", SqlDbType.Int);
sqlParameters[1].Value = Pregunta;
con.dbConnection();
dtData = con.executeStoreProcedure(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "getPreguntaMedidor";
clsError.LogWrite();
}
return(dtData);
}
public DataTable ExistsCentralID()
{
try
{
string query = string.Format("select count(1) existe from Centrales where Activo = @Activo and ( CveCentral = @CodCentral or Central = @Central ) and IdCentral <> @ID");
SqlParameter[] sqlParameters = new SqlParameter[4];
sqlParameters[0] = new SqlParameter("@Activo", SqlDbType.SmallInt);
sqlParameters[0].Value = Convert.ToString(Activo);
sqlParameters[1] = new SqlParameter("@CodCentral", SqlDbType.NVarChar);
sqlParameters[1].Value = Convert.ToString(CodCentral);
sqlParameters[2] = new SqlParameter("@Central", SqlDbType.NVarChar);
sqlParameters[2].Value = Convert.ToString(Central);
sqlParameters[3] = new SqlParameter("@ID", SqlDbType.NVarChar);
sqlParameters[3].Value = Convert.ToString(idCentral);
con.dbConnection();
dtExistsCentral = con.executeSelectQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "ExistsCentral";
clsError.LogWrite();
}
return(dtExistsCentral);
}
/// <summary>
/// Update Division into the database
/// </summary>
/// <returns></returns>
public Boolean UpdateDivision()
{
Boolean msg = true;
try
{
string query = string.Format("Update Divisiones SET CveDivision = upper(@CveDivision), Division = upper(@Division) where Activo = @Activo and IdDivision= @IdDivision");
SqlParameter[] sqlParameters = new SqlParameter[4];
sqlParameters[0] = new SqlParameter("@CveDivision", SqlDbType.NVarChar);
sqlParameters[0].Value = Convert.ToString(CveDivision);
sqlParameters[1] = new SqlParameter("@Division", SqlDbType.NVarChar);
sqlParameters[1].Value = Convert.ToString(Division);
sqlParameters[2] = new SqlParameter("@Activo", SqlDbType.SmallInt);
sqlParameters[2].Value = Convert.ToString(Activo);
sqlParameters[3] = new SqlParameter("@IdDivision", SqlDbType.Int);
sqlParameters[3].Value = Convert.ToString(idDivision);
con.dbConnection();
msg = con.executeUpdateQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "UpdateDivision";
clsError.LogWrite();
msg = false;
}
return(msg);
}
public DataTable GetAllCentral()
{
String FullName = string.Empty;
try
{
string query = string.Format("select IdCentral, CveCentral [Código], Central [Descripción] " +
" ,CONVERT(varchar, CAST( isnull((select sum(carga) " +
" from Convenios co join PuntoCargaPorConvenio pcpc on pcpc.IdConvenio = co.IdConvenio and pcpc.Activo = 1 and co.IdEstatus = 2 " +
" where co.Activo = 1 and co.IdCentral = c.IdCentral),0)AS money), 1) Carga " +
" from Centrales c " +
" where Activo = @Activo " +
"Order By Central ");
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@Activo", SqlDbType.SmallInt);
sqlParameters[0].Value = Convert.ToString(Activo);
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 ExistDivisionCve()
{
String FullName = string.Empty;
try
{
string query = string.Format("SELECT count(1) existe FROM Divisiones WHERE Activo = @Activo and ( CveDivision = upper(@strCveDivision) or Division = upper(@strDivision) )");//and Division = upper(@strDivision)
SqlParameter[] sqlParameters = new SqlParameter[3];
sqlParameters[0] = new SqlParameter("@Activo", SqlDbType.SmallInt);
sqlParameters[0].Value = Convert.ToString(Activo);
sqlParameters[1] = new SqlParameter("@strCveDivision", SqlDbType.NVarChar);
sqlParameters[1].Value = Convert.ToString(CveDivision);
sqlParameters[2] = new SqlParameter("@strDivision", SqlDbType.NVarChar);
sqlParameters[2].Value = Convert.ToString(Division);
con.dbConnection();
AllDivision = con.executeSelectQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "ExistDivision";
clsError.LogWrite();
}
return(AllDivision);
}
public DataTable GetAllGroup(int GMedicion, int GComercial)
{
String FullName = string.Empty;
try
{
string query = string.Format("spBuscarGruposByGestores");
SqlParameter[] sqlParameters = new SqlParameter[2];
sqlParameters[0] = new SqlParameter("@IdGMedicion", SqlDbType.Int);
sqlParameters[0].Value = GMedicion;
sqlParameters[1] = new SqlParameter("@IdGComercial", SqlDbType.Int);
sqlParameters[1].Value = GComercial;
con.dbConnection();
AllGroups = con.executeStoreProcedure(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GetRPU";
clsError.LogWrite();
}
return(AllGroups);
}
//DataTable dtConvenio;
public DataTable GeConveniosPorCentral()
{
try
{
string query = string.Format(" select c.IdConvenio Id , Convenio, c.Descripcion [Descripción], IdCentral, count(IdPuntoCarga) [Núm de Cargas],sum(pcc.carga) [Carga Total] , ce.Descripcion [Estatus], c.IdEstatus, c.Activo " +
" FROM Convenios c " +
" JOIN ConveniosEstatus ce " +
" ON c.IdEstatus = ce.IdEstatus " +
" join PuntoCargaPorConvenio pcc on c.IdConvenio = pcc.IdConvenio" +
" WHERE c.activo = 1 and c.IdCentral = @IdCentral " +
" group by c.IdConvenio , Convenio, c.Descripcion , IdCentral , ce.Descripcion , c.IdEstatus, c.Activo " +
" ORDER BY 1 desc ");
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@IdCentral", SqlDbType.SmallInt);
sqlParameters[0].Value = idCentral;
con.dbConnection();
dtConvenio = con.executeSelectQuery(query, sqlParameters);
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GeConveniosPorCentral";
clsError.LogWrite();
}
return(dtConvenio);
}
public Boolean NewGroup()
{
Boolean msg = true;
try
{
string query = string.Format("INSERT INTO GRUPOs(Grupo,FechaInicioOperaciones, FechaCreacion,FechaEliminacion,Activo,IdGMedicion,IdGComercial) Values(upper(@Grupo), @FechaInicioOperaciones, GETDATE(),null,1,@IdGMedicion,@IdGComercial)");
SqlParameter[] sqlParameters = new SqlParameter[4];
sqlParameters[0] = new SqlParameter("@Grupo", SqlDbType.NChar);
sqlParameters[0].Value = Convert.ToString(Grupo);
sqlParameters[1] = new SqlParameter("@FechaInicioOperaciones", SqlDbType.NChar);
sqlParameters[1].Value = Convert.ToString(InicioOperaciones);
sqlParameters[2] = new SqlParameter("@IdGMedicion", SqlDbType.Int);
sqlParameters[2].Value = IdMed;
sqlParameters[3] = new SqlParameter("@IdGComercial", SqlDbType.Int);
sqlParameters[3].Value = IdComer;
con.dbConnection();
msg = con.executeInsertQuery(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 String GetRPU()
{
String FullName = string.Empty;
DataTable RPU;
try
{
string query = string.Format("select RPU from PuntosCarga where (RPU = @RPU )");
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@RPU", SqlDbType.VarChar);
sqlParameters[0].Value = Convert.ToString(strServiceCFE);
con.dbConnection();
RPU = con.executeSelectQuery(query, sqlParameters);
if (RPU.Rows.Count > 0)
{
foreach (DataRow row in RPU.Rows)
{
FullName = Convert.ToString(row["RPU"]);
}
}
}
catch (Exception ex)
{
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
clsError.logMessage = ex.ToString();
clsError.logModule = "GetRPU";
clsError.LogWrite();
}
return(FullName);
}
public Boolean InsertDownloadLogFile(string strFile, string strEmail)
{
Boolean bMsg = true;
DataTable dtData;
LogError.LogErrorMedicion clsError = new LogError.LogErrorMedicion();
try
{
string query = string.Format("spInsertar_LogArchivosMedicion");
SqlParameter[] sqlParameters = new SqlParameter[2];
sqlParameters[0] = new SqlParameter("@chrEmail", SqlDbType.NVarChar);
sqlParameters[0].Value = strEmail;
sqlParameters[1] = new SqlParameter("@chrArchivo", SqlDbType.NVarChar);
sqlParameters[1].Value = strFile;
con.dbConnection();
dtData = con.executeStoreProcedure(query, sqlParameters);
}
catch (Exception ex)
{
clsError.logMessage = ex.ToString();
clsError.logModule = "InsertAgreement";
clsError.LogWrite();
bMsg = false;
}
return(bMsg);
}
